Identifying a Selected Range in a Macro

D

DCSwearingen

When a user clicks and drags to highlight a range, how do I identify
that range in a macro?

Say the user highlights cells E6:G17, I want to give them the ability
to click a button that will do several things to that range.

I have tested my macro, but only when I have used a known range.
 
B

Bondi

Hi,

Here is a example of the With Selection:

Private Sub CommandButton1_Click()
With Selection
.Value = 1
End With
End Sub

Regards,
Bondi
 
D

Dave Peterson

You can use Selection to get the user's current selection.

Dim myCell as range
for each mycell in selection.cells
...
next mycell

In fact, you could toss up an inputbox that allows the user to select the range.

Dim myRng as range
set myrng =nothing
on error resume next
set myrng = application.inputbox(Prompt:="Select a range!", type:=8)
on error goto 0

if myrng is nothing then
'user hit cancel
else
msgbox myrng.address
end if
 
D

DCSwearingen

Thank You for Responding!

I have been searching the help file all morning for this
 
Top