Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel VBA problem (XL2000 SR1)

    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:

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



    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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA problem (XL2000 SR1)

    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?
    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA problem (XL2000 SR1)

    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.

    <pre>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
    </pre>


  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA problem (XL2000 SR1)

    Is this code by any chance fired by a command button on a sheet? If so, uncheck it's TakeFocusOnClick property
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA problem (XL2000 SR1)

    The only way I can get the object required error is by clicking the cancel button.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA problem (XL2000 SR1)

    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:

    <pre>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
    </pre>

    Legare Coleman

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA problem (XL2000 SR1)

    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

  8. #8
    2 Star Lounger
    Join Date
    Dec 2000
    Location
    Sault Ste. Marie, Michigan, USA
    Posts
    102
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel VBA problem (XL2000 SR1)

    Jan,

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

    Ken

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •