Using an Inputbox

[NathanW]

I've used inputboxes in the past and I do not know why I am having so much trouble now, but here is what I have:

Public StartCell As Variant
StartCell = Application.InputBox(Msg, "Select a Cell", Type:=8)
Range(StartCell).Activate

I am getting the error Run-time error '1004': Method 'Range' of object '_Global' failed.

Someone please help me!

 

[JoeMo]

Add the keyword Set:
Set Startcell
since you have used type:=8, VBA is looking for a range object in Startcell

 

[NathanW]

Thank you very much for your quick response, Joe. However, that did not work. I received the same error on the line "Range(Startcell).Activate" (which is where the error was before)

 

[shg]

After adding the Set as Joe suggested, just

StartCell.Select

 

[NathanW]

Thanks for the advice shg! Unfortunately it still gave the same error =/

 

[NathanW]

If it helps at all, I am noticing that when I hit "debug", the value in StartCell is "Empty". So it does not have a cell reference to activate? Not sure why it is doing that....

 

[JoeMo]

If it helps at all, I am noticing that when I hit "debug", the value in StartCell is "Empty". So it does not have a cell reference to activate? Not sure why it is doing that....

Are you selecting a cell with your mouse when the input box appears?

 

[NathanW]

Yes, when the inputbox pops up, I select a cell on the screen and hit 'ok' on the inputbox, which is when the program crashes.

 

[JoeMo]

Yes, when the inputbox pops up, I select a cell on the screen and hit 'ok' on the inputbox, which is when the program crashes.

Run this test and tell us if you get the msgbox.

Sub test()
Dim StartCell As Variant
On Error Resume Next
Set StartCell = Application.InputBox("Select a Cell", Type:=8)
On Error GoTo 0
If Not StartCell Is Nothing Then
    MsgBox "StartCell is cell " & StartCell.Address
    StartCell.Select
End If
End Sub

If yes, then note that your original post showed InputBox(Msg, ...)
and you should either define Msg as a string and initialize it or remove it.

 

[NathanW]

That made it work! Thank you guys so much for your help and persistence. Any idea why it took all that error handling to make it work? I don't understand why this had to be so complicated =P