Home |
Search |
Today's Posts |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro doesn't work on UserInterfaceOnly-protected sheet
Thanks -- yes, that code works on my machine. However the increment method is
relative (and so dependent on what happens before it is run) and I really need to move the shape to an absolute position. This code: With ActiveSheet .Shapes("Rectangle 1").Select Selection.Left = .Columns("P").Left End With -- works fine when protection is turned off, but not when preceded by -- With ActiveSheet .EnableSelection = xlUnlockedCells .Protect Password:="pw", UserInterfaceOnly:=True, Contents:=True, DrawingObjects:=True End With So it seems there must be limitations to the 'UserInterfaceOnly:=True' setting with regard to macros making changes? Kasama "NickHK" wrote: Kasama, This works for me: With ActiveSheet .EnableSelection = xlUnlockedCells .Protect Password:="pw", UserInterfaceOnly:=True, Contents:=True, DrawingObjects:=True With .Shapes("Rectangle 2") .IncrementLeft -60.75 .IncrementTop -70.5 .Fill.ForeColor.SchemeColor = 13 .Fill.Solid End With End With So I think the error in the code is not with the protection aspect, but your code placing/moving the shape. NickHK "Kasama" wrote in message ... Yes, thanks, but the protection code is in: Private Sub Workbook_Open() Sheets("Name1").Unprotect Password:="pw" Sheets("Name2").Unprotect Password:="pw" Application.MoveAfterReturn = True Application.MoveAfterReturnDirection = xlDown Sheets("Name1").EnableSelection = xlUnlockedCells Sheets("Name1").Protect Password:="pw", UserInterfaceOnly:=True, Contents:=True, DrawingObjects:=True Sheets("Name2").EnableSelection = xlUnlockedCells Sheets("Name2").Protect Password:="pw", UserInterfaceOnly:=True, Contents:=True, DrawingObjects:=True End Sub -- But it still doesn't work, have I missed something? Kasama "Norman Jones" wrote: Hi Kasama, Your code works for me. However, the UserInterfaceOnly setting is not persistant between Excel sessions. Therefore, you should consider placing the protection code in rhe Workbook_Open procedure or, alternatively, in an Auto_Open macro in a standard module. --- Regards, Norman "Kasama" wrote in message ... Shouldn't 'UserInterfaceOnly:=True' allow macros to change the sheet? My worksheet has a Shape which moves to align with Column V when a macro is run. It works while the sheet is unprotected but when it's protected, the macro fails (Run-time error 1004) even though protection was set for User Interface Only. The 'Protect' macro uses this code: Sheets("Name").Protect Password:="password", UserInterfaceOnly:=True, Contents:=True, DrawingObjects:=True The 'Move' macro which aligns the drawing object selects it and uses this code: Selection.Left = .Columns("V").Left I know I could add code at the beginning and end of 'Move' to Unprotect and Protect the sheet, but I thought that with 'UserInterfaceOnly:=True', I should not need to do this? -- Kasama |
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) | |||
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 | |||
Strange problems with setting hidden property of a range when sheet protected with UserInterfaceOnly | Excel Programming | |||
Macro protected work sheet | Excel Programming |