Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    User input range for VBA by selecting (2000sp3)

    I'm trying to allow a user to input a range to be manipulated in VBA by selecting it. I have found no way to access Excel's own "select range in a dialog" input box, and using this:

    Dim myrange as range
    Set myrange=application.inputbox(Prompt:="Please select the range",Type:=8)

    keeps giving me an object required error, even though the address of the selected range does appear in the input box as it's being selected. I've tried declaring myrange as an object, and also tried as variant, but it makes no difference, and activating the worksheet in code prior to the input box also doesn't seem to help.

    Not sure where I'm going wrong here. Pointers appreciated.

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: User input range for VBA by selecting (2000sp3)

    The code you posted will run from an Excel VBA Module. Check for typos in the actual code and set "option explicit" to be sure you don't have a duplicate Type declaration.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: User input range for VBA by selecting (2000sp3)

    I'd do it like this:


    Option Explicit

    Sub test()
    Dim myrange As Range
    On Error Resume Next
    Set myrange = Application.InputBox(Prompt:="Please select the range", Type:=8)
    If myrange Is Nothing Then
    MsgBox "Cancelled"
    Else
    MsgBox myrange.Address
    End If

    End Sub
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: User input range for VBA by selecting (2000sp3)

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17>You neglected to add:
    <pre>On error goto 0</pre>

    after the <pre>Set myrange ... </pre>

    line, to make sure any other errors are not ignored.

    Steve

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

    Re: User input range for VBA by selecting (2000sp3)

    Thanks, well spotted.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    3 Star Lounger
    Join Date
    Feb 2001
    Posts
    369
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: User input range for VBA by selecting (2000sp3)

    Thanks.

    I give up.

    Tried it many times this morning, with the same error. Came back this afternoon and it works without complaint. I changed nothing.

    I've stopped trying to understand.

    Thanks anyway, and sorry to waste your time

Posting Permissions

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