How to Call Macros to Activate once a cell's value has changed?

  • Thread starter Thread starter hce
  • Start date Start date
H

hce

Hi all

I realise how stupid i m because i can't remember how i used to d
these things... it's been a while since i last touched vb again... ho
can i get the macros to run once a cell's value has changed and how
can get the macros to run upon opening the workbook?

cheer
 
Hi
1. for the first question use the worksheet_change event of your
worksheet. e.g.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
With Target
if .value <>"" then
msgbox "Value in cell A1: " & .value
end if
End With
End Sub

You may also consider using the worksheet_calculate event instead of
the worksheet_change event

2. Put your code either in the workbook_open event of your workboor or
name your macro 'Auto_Open'
 
Hi Frank,

There is no need to test Target.Cells.Count as Target is a range, so it has
its own Count property. So Target.Count > 1 is sufficient.

And why are you suggesting the calculate event, as the OP's limited question
would classically suggest the change event to me.

OP, just to clarify, when Franks says put your code in the Auto_Open or
Workbook_Open event, he is simply referring to any code to be acted upon,
not the full Worksheet_Change code, as that has to be associated with the
parent worksheet.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi Bob
thanks for the tip with the count property ('target.cells.count' was
just a habit + copy/paste of code).

Calculate event: In respect to the OP's very short description I also
made the assumption that the worksheet change event probably would be
sufficient (the claculate event was just an addition if the OP has
something else in mind and the cell value in A1 is changed by a
formula)
 
Thanks, I was just interested in case there was a deep thought going on
there.

Bob
 
Back
Top