refer to user selection
 

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

refer to user selection
 

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

Target.Address gives you what you need.

NickHK

"someguy" wrote in message
7.142...
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

refer to user selection
 

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)


"someguy" wrote in message
7.142...
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

refer to user selection
 

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





*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

refer to user selection
 

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).sele ct

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" wrote:

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)


"someguy" wrote in message
7.142...
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

refer to user selection
 

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)


"Neal Zimm" wrote in message
...
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).sele ct

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" wrote:

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)


"someguy" wrote in message
7.142...
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

refer to user selection
 

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" wrote:

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

--

refer to user selection
 

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)


"Neal Zimm" wrote in message
...
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" wrote:

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

--

refer to user selection
 

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


"Neal Zimm" wrote in message
...
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" wrote:

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

--

refer to user selection
 

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.





"Bob Phillips" wrote:

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)


"Neal Zimm" wrote in message
...
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" wrote:

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

--

refer to user selection
 

for a single rectangular contiguous range:

msgbox seletion(selection.count).Address

--
Regards,
Tom Ogilvy

"Neal Zimm" wrote in message
...
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.





"Bob Phillips" wrote:

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)


"Neal Zimm" wrote in message
...
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" wrote:

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

--

refer to user selection
 

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

--
Regards,
Tom Ogilvy

"Neal Zimm" wrote in message
...
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.





"Bob Phillips" wrote:

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)


"Neal Zimm" wrote in message
...
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" wrote:

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

--