run macro when cell is selected

I have been trying to figure this out for hours now.

I just want to run a macro when a certain cell is selected.
upon investigation, i have found that I can right click on the sheet tab and
view the code. I can then add the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$a$1" Then
run(macro1)
end if
End Sub

I know that this doesn't work, but I can't figure out how to get it to work.
Any help will be greatly appreciated.

thanks in advance

Melissa

Try this that works, but i don't know why, sorry...
..
One for Selection change, one by formula

Private Sub Worksheet_Change(ByVal Target As Range)
macro Target
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
macro Target
End Sub

Gilles P



"beauty_bobaloo" a écrit :

> I have been trying to figure this out for hours now.
>
> I just want to run a macro when a certain cell is selected.
> upon investigation, i have found that I can right click on the sheet tab and
> view the code. I can then add the following code:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Target.Address = "$a$1" Then
> run(macro1)
> end if
> End Sub
>
> I know that this doesn't work, but I can't figure out how to get it to work.
> Any help will be greatly appreciated.
>
> thanks in advance
>
> Melissa

Change what you to the following:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$a$1" Then macro1
End Sub

"beauty_bobaloo" <[email protected]> wrote in message
news:[email protected]...
>I have been trying to figure this out for hours now.
>
> I just want to run a macro when a certain cell is selected.
> upon investigation, i have found that I can right click on the sheet tab
> and
> view the code. I can then add the following code:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Target.Address = "$a$1" Then
> run(macro1)
> end if
> End Sub
>
> I know that this doesn't work, but I can't figure out how to get it to
> work.
> Any help will be greatly appreciated.
>
> thanks in advance
>
> Melissa

Just a word of warning:

If Target.Address = "$a$1" Then macro1

won't ever fire macro1--assuming "option compare text" isn't included.

..Address will be upper case:
If Target.Address = "$A$1" Then macro1

===
I like this format:

if intersect(target,me.range("a1")) is nothing then
'do nothing
else
macro1
end if

I find it easier to type (no case worries) and easier to add more cells to the
check.



Otto Moehrbach wrote:
>
> Change what you to the following:
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> If Target.Address = "$a$1" Then macro1
> End Sub
>
> "beauty_bobaloo" <[email protected]> wrote in message
> news:[email protected]...
> >I have been trying to figure this out for hours now.
> >
> > I just want to run a macro when a certain cell is selected.
> > upon investigation, i have found that I can right click on the sheet tab
> > and
> > view the code. I can then add the following code:
> >
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > If Target.Address = "$a$1" Then
> > run(macro1)
> > end if
> > End Sub
> >
> > I know that this doesn't work, but I can't figure out how to get it to
> > work.
> > Any help will be greatly appreciated.
> >
> > thanks in advance
> >
> > Melissa

--

Dave Peterson

Dave
Thanks for that. I will remember it. I hope. Otto
"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> Just a word of warning:
>
> If Target.Address = "$a$1" Then macro1
>
> won't ever fire macro1--assuming "option compare text" isn't included.
>
> .Address will be upper case:
> If Target.Address = "$A$1" Then macro1
>
> ===
> I like this format:
>
> if intersect(target,me.range("a1")) is nothing then
> 'do nothing
> else
> macro1
> end if
>
> I find it easier to type (no case worries) and easier to add more cells to
> the
> check.
>
>
>
> Otto Moehrbach wrote:
>>
>> Change what you to the following:
>> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> If Target.Address = "$a$1" Then macro1
>> End Sub
>>
>> "beauty_bobaloo" <[email protected]> wrote in
>> message
>> news:[email protected]...
>> >I have been trying to figure this out for hours now.
>> >
>> > I just want to run a macro when a certain cell is selected.
>> > upon investigation, i have found that I can right click on the sheet
>> > tab
>> > and
>> > view the code. I can then add the following code:
>> >
>> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> > If Target.Address = "$a$1" Then
>> > run(macro1)
>> > end if
>> > End Sub
>> >
>> > I know that this doesn't work, but I can't figure out how to get it to
>> > work.
>> > Any help will be greatly appreciated.
>> >
>> > thanks in advance
>> >
>> > Melissa
>
> --
>
> Dave Peterson

YOU GUYS ARE BRILLIANT!
Thankyou so much, I got it to work just by changing the cell reference to
upper case. I then changed the code as suggested. I am now going to proceed
to do this for about 150 cells. When any of these cells are pressed, I want
it to run the same macro, and use the value in the selected cell in the macro
so as to have a different outcome each time. I should just be able to copy
this code 150 times and put in the different cell references. Do you think
all of this will slow things down too much? Is there maybe a better way to do
this?

thanks again for your help.

Melissa

"Dave Peterson" wrote:

> Just a word of warning:
>
> If Target.Address = "$a$1" Then macro1
>
> won't ever fire macro1--assuming "option compare text" isn't included.
>
> ..Address will be upper case:
> If Target.Address = "$A$1" Then macro1
>
> ===
> I like this format:
>
> if intersect(target,me.range("a1")) is nothing then
> 'do nothing
> else
> macro1
> end if
>
> I find it easier to type (no case worries) and easier to add more cells to the
> check.
>
>
>
> Otto Moehrbach wrote:
> >
> > Change what you to the following:
> > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > If Target.Address = "$a$1" Then macro1
> > End Sub
> >
> > "beauty_bobaloo" <[email protected]> wrote in message
> > news:[email protected]...
> > >I have been trying to figure this out for hours now.
> > >
> > > I just want to run a macro when a certain cell is selected.
> > > upon investigation, i have found that I can right click on the sheet tab
> > > and
> > > view the code. I can then add the following code:
> > >
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > If Target.Address = "$a$1" Then
> > > run(macro1)
> > > end if
> > > End Sub
> > >
> > > I know that this doesn't work, but I can't figure out how to get it to
> > > work.
> > > Any help will be greatly appreciated.
> > >
> > > thanks in advance
> > >
> > > Melissa
>
> --
>
> Dave Peterson
>

Are the cells in a specific area:

if intersect(target,me.range("a1:a9,b12:b33,c1:c99")) is nothing then
'do nothing
else
macro1
end if


beauty_bobaloo wrote:
>
> YOU GUYS ARE BRILLIANT!
> Thankyou so much, I got it to work just by changing the cell reference to
> upper case. I then changed the code as suggested. I am now going to proceed
> to do this for about 150 cells. When any of these cells are pressed, I want
> it to run the same macro, and use the value in the selected cell in the macro
> so as to have a different outcome each time. I should just be able to copy
> this code 150 times and put in the different cell references. Do you think
> all of this will slow things down too much? Is there maybe a better way to do
> this?
>
> thanks again for your help.
>
> Melissa
>
> "Dave Peterson" wrote:
>
> > Just a word of warning:
> >
> > If Target.Address = "$a$1" Then macro1
> >
> > won't ever fire macro1--assuming "option compare text" isn't included.
> >
> > ..Address will be upper case:
> > If Target.Address = "$A$1" Then macro1
> >
> > ===
> > I like this format:
> >
> > if intersect(target,me.range("a1")) is nothing then
> > 'do nothing
> > else
> > macro1
> > end if
> >
> > I find it easier to type (no case worries) and easier to add more cells to the
> > check.
> >
> >
> >
> > Otto Moehrbach wrote:
> > >
> > > Change what you to the following:
> > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > If Target.Address = "$a$1" Then macro1
> > > End Sub
> > >
> > > "beauty_bobaloo" <[email protected]> wrote in message
> > > news:[email protected]...
> > > >I have been trying to figure this out for hours now.
> > > >
> > > > I just want to run a macro when a certain cell is selected.
> > > > upon investigation, i have found that I can right click on the sheet tab
> > > > and
> > > > view the code. I can then add the following code:
> > > >
> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > If Target.Address = "$a$1" Then
> > > > run(macro1)
> > > > end if
> > > > End Sub
> > > >
> > > > I know that this doesn't work, but I can't figure out how to get it to
> > > > work.
> > > > Any help will be greatly appreciated.
> > > >
> > > > thanks in advance
> > > >
> > > > Melissa
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

Yes the cells are in specific areas.

This should work great!

Thankyou again for all of your help. This is the first question that I have
had to post (as I have been able to figure out my problems reading other
posts), And I am surprised and grateful for the prompt and knowledgable
resposes.

Melissa

"Dave Peterson" wrote:

> Are the cells in a specific area:
>
> if intersect(target,me.range("a1:a9,b12:b33,c1:c99")) is nothing then
> 'do nothing
> else
> macro1
> end if
>
>
> beauty_bobaloo wrote:
> >
> > YOU GUYS ARE BRILLIANT!
> > Thankyou so much, I got it to work just by changing the cell reference to
> > upper case. I then changed the code as suggested. I am now going to proceed
> > to do this for about 150 cells. When any of these cells are pressed, I want
> > it to run the same macro, and use the value in the selected cell in the macro
> > so as to have a different outcome each time. I should just be able to copy
> > this code 150 times and put in the different cell references. Do you think
> > all of this will slow things down too much? Is there maybe a better way to do
> > this?
> >
> > thanks again for your help.
> >
> > Melissa
> >
> > "Dave Peterson" wrote:
> >
> > > Just a word of warning:
> > >
> > > If Target.Address = "$a$1" Then macro1
> > >
> > > won't ever fire macro1--assuming "option compare text" isn't included.
> > >
> > > ..Address will be upper case:
> > > If Target.Address = "$A$1" Then macro1
> > >
> > > ===
> > > I like this format:
> > >
> > > if intersect(target,me.range("a1")) is nothing then
> > > 'do nothing
> > > else
> > > macro1
> > > end if
> > >
> > > I find it easier to type (no case worries) and easier to add more cells to the
> > > check.
> > >
> > >
> > >
> > > Otto Moehrbach wrote:
> > > >
> > > > Change what you to the following:
> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > If Target.Address = "$a$1" Then macro1
> > > > End Sub
> > > >
> > > > "beauty_bobaloo" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > >I have been trying to figure this out for hours now.
> > > > >
> > > > > I just want to run a macro when a certain cell is selected.
> > > > > upon investigation, i have found that I can right click on the sheet tab
> > > > > and
> > > > > view the code. I can then add the following code:
> > > > >
> > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> > > > > If Target.Address = "$a$1" Then
> > > > > run(macro1)
> > > > > end if
> > > > > End Sub
> > > > >
> > > > > I know that this doesn't work, but I can't figure out how to get it to
> > > > > work.
> > > > > Any help will be greatly appreciated.
> > > > >
> > > > > thanks in advance
> > > > >
> > > > > Melissa
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Melissa
Did you understand Dave's response that you don't have to copy the code
150 times (in fact you can't do that, it won't work)? Look at what Dave
gave you and you will see that you need only the one macro and that all the
cells will be listed in that one macro. HTH Otto
"beauty_bobaloo" <[email protected]> wrote in message
news:[email protected]...
> Yes the cells are in specific areas.
>
> This should work great!
>
> Thankyou again for all of your help. This is the first question that I
> have
> had to post (as I have been able to figure out my problems reading other
> posts), And I am surprised and grateful for the prompt and knowledgable
> resposes.
>
> Melissa
>
> "Dave Peterson" wrote:
>
>> Are the cells in a specific area:
>>
>> if intersect(target,me.range("a1:a9,b12:b33,c1:c99")) is nothing then
>> 'do nothing
>> else
>> macro1
>> end if
>>
>>
>> beauty_bobaloo wrote:
>> >
>> > YOU GUYS ARE BRILLIANT!
>> > Thankyou so much, I got it to work just by changing the cell reference
>> > to
>> > upper case. I then changed the code as suggested. I am now going to
>> > proceed
>> > to do this for about 150 cells. When any of these cells are pressed, I
>> > want
>> > it to run the same macro, and use the value in the selected cell in the
>> > macro
>> > so as to have a different outcome each time. I should just be able to
>> > copy
>> > this code 150 times and put in the different cell references. Do you
>> > think
>> > all of this will slow things down too much? Is there maybe a better way
>> > to do
>> > this?
>> >
>> > thanks again for your help.
>> >
>> > Melissa
>> >
>> > "Dave Peterson" wrote:
>> >
>> > > Just a word of warning:
>> > >
>> > > If Target.Address = "$a$1" Then macro1
>> > >
>> > > won't ever fire macro1--assuming "option compare text" isn't
>> > > included.
>> > >
>> > > ..Address will be upper case:
>> > > If Target.Address = "$A$1" Then macro1
>> > >
>> > > ===
>> > > I like this format:
>> > >
>> > > if intersect(target,me.range("a1")) is nothing then
>> > > 'do nothing
>> > > else
>> > > macro1
>> > > end if
>> > >
>> > > I find it easier to type (no case worries) and easier to add more
>> > > cells to the
>> > > check.
>> > >
>> > >
>> > >
>> > > Otto Moehrbach wrote:
>> > > >
>> > > > Change what you to the following:
>> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> > > > If Target.Address = "$a$1" Then macro1
>> > > > End Sub
>> > > >
>> > > > "beauty_bobaloo" <[email protected]> wrote
>> > > > in message
>> > > > news:[email protected]...
>> > > > >I have been trying to figure this out for hours now.
>> > > > >
>> > > > > I just want to run a macro when a certain cell is selected.
>> > > > > upon investigation, i have found that I can right click on the
>> > > > > sheet tab
>> > > > > and
>> > > > > view the code. I can then add the following code:
>> > > > >
>> > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
>> > > > > If Target.Address = "$a$1" Then
>> > > > > run(macro1)
>> > > > > end if
>> > > > > End Sub
>> > > > >
>> > > > > I know that this doesn't work, but I can't figure out how to get
>> > > > > it to
>> > > > > work.
>> > > > > Any help will be greatly appreciated.
>> > > > >
>> > > > > thanks in advance
>> > > > >
>> > > > > Melissa
>> > >
>> > > --
>> > >
>> > > Dave Peterson
>> > >
>>
>> --
>>
>> Dave Peterson
>>

Yes, thankyou, I did see that. I have now got it working with only a couple
of lines of code, and it works Great. I have just implemented the project
that I was working on, and the users are very impressed, and so am I.
Thankyou again for all your help.

Melissa

"Otto Moehrbach" wrote:

> Melissa
> Did you understand Dave's response that you don't have to copy the code
> 150 times (in fact you can't do that, it won't work)? Look at what Dave
> gave you and you will see that you need only the one macro and that all the
> cells will be listed in that one macro. HTH Otto
> "beauty_bobaloo" <[email protected]> wrote in message
> news:[email protected]...
> > Yes the cells are in specific areas.
> >
> > This should work great!
> >
> > Thankyou again for all of your help. This is the first question that I
> > have
> > had to post (as I have been able to figure out my problems reading other
> > posts), And I am surprised and grateful for the prompt and knowledgable
> > resposes.
> >
> > Melissa
> >
> > "Dave Peterson" wrote:
> >
> >> Are the cells in a specific area:
> >>
> >> if intersect(target,me.range("a1:a9,b12:b33,c1:c99")) is nothing then
> >> 'do nothing
> >> else
> >> macro1
> >> end if
> >>
> >>
> >> beauty_bobaloo wrote:
> >> >
> >> > YOU GUYS ARE BRILLIANT!
> >> > Thankyou so much, I got it to work just by changing the cell reference
> >> > to
> >> > upper case. I then changed the code as suggested. I am now going to
> >> > proceed
> >> > to do this for about 150 cells. When any of these cells are pressed, I
> >> > want
> >> > it to run the same macro, and use the value in the selected cell in the
> >> > macro
> >> > so as to have a different outcome each time. I should just be able to
> >> > copy
> >> > this code 150 times and put in the different cell references. Do you
> >> > think
> >> > all of this will slow things down too much? Is there maybe a better way
> >> > to do
> >> > this?
> >> >
> >> > thanks again for your help.
> >> >
> >> > Melissa
> >> >
> >> > "Dave Peterson" wrote:
> >> >
> >> > > Just a word of warning:
> >> > >
> >> > > If Target.Address = "$a$1" Then macro1
> >> > >
> >> > > won't ever fire macro1--assuming "option compare text" isn't
> >> > > included.
> >> > >
> >> > > ..Address will be upper case:
> >> > > If Target.Address = "$A$1" Then macro1
> >> > >
> >> > > ===
> >> > > I like this format:
> >> > >
> >> > > if intersect(target,me.range("a1")) is nothing then
> >> > > 'do nothing
> >> > > else
> >> > > macro1
> >> > > end if
> >> > >
> >> > > I find it easier to type (no case worries) and easier to add more
> >> > > cells to the
> >> > > check.
> >> > >
> >> > >
> >> > >
> >> > > Otto Moehrbach wrote:
> >> > > >
> >> > > > Change what you to the following:
> >> > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >> > > > If Target.Address = "$a$1" Then macro1
> >> > > > End Sub
> >> > > >
> >> > > > "beauty_bobaloo" <[email protected]> wrote
> >> > > > in message
> >> > > > news:[email protected]...
> >> > > > >I have been trying to figure this out for hours now.
> >> > > > >
> >> > > > > I just want to run a macro when a certain cell is selected.
> >> > > > > upon investigation, i have found that I can right click on the
> >> > > > > sheet tab
> >> > > > > and
> >> > > > > view the code. I can then add the following code:
> >> > > > >
> >> > > > > Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> >> > > > > If Target.Address = "$a$1" Then
> >> > > > > run(macro1)
> >> > > > > end if
> >> > > > > End Sub
> >> > > > >
> >> > > > > I know that this doesn't work, but I can't figure out how to get
> >> > > > > it to
> >> > > > > work.
> >> > > > > Any help will be greatly appreciated.
> >> > > > >
> >> > > > > thanks in advance
> >> > > > >
> >> > > > > Melissa
> >> > >
> >> > > --
> >> > >
> >> > > Dave Peterson
> >> > >
> >>
> >> --
> >>
> >> Dave Peterson
> >>
>
>
>