Dialog for selecting range within subroutine

  • Thread starter Thread starter John
  • Start date Start date
J

John

Excel 2007

Is there a dialog which I can call within a subroutine which would ask the
user to select with the mouse a range of cells and place the result in a
variable?

Thanks in advance
 
Sub rng()
Set myRange = Application.InputBox("Enter a range or " _
& "select a range with the mouse.", _
"SELECT A RANGE", Type:=8)
MsgBox myRange.Address
End Sub
 
Excel 2007

Dialog for selecting range
Is there a dialog which I can call within a subroutine which would ask the
user to select with the mouse a range of cells and place the result in a
variable?

http://www.ozgrid.com/forum/showthread.php?t=64872
Is there a built-in dialog box that allows the user to select a range by
clicking and dragging with the mouse? I'd like to use this in my code and
have the dialog box return either a range object or and address that I can
use in a range assignment statement. I'm looking for something like what you
get when you select Insert-->Name-->Define. The "Refers To" text box at the
bottom automatically updates as the user clicks and drags across cells. Is
there such a thing accessible via VBA?

Answer
http://msdn.microsoft.com/en-us/library/bb209950.aspx

Dim rng As Range
Set rng = Application.InputBox("Select cell(s)",type:=8)

Note : VB: AutoLinked keywords will cause extra spaces before keywords.
Extra spacing is NOT transferred when copy/pasting, but IS if the keyword
uses "quotes".
 
Back
Top