Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro will not work in protected Excel 2003 Worksheet
Columns D, I , and J are locked (Although my Macro only has to do with B, I,
and J). When I protect the sheet so that only unlocked cells can be selected and edited my macro will not work. What do I need to do? This form will be going out to other employees and I don't want it to be difficult for them and I am protecting it from them for a reason, obviously;). Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect([B:B], Target) Is Nothing Then Application.EnableEvents = False Cells(Target.Row, 9) = Date If Range("J" & Target.Row).Value = "" Then Cells(Target.Row, 10) = Now End If Application.EnableEvents = True End If End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro will not work in protected Excel 2003 Worksheet
In situations like that, I use the UserInterfaceOnly parameter on the
Protect statement. E.g., Worksheets(1).Protect password:="abc",UserInterfaceOnly:=True With UserInterfaceOnly set to True, VBA code can do anything, regardless of protection setting. The protection applies only to actions taken by the user (e.g., by the keyboard or mouse). The UserInterfaceOnly property isn't persistent, so it needs to be set with code with the workbook opens. -- Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group Pearson Software Consulting, LLC www.cpearson.com (email on web site) "LiveUser" wrote in message ... Columns D, I , and J are locked (Although my Macro only has to do with B, I, and J). When I protect the sheet so that only unlocked cells can be selected and edited my macro will not work. What do I need to do? This form will be going out to other employees and I don't want it to be difficult for them and I am protecting it from them for a reason, obviously;). Private Sub Worksheet_Change(ByVal Target As Range) If Not Intersect([B:B], Target) Is Nothing Then Application.EnableEvents = False Cells(Target.Row, 9) = Date If Range("J" & Target.Row).Value = "" Then Cells(Target.Row, 10) = Now End If Application.EnableEvents = True End If End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UDF doesn't work on a protected sheet in Excel 2003 | Excel Discussion (Misc queries) | |||
UDF doesn't work on a protected sheet in Excel 2003 | Excel Discussion (Misc queries) | |||
UDF doesn't work on a protected sheet in Excel 2003 | Excel Discussion (Misc queries) | |||
How to sort protected worksheet in Excel 07? Doesn't work. | Excel Worksheet Functions | |||
Enable filter to work in a protected sheet from Excel 2003 to 2000 | Excel Programming |