Macro doesn't work on UserInterfaceOnly-protected sheet

[Kasama]

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?

 

[NickHK]

Kasama,
Did you read the 'remarks" section in the help.
Does this apply ?

NickHK

 

[Norman Jones]

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 Autpen macro in a
standard module.

 

[Kasama]

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

 

[Kasama]

Thanks but it's already in 'Workbook_Open', see reply to next post.

 

[NickHK]

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]

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]

Kasama,
Yes, it will fail because you cannot .Select object when the sheet is
protected.
But you do not need to .Select them to work with them.
With ActiveSheet
.Shapes("Rectangle 1").Left = .Columns("P").Left
End With

NickHK

 

[Kasama]

Thanks -- that answers the question!
--
Kasama

Kasama,
Yes, it will fail because you cannot .Select object when the sheet is
protected.
But you do not need to .Select them to work with them.
With ActiveSheet
.Shapes("Rectangle 1").Left = .Columns("P").Left
End With

NickHK