How do you do a range selection dialog like those many in Excel?

[LunaMoon]

Hi all,

I want to select some range dynamically, including multiple selection,
via showing a range selection dialog...

Hopefully there are some dialog functions that I can use...

How to do that in VBA?

Thanks!

 

[John Bundy]

There is a RefEdit control for the toolbar, add it if you don't have it
already by selecting More Controls (hammer and wrench icon) and select
RefEdit. You need to have a userform up to do this in VB, right click on your
project and add Userform, the toolbox will appear.

 

[XP]

Another way; this just uses an input box...

Copy the following into a standard code module and run the first one (you
will need to correct for line wrap):

Sub TESTIT()

Dim sAddress As String
sAddress = InputBoxRange

MsgBox "The selection was: " & sAddress

End Sub


Public Function InputBoxRange() As String
Dim rngResponse As Range
Dim strMsg As String
Dim intConfirm As Integer
On Error Resume Next
Do
Set rngResponse = Application.InputBox("Type or select a range of
cells:", "RANGE VALIDATION", Selection.Address, , , , , 8)
If Err.Number <> 0 Then Err.Clear: End
rngResponse.Select
intConfirm = MsgBox("Is the following selection correct?" & vbCr & vbCr
& rngResponse.Address, vbQuestion + vbYesNo, "CONFIRM SELECTION")
If intConfirm = 6 Then Exit Do 'YES
Loop
InputBoxRange = rngResponse.Address
End Function