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.


--
DCSwearingen


------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=535965

That is Selection, as in

Msgbox Selection.Address

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"DCSwearingen"
wrote in message
news:DCSwearingen.26trd2_1145976316.3013@excelforu m-nospam.com...

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.


--
DCSwearingen


------------------------------------------------------------------------
DCSwearingen's Profile:
http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=535965

Hi,

Here is a example of the With Selection:

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

Regards,
Bondi

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



DCSwearingen wrote:

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.

--
DCSwearingen

------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=535965

--

Dave Peterson

Thank You for Responding!

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


--
DCSwearingen


------------------------------------------------------------------------
DCSwearingen's Profile: http://www.excelforum.com/member.php...o&userid=21506
View this thread: http://www.excelforum.com/showthread...hreadid=535965