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
|