Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Thanked 1 Time in 1 Post

    Highlight a range for code in macro (Excel xp)

    I am writing a macro that the user will enter a range that their chart will link to. I would like the user to be able to go to the data worksheet and highlight the range and have that highlight range be used as a part of my macro. Right now, the user must type in B47:B112 in an inputbox and then type in the worksheet name in another input box..."data". I would rather, they be able to click on the data worksheet and highlight B47:B112 and I be able to capture that information to use in my macro.

    Is there an easy way for me to do this or instructions that will explain it to me in detail? Thank you very much for any assistance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Highlight a range for code in macro (Excel xp)

    Excel has a custom InputBox function as a method of the Application object - I hope it still exists in Excel XP. This Inputbox function has an extra Type parameter:

    <table border=1><td>Value</td><td>Description</td><td align=right>0</td><td>Formula</td><td align=right>1</td><td>Number</td><td align=right>2</td><td>String</td><td align=right>4</td><td>Logical value (True/False)</td><td align=right>8</td><td>Range</td><td align=right>16</td><td>Error value such as #N/A</td><td align=right>64</td><td>Array</td></table>

    The following code will prompt the user for a range; it can be typed or selected, and the result will be assigned to the variable rng of type Range directly:
    Dim rng As Range
    Set rng = Application.InputBox(Prompt:="Type or select a range", Type:=8)

Posting Permissions

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