Macro doesn't work on UserInterfaceOnly-protected sheet

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

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

NickHK

"Kasama" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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


"NickHK" wrote:

> Kasama,
> Did you read the 'remarks" section in the help.
> Does this apply ?
>
> NickHK
>
> "Kasama" <[email protected]> wrote in message
> news:[email protected]...
> > 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
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> >
> >
> >

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > >
> > >
> > >
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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
> > > >
> > > >
> > > >
> >
> >
> >

Thanks -- that answers the question!
--
Kasama


"NickHK" wrote:

> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > > news:[email protected]...
> > > > > > 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
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>