# Microsoft Office Application Help - Excel Help forum > Excel Programming / VBA / Macros >  >  How can i get activecell address

## Bond S.C

Hi, everybody

I can get the activecell address.  using below code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("a1").Value = ActiveCell.Address
End Sub

But,  if  I select multi cells (ex : range("b1:f:20")
I can't get activecell.address.
Please let' me know how to get the activecell address in this case.








Bond! <Chon, Sung-Chul>

----------


## KL

Hi,

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("a1").Value = Target.Address
End Sub

Regards,
KL


"Bond S.C" <daumbond at hanmail.net@N0spum.net> wrote in message
news:811C58F6-7380-4F07-BD15-4996F3C0B7CC@microsoft.com...
> Hi, everybody
>
> I can get the activecell address.  using below code.
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> Range("a1").Value = ActiveCell.Address
> End Sub
>
> But,  if  I select multi cells (ex : range("b1:f:20")
> I can't get activecell.address.
> Please let' me know how to get the activecell address in this case.
>
>
>
>
>
>
>
>
> Bond! <Chon, Sung-Chul>

----------


## Bond S.C

Thank you. KL

According to your Code
I get B1:F20 at cell A1

It is not easy to explain about my problem.

Pleas confirm this process
1.  Multi select cells(Drag from B1:C10)
2.  Cell B1 is reversed
3.  When press "Enter Key" the cell only move in the selected area
For example
B1 -> C1  (at that case   I want the cell A1's value is = $C$1
And enter again the reversed cell is from C1 to B2
(at thac case I want the cell A1's value is $B$2


Is there any soluctions ?


> Try this:

>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>     Range("a1").Value = Target.Address
> End Sub
>
> Regards,
> KL
>
>
> "Bond S.C" <daumbond at hanmail.net@N0spum.net> wrote in message
> news:811C58F6-7380-4F07-BD15-4996F3C0B7CC@microsoft.com...
> > Hi, everybody
> >
> > I can get the activecell address.  using below code.
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > Range("a1").Value = ActiveCell.Address
> > End Sub
> >
> > But,  if  I select multi cells (ex : range("b1:f:20")
> > I can't get activecell.address.
> > Please let' me know how to get the activecell address in this case.
> >
> >
> >
> >
> >
> >
> >
> >
> > Bond! <Chon, Sung-Chul>
>
>
>

----------


## Bob Phillips

That is the selection address. If he really means activecell address, he
needs

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Range("a1").Value = Target(1, 1).Address
End Sub


--
HTH

Bob Phillips

"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:eS3Yo1mlFHA.360@TK2MSFTNGP09.phx.gbl...
> Hi,
>
> Try this:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>     Range("a1").Value = Target.Address
> End Sub
>
> Regards,
> KL
>
>
> "Bond S.C" <daumbond at hanmail.net@N0spum.net> wrote in message
> news:811C58F6-7380-4F07-BD15-4996F3C0B7CC@microsoft.com...
> > Hi, everybody
> >
> > I can get the activecell address.  using below code.
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > Range("a1").Value = ActiveCell.Address
> > End Sub
> >
> > But,  if  I select multi cells (ex : range("b1:f:20")
> > I can't get activecell.address.
> > Please let' me know how to get the activecell address in this case.
> >
> >
> >
> >
> >
> >
> >
> >
> > Bond! <Chon, Sung-Chul>
>
>

----------


## KL

Hi Bob,

What if the selection is done from bottom up and/or right to left ?  :-)
Actually the original code should work with multiple selection :-))) LOL

Regards,
KL


"Bob Phillips" <phillips@tiscali.co.uk> wrote in message
news:eUPJjMnlFHA.3336@tk2msftngp13.phx.gbl...
> That is the selection address. If he really means activecell address, he
> needs
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>    Range("a1").Value = Target(1, 1).Address
> End Sub
>
>
> --
> HTH
>
> Bob Phillips
>
> "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> news:eS3Yo1mlFHA.360@TK2MSFTNGP09.phx.gbl...
>> Hi,
>>
>> Try this:
>>
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>>     Range("a1").Value = Target.Address
>> End Sub
>>
>> Regards,
>> KL
>>
>>
>> "Bond S.C" <daumbond at hanmail.net@N0spum.net> wrote in message
>> news:811C58F6-7380-4F07-BD15-4996F3C0B7CC@microsoft.com...
>> > Hi, everybody
>> >
>> > I can get the activecell address.  using below code.
>> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> > Range("a1").Value = ActiveCell.Address
>> > End Sub
>> >
>> > But,  if  I select multi cells (ex : range("b1:f:20")
>> > I can't get activecell.address.
>> > Please let' me know how to get the activecell address in this case.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > Bond! <Chon, Sung-Chul>
>>
>>
>
>

----------


## KL

Opps! AFAIK there isn't an event that captures that change as the selection
doesn't change.

Regards,
KL


"Bond S.C" <daumbond at hanmail.net@N0spum.net> wrote in message
news:846B03A4-B61E-4047-A1AC-759E5994173E@microsoft.com...
> Thank you. KL
>
> According to your Code
> I get B1:F20 at cell A1
>
> It is not easy to explain about my problem.
>
> Pleas confirm this process
> 1.  Multi select cells(Drag from B1:C10)
> 2.  Cell B1 is reversed
> 3.  When press "Enter Key" the cell only move in the selected area
>    For example
>     B1 -> C1  (at that case   I want the cell A1's value is = $C$1
>     And enter again the reversed cell is from C1 to B2
>                     (at thac case I want the cell A1's value is $B$2
>
>
> Is there any soluctions ?
>
>
>> Try this:
>
>>
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>>     Range("a1").Value = Target.Address
>> End Sub
>>
>> Regards,
>> KL
>>
>>
>> "Bond S.C" <daumbond at hanmail.net@N0spum.net> wrote in message
>> news:811C58F6-7380-4F07-BD15-4996F3C0B7CC@microsoft.com...
>> > Hi, everybody
>> >
>> > I can get the activecell address.  using below code.
>> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> > Range("a1").Value = ActiveCell.Address
>> > End Sub
>> >
>> > But,  if  I select multi cells (ex : range("b1:f:20")
>> > I can't get activecell.address.
>> > Please let' me know how to get the activecell address in this case.
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> >
>> > Bond! <Chon, Sung-Chul>
>>
>>
>>

----------


## Greg Wilson

As you have pointed out, when you select a multi-cell range and then
repeatedly press the Enter key, the active cell changes within the selected
range instead of within the entire worksheet. However, since the selection
itself does not change, the SelectionChange event is not fired. And
therefore, you can't return the new active cell address using the
SelectionChange event.

There is no worksheet level event that covers changing the active cell as
opposed to the selection. However, you can always obtain the active cell
address by other means: Change event, BeforeRightClick event, clicking a
button or remapping a key or key combination using OnKey etc.

Do you need to return the active cell address in A1 each time the Enter key
is clicked and at the same time keep the active cell within the selected
range? If so, this will be difficult.

Regards,
Greg


Sub RemapEnterKey

"Bond S.C" wrote:

> Thank you. KL
>
> According to your Code
> I get B1:F20 at cell A1
>
> It is not easy to explain about my problem.
>
> Pleas confirm this process
> 1.  Multi select cells(Drag from B1:C10)
> 2.  Cell B1 is reversed
> 3.  When press "Enter Key" the cell only move in the selected area
>     For example
>      B1 -> C1  (at that case   I want the cell A1's value is = $C$1
>      And enter again the reversed cell is from C1 to B2
>                      (at thac case I want the cell A1's value is $B$2
>
>
> Is there any soluctions ?
>
>
> > Try this:
>
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >     Range("a1").Value = Target.Address
> > End Sub
> >
> > Regards,
> > KL
> >
> >
> > "Bond S.C" <daumbond at hanmail.net@N0spum.net> wrote in message
> > news:811C58F6-7380-4F07-BD15-4996F3C0B7CC@microsoft.com...
> > > Hi, everybody
> > >
> > > I can get the activecell address.  using below code.
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > Range("a1").Value = ActiveCell.Address
> > > End Sub
> > >
> > > But,  if  I select multi cells (ex : range("b1:f:20")
> > > I can't get activecell.address.
> > > Please let' me know how to get the activecell address in this case.
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > >
> > > Bond! <Chon, Sung-Chul>
> >
> >
> >

----------


## Bob Phillips

Morning KL,

You are right, it should be Activecell.Address.

Bob

"KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
news:OgF7vVnlFHA.3256@tk2msftngp13.phx.gbl...
> Hi Bob,
>
> What if the selection is done from bottom up and/or right to left ?  :-)
> Actually the original code should work with multiple selection :-))) LOL
>
> Regards,
> KL
>
>
> "Bob Phillips" <phillips@tiscali.co.uk> wrote in message
> news:eUPJjMnlFHA.3336@tk2msftngp13.phx.gbl...
> > That is the selection address. If he really means activecell address, he
> > needs
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >    Range("a1").Value = Target(1, 1).Address
> > End Sub
> >
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "KL" <NOSPAMlapink2000@PLEASEhotmail.com> wrote in message
> > news:eS3Yo1mlFHA.360@TK2MSFTNGP09.phx.gbl...
> >> Hi,
> >>
> >> Try this:
> >>
> >> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >>     Range("a1").Value = Target.Address
> >> End Sub
> >>
> >> Regards,
> >> KL
> >>
> >>
> >> "Bond S.C" <daumbond at hanmail.net@N0spum.net> wrote in message
> >> news:811C58F6-7380-4F07-BD15-4996F3C0B7CC@microsoft.com...
> >> > Hi, everybody
> >> >
> >> > I can get the activecell address.  using below code.
> >> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >> > Range("a1").Value = ActiveCell.Address
> >> > End Sub
> >> >
> >> > But,  if  I select multi cells (ex : range("b1:f:20")
> >> > I can't get activecell.address.
> >> > Please let' me know how to get the activecell address in this case.
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> >
> >> > Bond! <Chon, Sung-Chul>
> >>
> >>
> >
> >
>
>

----------

