worksheet change macro

[Guest]

Hi,
I'm trying to run a code with worksheet changes, but I want to limit the
change event to a couple of cells. In other words, I only want the macro to
run when certain cells in the worksheet change. Can anyone help with this?
I want this macro to run only when cells C4, C11, C18, and C25 change. Also,
since I protected the other sheets, and had to add in the unprotect and then
protect language for each sheet, the sheets are actually selected now when
the marco runs. Is there any way to run this without the other sheets being
selected (which makes a flash of sheets appear while the macro is running).
My existing macro is as follows:

ActiveSheet.Unprotect
With Sheet8
Sheet8.Unprotect
.Range("A1") = Range("Veh1").Value
Sheet8.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With
With Sheet9
Sheet9.Unprotect
.Range("A1") = Range("Veh1R").Value
Sheet9.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With
With Sheet1
Sheet1.Unprotect
.Range("A1") = Range("Veh2").Value
Sheet1.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With
With Sheet4
Sheet4.Unprotect
.Range("A1") = Range("Veh2R").Value
Sheet4.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With
With Sheet3
Sheet3.Unprotect
.Range("A1") = Range("Veh3").Value
Sheet3.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With
With Sheet5
Sheet5.Unprotect
.Range("A1") = Range("Veh3R").Value
Sheet5.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With
With Sheet2
Sheet2.Unprotect
.Range("A1") = Range("Veh4").Value
Sheet2.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With
With Sheet6
Sheet6.Unprotect
.Range("A1") = Range("Veh4R").Value
Sheet6.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
End With

ActiveSheet.Shapes("Button 12").Select
Selection.Characters.Text = Range("Veh1").Value
ActiveSheet.Shapes("Button 11").Select
Selection.Characters.Text = Range("Veh1R").Value
ActiveSheet.Shapes("Button 4").Select
Selection.Characters.Text = Range("Veh2").Value
ActiveSheet.Shapes("Button 7").Select
Selection.Characters.Text = Range("Veh2R").Value
ActiveSheet.Shapes("Button 6").Select
Selection.Characters.Text = Range("Veh3").Value
ActiveSheet.Shapes("Button 10").Select
Selection.Characters.Text = Range("Veh3R").Value
ActiveSheet.Shapes("Button 8").Select
Selection.Characters.Text = Range("Veh4").Value
ActiveSheet.Shapes("Button 5").Select
Selection.Characters.Text = Range("Veh4R").Value
Range("C4").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True

End Sub

Thanks!
Jason

 

[Guest]

For the first question, at the top of your sub:

Set r = Union(Range("C4"), Range("C11"), Range("C18"), Range("C25"))
If Intersect(Target, r) Is Nothing Then
Exit Sub
End If

 

[Bob Phillips]

This is standard way of running worksheet change code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
' do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

You shouldn't be working on all sheets from the same code, just the
applicable sheet, unless the actions are identical, in which case it is best
to use the Workbook_SheetChange event. See
http://www.cpearson.com/excel/events.htm

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Thanks Bob and Gary's Student

This is standard way of running worksheet change code

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "H1:H10" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
' do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

You shouldn't be working on all sheets from the same code, just the
applicable sheet, unless the actions are identical, in which case it is best
to use the Workbook_SheetChange event. See
http://www.cpearson.com/excel/events.htm

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)