Automatically run macro when cell value changes

  • Thread starter Thread starter smilly
  • Start date Start date
S

smilly

Hi,

I use the following macro to insert a header onto a chart located in a
worksheet called "chart" based on a cell reference in a different
worksheet called "jur":

Sub RefHeader()
With ActiveSheet
.PageSetup.LeftHeader = "&""Times New Roman,Bold""&14 " &
Sheets("jur").Range("A2").Text
End With
End Sub

I would like to have the above macro automatically run whenever the
value of a specific cell (C3) in the "jur" worksheet changes. Is this
possible? I have a feeling it must be done with a worksheet change
event function, and I have read the helpful Websites of David
McRitchie, Ron de Bruin, and Charles Pearson, but I fear that I'm too
inexperienced to understand how to make it work without a bit more
nudging along.

Thanks,
Scott
 
One way:

Put this in the "jur" worksheet code module:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Const sHDR = "&""Times New Roman,Bold""&14 "
With Target
If .Count = 1 Then _
If .Address(False, False) = "C3" Then _
Sheets("chart").PageSetup.LeftHeader = _
sHDR & Me.Range("A2").Text
End With
End Sub
 
Hi Scott,

All you have to do is have the Worksheet_Change Macro in the "Jur" sheet
call the RefHeader Macro thus:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Address <> Range("C3").Address Then Exit Sub
RefHeader
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk
 
Scott

If you put RefHeader into a selection change macro on the appropriate
worksheet module...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$3" Then
RefHeader
End If
End Sub

Martin


--
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Visit Eighty-Twenty Spreadsheet Automation for professional customised
spreadsheet development

http://homepage.ntlworld.com/martin.rice1/
 
Back
Top