Excel VBA problem (XL2000 SR1)

Topic

New Reply

I’m having a problem with the InputBox method in VBA for returning a reference. I didn’t find it in the MSKB though. Start by putting this snippet of
code in a general module of a workbook with more than one sheet:

Sub test()
  Dim rngTargetCell As Range
  Set rngTargetCell = Application.InputBox("Select a target cell on another sheet", Type:=8)
End Sub

Start the macro, go to a different sheet and select a cell on that sheet, then either click OK, or press ENTER. I find that if I press ENTER, everything thing runs fine, but if I click OK, most of the time it errors out with error 424 (Object Required). Is this a known problem? Does SR2 perhaps fix it? Your input is appreciated.

Ken

Reply | Quote

Replies

I copied your code and pasted it into my XL2000Sr1 and ran it about 20 times clicking OK and a few more times pressing enter and had no errors.

Is this code part of a VBA routine that has other code in it? When you get the error, if you click on the Debug button what line of code is hilited?

Reply | Quote

Legare, Thanks for responding. Here’s my actual project…although I have the same problem with the snippet as with the actual project.
It’s purpose is to extract the unique values from a selected 1 column list and write it to the target location. The error occurs on the
“Set” statement.

Public Sub Extract()
Dim rngStartingCell As Range

Set rngStartingCell = Application.InputBox(Prompt:="Select a cell in a blank area _
 to start the list of unique items", Type:=8)

Selection.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", _
CopyToRange:=rngStartingCell, Unique:=True

End Sub 

Reply | Quote

Is this code by any chance fired by a command button on a sheet? If so, uncheck it’s TakeFocusOnClick property

Reply | Quote

Jan,

Nope, no command button. I’m running it from the Macro dialog box, and also from a custom menu button.

Ken

Reply | Quote

I have not been able to get that error by clicking on OK, but as Jan Karel said in his post, you will get that error if you click on Cancel. If that is your problem, here is some code that will get you around that:

Sub test()
    Dim rngTargetCell As Range
    On Error Resume Next
    Set rngTargetCell = Application.InputBox("Select a target cell on another sheet", Type:=8)
    On Error GoTo 0
    If rngTargetCell Is Nothing Then
        MsgBox "You hit cancel"
        ' Do what you need to do if the user hit cancel
    End If
End Sub

Reply | Quote

Legare & Jan,

Yes, the cancel button does give me that error too, but so does the OK button. I probably need to reinstall XL to fix this, but I’m not sure that it is worth the effort required. Thanks for your help.

Ken

Reply | Quote

The only way I can get the object required error is by clicking the cancel button.

Reply | Quote