Macro doesn't work on UserInterfaceOnly-protected sheet
 

Shouldnt €˜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 its 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

Macro doesn't work on UserInterfaceOnly-protected sheet
 

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

NickHK

"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

Macro doesn't work on UserInterfaceOnly-protected sheet
 

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

Macro doesn't work on UserInterfaceOnly-protected sheet
 

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

Macro doesn't work on UserInterfaceOnly-protected sheet
 

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" 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

Macro doesn't work on UserInterfaceOnly-protected sheet
 

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

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

Macro doesn't work on UserInterfaceOnly-protected sheet
 

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" wrote in message
...
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

Macro doesn't work on UserInterfaceOnly-protected sheet
 

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" wrote in message
...
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