Selecting Cells using dialog box

[Todd Huttenstine]

Hey

I would like to be able to have a little popup dialogbox
that allows me to click in Excel and select a range of
cells by clicking the mouse on a cell and dragging into
the cells I want to include into my range. Then after I
release my mouse button, the actual range pops up in the
dialogbox. For example I click a button and the dialog
pop up. I click in Excel and drag from A3 to B20. When I
release the mouse button, I would like for the popupbox
textbox to show A3:B20. (This type of thing can be
observed when creating a chart or pivot table.)


How do I do this?


Thanks
Todd Huttenstine

 

[Guest]

You will need to create a userform and place a label on that form. Then run
the test funtion which should be placed in one of the sheets...

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserForm1.Label1.Caption = Target.Address
End Sub

Sub test()
UserForm1.Show False
End Sub

 

[Todd Huttenstine]

Thanks

-----Original Message-----
You will need to create a userform and place a label on that form. Then run
the test funtion which should be placed in one of the sheets...

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
UserForm1.Label1.Caption = Target.Address
End Sub

Sub test()
UserForm1.Show False
End Sub



.

 

[Harald Staff]

Hi Todd

It I understand this right:

Sub test()
Dim R As Range
On Error Resume Next
Set R = Application.InputBox("Select a range:", _
"Todd says", Selection.Address, Type:=8)
If R Is Nothing Then Exit Sub
R.Font.Bold = True 'or whatever
End Sub

You can also make a userform with a RefEdit control on it for similar
behavior.

HTH. Best wishes Harald