[SOLVED] 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

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

Target.Address gives you what you need.

NickHK

"someguy" <inderjitrai@yahoo.com> wrote in message
news:Xns9606EA2DC6F21inderjitraiyahoocom@216.196.97.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

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" <inderjitrai@yahoo.com> wrote in message
news:Xns9606EA2DC6F21inderjitraiyahoocom@216.196.97.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

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!

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" 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" <inderjitrai@yahoo.com> wrote in message
> news:Xns9606EA2DC6F21inderjitraiyahoocom@216.196.97.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
>
>
>

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" <nealzimm@yahoo.com> wrote in message
news:6E5D59E9-542E-4256-B055-B2AB5D27D8A0@microsoft.com...
> 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" 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" <inderjitrai@yahoo.com> wrote in message
> > news:Xns9606EA2DC6F21inderjitraiyahoocom@216.196.97.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
> >
> >
> >

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
>
> --

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" <nealzimm@yahoo.com> wrote in message
news:A534C557-5010-4E05-9CB4-E1333E797167@microsoft.com...
> 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
> >
> > --
>

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" <nealzimm@yahoo.com> wrote in message
news:A534C557-5010-4E05-9CB4-E1333E797167@microsoft.com...
> 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
>>
>> --
>

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" <nealzimm@yahoo.com> wrote in message
> news:A534C557-5010-4E05-9CB4-E1333E797167@microsoft.com...
> > 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
> > >
> > > --
> >
>
>
>

for a single rectangular contiguous range:

msgbox seletion(selection.count).Address

--
Regards,
Tom Ogilvy

"Neal Zimm" <nealzimm@yahoo.com> wrote in message
news:EEC39936-AC8A-4173-9DE3-B3BAB7A02F9F@microsoft.com...
> 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" <nealzimm@yahoo.com> wrote in message
> > news:A534C557-5010-4E05-9CB4-E1333E797167@microsoft.com...
> > > 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
> > > >
> > > > --
> > >
> >
> >
> >

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

--
Regards,
Tom Ogilvy

"Neal Zimm" <nealzimm@yahoo.com> wrote in message
news:EEC39936-AC8A-4173-9DE3-B3BAB7A02F9F@microsoft.com...
> 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" <nealzimm@yahoo.com> wrote in message
> > news:A534C557-5010-4E05-9CB4-E1333E797167@microsoft.com...
> > > 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
> > > >
> > > > --
> > >
> >
> >
> >