Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't work when sheet protected
This is the Macro I have in my work sheet. Even when I set G3:F3 as
un-locked the Macro ceases to reset the color in respect to the cell contents. When I un-protect the sheet it does exactly what it is supposed to. Any suggestions on what to add to the code and where to put it in? (I'm a complete newbie so I really am only cutting and pasting at this point.) Any help would be greatly appreciated. Thanks in advance! Excel-chump '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "g3:f38" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "YES": .Interior.ColorIndex = 4 'green Case "NO": .Interior.ColorIndex = 3 'red Case "A1": .Interior.ColorIndex = 12 'dark yellow Case "A2": .Interior.ColorIndex = 6 'yellow Case 0: .Interior.ColorIndex = 19 'blank End Select End With End If ws_exit: Application.EnableEvents = True End Sub '0 blank '1 black '2 white '3 red '4 green '5 blue '6 yellow '7 magenta '8 cyan '9 maroon '10 dark green '11 dk blue '12 dk yellow '13 dk magenta '14 dk cyan '15 grey '16 dk grey '17 lt purple 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't work when sheet protected
Hi Excel-chump.
Try to unprotect the sheet at the beginning of the routine and protect again at the end That is: Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Activesheet.Unprotect ............. .......your code .......... Activesheet.Protect Regards Eliano On 20 Set, 21:14, excel-chump wrote: This is the Macro I have in my work sheet. Even when I set G3:F3 as un-locked the Macro ceases to reset the color in respect to the cell contents. When I un-protect the sheet it does exactly what it is supposed to. Any suggestions on what to add to the code and where to put it in? (I'm a complete newbie so I really am only cutting and pasting at this point.) Any help would be greatly appreciated. Thanks in advance! Excel-chump '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "g3:f38" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "YES": .Interior.ColorIndex = 4 'green Case "NO": .Interior.ColorIndex = 3 'red Case "A1": .Interior.ColorIndex = 12 'dark yellow Case "A2": .Interior.ColorIndex = 6 'yellow Case 0: .Interior.ColorIndex = 19 'blank End Select End With End If ws_exit: Application.EnableEvents = True End Sub '0 blank '1 black '2 white '3 red '4 green '5 blue '6 yellow '7 magenta '8 cyan '9 maroon '10 dark green '11 dk blue '12 dk yellow '13 dk magenta '14 dk cyan '15 grey '16 dk grey '17 lt purple 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't work when sheet protected
Thank you very much Eliano! It works perfectly! I really do feel like a
chump for it being so simple though. <g Excel-chump "eliano" wrote: Hi Excel-chump. Try to unprotect the sheet at the beginning of the routine and protect again at the end That is: Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Activesheet.Unprotect ............. .......your code .......... Activesheet.Protect Regards Eliano On 20 Set, 21:14, excel-chump wrote: This is the Macro I have in my work sheet. Even when I set G3:F3 as un-locked the Macro ceases to reset the color in respect to the cell contents. When I un-protect the sheet it does exactly what it is supposed to. Any suggestions on what to add to the code and where to put it in? (I'm a complete newbie so I really am only cutting and pasting at this point.) Any help would be greatly appreciated. Thanks in advance! Excel-chump '----------------------------------------------------------------- Private Sub Worksheet_Change(ByVal Target As Range) '----------------------------------------------------------------- Const WS_RANGE As String = "g3:f38" '<=== change to suit On Error GoTo ws_exit: Application.EnableEvents = False If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then With Target Select Case .Value Case "YES": .Interior.ColorIndex = 4 'green Case "NO": .Interior.ColorIndex = 3 'red Case "A1": .Interior.ColorIndex = 12 'dark yellow Case "A2": .Interior.ColorIndex = 6 'yellow Case 0: .Interior.ColorIndex = 19 'blank End Select End With End If ws_exit: Application.EnableEvents = True End Sub '0 blank '1 black '2 white '3 red '4 green '5 blue '6 yellow '7 magenta '8 cyan '9 maroon '10 dark green '11 dk blue '12 dk yellow '13 dk magenta '14 dk cyan '15 grey '16 dk grey '17 lt purple 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
protected work sheet | Excel Discussion (Misc queries) | |||
Protected work sheet | Excel Discussion (Misc queries) | |||
Macro doesn't work on UserInterfaceOnly-protected sheet | Excel Programming | |||
If I have a work sheet protected and try to run a macro to hide rows or columns it won't work. Correct? | Excel Programming | |||
Macro protected work sheet | Excel Programming |