| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| 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 | |||
Linear Mode
