Hi

I have a situation where I would like to activate a macro after a change in the contents
of a single cell. I know that this can be done for applications and worksheets, but can
someone tell me how to make it happen for a single cell. Note that I am not very good at
VBA coding, so any details would be helpful.

Thanks

John Baker

John,

You can use the Worksheet_Change event procedure and test which cell was
changed. For example,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
MsgBox "Do something "
End If
End Sub

--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"John Baker" wrote in message
...
Hi

I have a situation where I would like to activate a macro after a change
in the contents
of a single cell. I know that this can be done for applications and
worksheets, but can
someone tell me how to make it happen for a single cell. Note that I am
not very good at
VBA coding, so any details would be helpful.

Thanks

John Baker

Chip:

Thanks. I Tried this (using the appropriate cell designation), and it didn't work when set
up as a macro. It also didn't work when set up as a VBA item. Is the problem that i need
to execute it in the startup (to make it active), or is it that it doesn't point to the
actual sheet (name "input) where the cell at interest resides?

Thanks a lot for your help on this.

regards

John Baker
"Chip Pearson" wrote:

John,

You can use the Worksheet_Change event procedure and test which cell was
changed. For example,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
MsgBox "Do something "
End If
End Sub

It is event code

Right click on the worksheet tab where you want this behavior. Select View
code.

Paste in the code.

--
Regards,
Tom Ogilvy

"John Baker" wrote in message
...
Chip:

Thanks. I Tried this (using the appropriate cell designation), and it
didn't work when set
up as a macro. It also didn't work when set up as a VBA item. Is the
problem that i need
to execute it in the startup (to make it active), or is it that it doesn't
point to the
actual sheet (name "input) where the cell at interest resides?

Thanks a lot for your help on this.

regards

John Baker
"Chip Pearson" wrote:

John,

You can use the Worksheet_Change event procedure and test which cell was
changed. For example,

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$2" Then
MsgBox "Do something "
End If
End Sub

Tom:

Thanks for the advice.

The following is exactly what I pasted into the worksheet code sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$f$5" Then
Msg box "Do something "
End If
End Sub

However, nothing happens when I change the contents of F5 and press enter! When I change
the cell contents, press enter, leave the cell, and then later return and click on it the
"DO SOMETHING" box activates.

Is this the way its supposed to happen?


"Tom Ogilvy" wrote:

It is event code

Right click on the worksheet tab where you want this behavior. Select View
code.

Paste in the code.

John,

The problem is most likely the comparison with the address: by default, VBA
does case sensitive comparisons, and Address will return $F$5 and your code
has $f$5. At the very top of the code module, before any procedure or
declaration, include

Option Compare Text


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"John Baker" wrote in message
...
Tom:

Thanks for the advice.

The following is exactly what I pasted into the worksheet code sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$f$5" Then
Msg box "Do something "
End If
End Sub

However, nothing happens when I change the contents of F5 and press enter!
When I change
the cell contents, press enter, leave the cell, and then later return and
click on it the
"DO SOMETHING" box activates.

Is this the way its supposed to happen?


"Tom Ogilvy" wrote:

It is event code

Right click on the worksheet tab where you want this behavior. Select
View
code.

Paste in the code.

John,

It doesn't work as intended because you use Worksheet_SelectionChange instead of Worksheet_Change as Chip wrote.

HTH
Anders Silven


"John Baker" skrev i meddelandet ...
Tom:

Thanks for the advice.

The following is exactly what I pasted into the worksheet code sheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Target.Address = "$f$5" Then
Msg box "Do something "
End If
End Sub

However, nothing happens when I change the contents of F5 and press enter! When I change
the cell contents, press enter, leave the cell, and then later return and click on it the
"DO SOMETHING" box activates.

Is this the way its supposed to happen?


"Tom Ogilvy" wrote:

It is event code

Right click on the worksheet tab where you want this behavior. Select View
code.

Paste in the code.

Hi John,

Right-click on the sheet tab in question and select 'View Code'. In
the VBE paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address < "$A$1" Then Exit Sub
Call ARoutine
End Sub

And in a general module (eg Module1) paste the routine to be called:

Sub ARoutine()
MsgBox "Your macro here!"
End Sub

Just change the A1 reference to the cell of your choice and amend
ARoutine to the routine in question.

HTH
Richie

John Baker wrote in message . ..
Hi

I have a situation where I would like to activate a macro after a change in the contents
of a single cell. I know that this can be done for applications and worksheets, but can
someone tell me how to make it happen for a single cell. Note that I am not very good at
VBA coding, so any details would be helpful.

Thanks

John Baker

Thanks

Foolish me..I didnt take the advice of experts the first time!
In fact I copied one of the early solutions, and then just modified the others and missed
the critical change.

Thank you all very much. That was most informative!

Regards

John


(Richie UK) wrote:

Hi John,

Right-click on the sheet tab in question and select 'View Code'. In
the VBE paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address < "$A$1" Then Exit Sub
Call ARoutine
End Sub

And in a general module (eg Module1) paste the routine to be called:

Sub ARoutine()
MsgBox "Your macro here!"
End Sub

Just change the A1 reference to the cell of your choice and amend
ARoutine to the routine in question.

HTH
Richie

John Baker wrote in message . ..
Hi

I have a situation where I would like to activate a macro after a change in the contents
of a single cell. I know that this can be done for applications and worksheets, but can
someone tell me how to make it happen for a single cell. Note that I am not very good at
VBA coding, so any details would be helpful.

Thanks

John Baker

Guys:

I am embarrassed to ask this, but here goes.

The suggestions you all have made work magnificently, but I have found that there are TWO
cells that need to trigger the SAME macro when they change (the month and the year). I
tried putting in a second copy of the macro in on the worksheet level, pointing to the
second cell, but it complained that it was a duplicate name (quite justly). I tried
various combinations to refer to the two cells F5 and F6, but none of them worked.

How do I refer to two adjacent cells in the formula you gave me?

Thanks again

John Baker

(Richie UK) wrote:

Hi John,

Right-click on the sheet tab in question and select 'View Code'. In
the VBE paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address < "$A$1" Then Exit Sub
Call ARoutine
End Sub

And in a general module (eg Module1) paste the routine to be called:

Sub ARoutine()
MsgBox "Your macro here!"
End Sub

Just change the A1 reference to the cell of your choice and amend
ARoutine to the routine in question.

HTH
Richie

John Baker wrote in message . ..
Hi

I have a situation where I would like to activate a macro after a change in the contents
of a single cell. I know that this can be done for applications and worksheets, but can
someone tell me how to make it happen for a single cell. Note that I am not very good at
VBA coding, so any details would be helpful.

Thanks

John Baker

I not intersect(Target,Range("F5:F6")) is nothing then


End if

--
Regards,
Tom Ogilvy

John Baker wrote in message
...
Guys:

I am embarrassed to ask this, but here goes.

The suggestions you all have made work magnificently, but I have found
that there are TWO
cells that need to trigger the SAME macro when they change (the month and
the year). I
tried putting in a second copy of the macro in on the worksheet level,
pointing to the
second cell, but it complained that it was a duplicate name (quite
justly). I tried
various combinations to refer to the two cells F5 and F6, but none of them
worked.

How do I refer to two adjacent cells in the formula you gave me?

Thanks again

John Baker

(Richie UK) wrote:

Hi John,

Right-click on the sheet tab in question and select 'View Code'. In
the VBE paste in the following code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address < "$A$1" Then Exit Sub
Call ARoutine
End Sub

And in a general module (eg Module1) paste the routine to be called:

Sub ARoutine()
MsgBox "Your macro here!"
End Sub

Just change the A1 reference to the cell of your choice and amend
ARoutine to the routine in question.

HTH
Richie

John Baker wrote in message
. ..
Hi

I have a situation where I would like to activate a macro after a
change in the contents
of a single cell. I know that this can be done for applications and
worksheets, but can
someone tell me how to make it happen for a single cell. Note that I am
not very good at
VBA coding, so any details would be helpful.

Thanks

John Baker