User Prompt For Cell

**canyon289** · 09-13-2011, 03:00 PM

Hi,
I'm trying to write a macro and at one point I want it to stop at a user selected cell then delete all the values in the column below that cell and the same for the column to the left.

This is the code I've tried to use to allow the user to select the cell, it comes up with with an input box but fails stating

"method 'range' of object '_global' failed"\

    Dim sCell As String
    sCell = Range(Application.InputBox(Prompt:="Pick the Cell", Type:=8)).Value
    MsgBox sCell

This should be really simple but it's not working and I'm pulling my hair out. Thanks in advance.

Also attached is a graphic showing what I'm trying to do.

**TMS** · 09-13-2011, 03:46 PM

Please don't post pictures of worksheets; post a sample workbook.

I don't want to have to recreate your spreadsheet before I can help with a solution.

Regards

**watersev** · 09-13-2011, 03:52 PM

hi, canyon289, try this out:

Sub test()

Dim icol As Long, myrange As Range

On Error Resume Next

Set myrange = Application.InputBox(Prompt:="Select cell", Type:=8)

If myrange Is Nothing Then Exit Sub

If myrange.Cells.Count = 1 Then
    
    icol = myrange.Column
    
    Union(Range(myrange, Cells(Rows.Count, icol).End(xlUp)), Range(myrange.Offset(, 1), Cells(Rows.Count, icol + 1).End(xlUp))).ClearContents

Else

    MsgBox "Please try again and select one cell only.", vbInformation

End If

End Sub

**mikerickson** · 09-13-2011, 03:54 PM

If type = 8, Application.InputBox returns a Range object, not a string. Try this

Dim sCell As String
   
sCell = Application.InputBox(Prompt:="Pick the Cell", Type:=8).Value

MsgBox sCell

To buffer it (if the user presses Cancel or selects more than one cell or selects a cell with and error value), I'd add

Dim sCell As String

sCell = Chr(5)
On Error Resume Next
sCell = CStr(Application.InputBox(Prompt:="Pick the Cell", Type:=8).Cells(1,1).Value)
On Error Goto 0

If sCell = Chr(5) Then
    MsgBox "cancel Pressed"
Else
    MsgBox sCell
End If

Alternatly, if you are as interested in the actual cells as you are in its contents, you might declare uiCell as a Range object and set that to the return of the InputBox.

**canyon289** · 10-08-2011, 07:50 PM

Hey,
Thank you for the help! Your functions have helped so much in reducing the amount of time I need to spend on crunching my data. I have another question now but that's for another thread.

Thank you again!