refer to user selection

[someguy]

Hi,

I'm trying to write a macro that can refer to certain cells that the
user has selected. Basically i want the user to be able to select certain
cells and call this macro. The macro then look at each cell and if there
is a value in the cell it add quotation marks to the value. How do I refer
to the range the user selected from my function/sub?

TIA

 

[NickHK]

someguy,
Look into the event:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Target.Address gives you what you need.

NickHK

 

[Bob Phillips]

When the user selects cells, these can be accessed via the Selection range
property, such as

For Each cell In Selection
'do stuff on cell
Next cell

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Inderjit Rai]

Thanks Bob.....that was a lot easier than I thought

 

[Guest]

Hi Bob -
I've got a roughly similar problem. In my application I'm building some
macros for customized cut and paste functions. (I'm NOT interested in the
value in the cells)

if the user selects one cell, (the active cell) l've been able to use;
row=activecell.row and col=activecell.column
in order to build statements like:
range(cells(row,col),cells(row+var1,col+var2).select

My problem is that I've not been able to figure out from the Excel docum in
range properties how to get the row and column "coordinates" when the user
has selected part of a column, A1:A9 for example, or more importantly,
a "traditional" range like B2:E9

how do I get the upper left and lower right row and column numbers for the
cells that 'define' a user selected range?

Many thanks,
Neal Zimm

 

[Bob Phillips]

Neal,

When you have a multi-cell selection, just get the first cell, like

row = Selection.Cells(1,1).Row

col = Selection.Cells(1,1).Column

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks, much, and I HATE being a pest like this, but what about the cell in
the lower right? I need to know the 'size' of the selected range. Since I
wrote you this morning, I tried the loop you mentioned to 'someguy' and was
able to make each cell the activecell and use the max function to trap the
highest row and column values but I'm hopeful there's a better way. I'll
see what Excel has on the keyword 'selection' in the interim.
Thanks in advance, again,
Neal Z

 

[Bob Phillips]

You could try

With Selection
row = .Cells(1,1).Row + .Rows.Count - 1
col = .Cells(1,1).Column + .Columns.Count - 1
End With

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Steve]

I missed the first part of the thread but, does this help:

Mid(Selection.Address, InStr(Selection.Address, ":") + 1,
Len(Selection.Address) - InStr(Selection.Address, ":"))

Steve

 

[Guest]

Thanks so much for the speedy reply, I'll try this out as soon as I finish
this reply.
I'm sure you know this, but I wrote the following 'on a lark'

Dim text As String
text = Selection.Address
MsgBox text

and it yielded: $D$5:$I$9

the thought of writing or stealing a routine to parse the answer was not
thrilling.
Again, Thanks.
Neal Z.

 

[Tom Ogilvy]

for a single rectangular contiguous range:

msgbox seletion(selection.count).Address

 

[Tom Ogilvy]

selection(selection.count).row
selection(selection.count).column