Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change macro
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 -- Gary's Student gsnu200704 "jcliquidtension" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change macro
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) "jcliquidtension" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
worksheet change macro
Thanks Bob and Gary's Student
"Bob Phillips" wrote: 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) "jcliquidtension" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet Change Macro Help | Excel Discussion (Misc queries) | |||
Worksheet change Macro part 2 | Excel Discussion (Misc queries) | |||
424 error on a Worksheet Change Macro | Excel Programming | |||
Cell value change to trigger macro (worksheet change event?) | Excel Programming | |||
Worksheet Overwrite Macro Change | Excel Programming |