Results 1 to 5 of 5
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Capture a range using an inputbox (Excel 2000 >)

    Here is a useful way of capturing a range with an inputbox. No need to created a custom userform to do the job!!!
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Capture a range using an inputbox (Excel 2000 >)

    Thanks. The use of Application.InputBox in Excel is relatively little known. Apart from using it to let the user enter a range, as in your instructions, it can also be used to limit the values the user can enter to for example numbers only.

    Type Application.InputBox in the Visual Basic Editor and press F1 to see the online help subject. Here is the list of possible values of the Type argument from the online help:

    <table border=1><td align=center>Value</td><td align=center>Meaning</td><td align=right>0</td><td>A formula</td><td align=right>1</td><td>A number</td><td align=right>2</td><td>Text (a string)</td><td align=right>4</td><td>A logical value (True or False)</td><td align=right>8</td><td>A cell reference, as a Range object</td><td align=right>16</td><td>An error value, such as #N/A</td><td align=right>64</td><td>An array of values</td></table>
    You can use the sum of the allowable values for Type. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.

  3. #3
    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: Capture a range using an inputbox (Excel 2000 >)

    I would add the line:

    on error goto 0

    after the "set varRange" line to turn back on error checking. You should only ignore it for the 1 line.

    Steve

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Capture a range using an inputbox (Excel 2000 >)

    Hi Hans,
    What you have added to my initial post is GREATnews. The application.inputbox is relatively new to me still, as I only discovered it a short while ago. Just the fact that it could be used to capture a range blew me away as i at times created my own userform to do that. This is going to save much time! What u inform me of now is extending my interest regarding this feature. I am certainly going to experiment with it further!

    Thanks for your input and value added advice!
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Capture a range using an inputbox (Excel 2000 >)

    Hi Steve,

    I agree with you concerning the statement. I do apologize not adding that to the attachment. I think I was soooo excited to share the find that I overlooked the accuracy of my post.

    Thanx for the correction!
    Regards,
    Rudi

Posting Permissions

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