Prompt user to select a range

[Aaron Dyck]

I am writing an add-in that loads a form when a certain toolbar item (That I
have created) is pressed. In this form, there is a textbox for a range. I
need to be able to have the user select the range, much like in the Goal Seek
menu, where the range can be hilighted and dynamically updates the content of
the textbox.

My goal is to have the user select a range, and the macro would then perform
a function on that range.

Any suggestions?

 

[Anant Basant]

Hi Aaron,

The code snippet you can use to do such a thing is:

Private Sub CmdOK_Click()

Dim r As Range
On Error Resume Next
Set r = Range(RefEdit1.Value)
If Err.Number <> 0 Then
MsgBox "wrong value for range", vbCritical, "Wrong Range"
End If
On Error GoTo 0
End Sub

Once the range is set, it can be used further in the macro.