Cell Change activating macro. How do you make it happen?

  • Thread starter Thread starter John Baker
  • Start date Start date
J

John Baker

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
 
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
 
It is event code

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

Paste in the code.
 
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?
 
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
 
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
 
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
 
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
 
Back
Top