Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly question.
Windows XP, xl2003:
On one computer... ActiveSheet.Protect UserInterfaceOnly := True works fine. On another computer (also Windows XP, xl2003), after the same code line is entered it appears as... ActiveSheet.Protect userinterfaceonly := True and, of course, does not work. Any ideas as to what is responsible for this difference and how I can fix it. Ken Johnson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly question.
Hi Ken,
In what way does your code not work? --- Regards, Norman "Ken Johnson" wrote in message ups.com... Windows XP, xl2003: On one computer... ActiveSheet.Protect UserInterfaceOnly := True works fine. On another computer (also Windows XP, xl2003), after the same code line is entered it appears as... ActiveSheet.Protect userinterfaceonly := True and, of course, does not work. Any ideas as to what is responsible for this difference and how I can fix it. Ken Johnson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly question.
On Apr 21, 6:12 pm, "Norman Jones"
wrote: Hi Ken, In what way does your code not work? --- Regards, Norman "Ken Johnson" wrote in message ups.com... Windows XP, xl2003: On one computer... ActiveSheet.Protect UserInterfaceOnly := True works fine. On another computer (also Windows XP, xl2003), after the same code line is entered it appears as... ActiveSheet.Protect userinterfaceonly := True and, of course, does not work. Any ideas as to what is responsible for this difference and how I can fix it. Ken Johnson Hi Norman, Here's a simplified version of what I'm trying to do... Sub SaveSelectUnLocked() Dim SaveAsFilename ActiveSheet.Unprotect Cells.Locked = True With Range("A1,B2,C3") .Locked = False .FormulaHidden = False End With With ActiveSheet .EnableSelection = xlUnlockedCells .Protect UserInterfaceOnly:= True End With ActiveWorkbook.Protect SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="", _ filefilter:="Microsoft Office Excel Workbook (*.xls),*xls") If SaveAsFilename < False Then ActiveWorkbook.SaveAs(SaveAsFilename) End Sub I'm wanting the macro to make a SaveAs copy of the workbook with particular cells unlocked and the sheet protected so that only the unlocked cells can be selected. There is just the one sheet in the workbook. When I run the macro on my computer (Windows XP, xl2003), the Save As dialog appears, I select Desktop for Save in, type in a new filename, click OK and the resulting workbook that appears is protected and only the locked cells can be selected, which is what I am wanting. However, when I close the workbook, then reopen it by double clicking the desktop icon it is protected but all cells can be selected. When I run the macro on my son's computer (also XP and xl2003) it works perfectly, ie the reopened SaveAs copy's locked cells cannot be selected. Barb Reinhardt put me on to using UserInterfaceOnly:=True, and the first time I tried it, luckily, was on my son's computer. When I went back to my computer I typed in userinterfaceonly:=true, then when the cursor left that line it changed to userinterfaceonly:= True instead of UserInterfaceOnly:= True, and of course the macro failed to produce the desired result. I thought I had hit on a solution when I went to the Protect Method Help file and copied the following code example... ActiveSheet.Protect Scenarios:= True, UserInterfaceOnly:= True When I pasted this line into my macro (just as an extra line) I noticed that the userinterfaceonly:= True changed to UserInterfaceOnly:= True. I thought that was very weird. Unfortunately, even though the code now looked like it would work, it still failed to produce a SaveAs copy with locked cells that could not be selected after reopening. I tried restarting my computer, but the problem remains. My computer's an old Pentium 3, while my son uses an AMD64. Thanks for replying. Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly question.
On Apr 21, 10:00 pm, Ken Johnson wrote:
On Apr 21, 6:12 pm, "Norman Jones" wrote: Hi Ken, In what way does your code not work? --- Regards, Norman "Ken Johnson" wrote in message oups.com... Windows XP, xl2003: On one computer... ActiveSheet.Protect UserInterfaceOnly := True works fine. On another computer (also Windows XP, xl2003), after the same code line is entered it appears as... ActiveSheet.Protect userinterfaceonly := True and, of course, does not work. Any ideas as to what is responsible for this difference and how I can fix it. Ken Johnson Hi Norman, Here's a simplified version of what I'm trying to do... Sub SaveSelectUnLocked() Dim SaveAsFilename ActiveSheet.Unprotect Cells.Locked = True With Range("A1,B2,C3") .Locked = False .FormulaHidden = False End With With ActiveSheet .EnableSelection = xlUnlockedCells .Protect UserInterfaceOnly:= True End With ActiveWorkbook.Protect SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="", _ filefilter:="Microsoft Office Excel Workbook (*.xls),*xls") If SaveAsFilename < False Then ActiveWorkbook.SaveAs(SaveAsFilename) End Sub I'm wanting the macro to make a SaveAs copy of the workbook with particular cells unlocked and the sheet protected so that only the unlocked cells can be selected. There is just the one sheet in the workbook. When I run the macro on my computer (Windows XP, xl2003), the Save As dialog appears, I select Desktop for Save in, type in a new filename, click OK and the resulting workbook that appears is protected and only the locked cells can be selected, which is what I am wanting. However, when I close the workbook, then reopen it by double clicking the desktop icon it is protected but all cells can be selected. When I run the macro on my son's computer (also XP and xl2003) it works perfectly, ie the reopened SaveAs copy's locked cells cannot be selected. Barb Reinhardt put me on to using UserInterfaceOnly:=True, and the first time I tried it, luckily, was on my son's computer. When I went back to my computer I typed in userinterfaceonly:=true, then when the cursor left that line it changed to userinterfaceonly:= True instead of UserInterfaceOnly:= True, and of course the macro failed to produce the desired result. I thought I had hit on a solution when I went to the Protect Method Help file and copied the following code example... ActiveSheet.Protect Scenarios:= True, UserInterfaceOnly:= True When I pasted this line into my macro (just as an extra line) I noticed that the userinterfaceonly:= True changed to UserInterfaceOnly:= True. I thought that was very weird. Unfortunately, even though the code now looked like it would work, it still failed to produce a SaveAs copy with locked cells that could not be selected after reopening. I tried restarting my computer, but the problem remains. My computer's an old Pentium 3, while my son uses an AMD64. Thanks for replying. Ken Johnson Oops! In the 2nd paragraph after the code I meant to type "only the unlocked cells can be selected" Ken Johnson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly question.
Hi Ken,
The UserInterfaceOnly argument is not persistent between Excel sessions. Therefore, try protecting the sheet in the Workbook_Open event or, alternatively, use Auto_Open. --- Regards, Norman "Ken Johnson" wrote in message oups.com... On Apr 21, 6:12 pm, "Norman Jones" wrote: Hi Ken, In what way does your code not work? --- Regards, Norman "Ken Johnson" wrote in message ups.com... Windows XP, xl2003: On one computer... ActiveSheet.Protect UserInterfaceOnly := True works fine. On another computer (also Windows XP, xl2003), after the same code line is entered it appears as... ActiveSheet.Protect userinterfaceonly := True and, of course, does not work. Any ideas as to what is responsible for this difference and how I can fix it. Ken Johnson Hi Norman, Here's a simplified version of what I'm trying to do... Sub SaveSelectUnLocked() Dim SaveAsFilename ActiveSheet.Unprotect Cells.Locked = True With Range("A1,B2,C3") .Locked = False .FormulaHidden = False End With With ActiveSheet .EnableSelection = xlUnlockedCells .Protect UserInterfaceOnly:= True End With ActiveWorkbook.Protect SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="", _ filefilter:="Microsoft Office Excel Workbook (*.xls),*xls") If SaveAsFilename < False Then ActiveWorkbook.SaveAs(SaveAsFilename) End Sub I'm wanting the macro to make a SaveAs copy of the workbook with particular cells unlocked and the sheet protected so that only the unlocked cells can be selected. There is just the one sheet in the workbook. When I run the macro on my computer (Windows XP, xl2003), the Save As dialog appears, I select Desktop for Save in, type in a new filename, click OK and the resulting workbook that appears is protected and only the locked cells can be selected, which is what I am wanting. However, when I close the workbook, then reopen it by double clicking the desktop icon it is protected but all cells can be selected. When I run the macro on my son's computer (also XP and xl2003) it works perfectly, ie the reopened SaveAs copy's locked cells cannot be selected. Barb Reinhardt put me on to using UserInterfaceOnly:=True, and the first time I tried it, luckily, was on my son's computer. When I went back to my computer I typed in userinterfaceonly:=true, then when the cursor left that line it changed to userinterfaceonly:= True instead of UserInterfaceOnly:= True, and of course the macro failed to produce the desired result. I thought I had hit on a solution when I went to the Protect Method Help file and copied the following code example... ActiveSheet.Protect Scenarios:= True, UserInterfaceOnly:= True When I pasted this line into my macro (just as an extra line) I noticed that the userinterfaceonly:= True changed to UserInterfaceOnly:= True. I thought that was very weird. Unfortunately, even though the code now looked like it would work, it still failed to produce a SaveAs copy with locked cells that could not be selected after reopening. I tried restarting my computer, but the problem remains. My computer's an old Pentium 3, while my son uses an AMD64. Thanks for replying. Ken Johnson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly question.
Hi Ken,
I should have added that, similarly, the EnableSelection property setting will not persist once the workbook is closed --- Regards, Norman |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly question.
On Apr 21, 10:35 pm, "Norman Jones"
wrote: Hi Ken, The UserInterfaceOnly argument is not persistent between Excel sessions. Therefore, try protecting the sheet in the Workbook_Open event or, alternatively, use Auto_Open. --- Regards, Norman "Ken Johnson" wrote in message oups.com... On Apr 21, 6:12 pm, "Norman Jones" wrote: Hi Ken, In what way does your code not work? --- Regards, Norman "Ken Johnson" wrote in message roups.com... Windows XP, xl2003: On one computer... ActiveSheet.Protect UserInterfaceOnly := True works fine. On another computer (also Windows XP, xl2003), after the same code line is entered it appears as... ActiveSheet.Protect userinterfaceonly := True and, of course, does not work. Any ideas as to what is responsible for this difference and how I can fix it. Ken Johnson Hi Norman, Here's a simplified version of what I'm trying to do... Sub SaveSelectUnLocked() Dim SaveAsFilename ActiveSheet.Unprotect Cells.Locked = True With Range("A1,B2,C3") .Locked = False .FormulaHidden = False End With With ActiveSheet .EnableSelection = xlUnlockedCells .Protect UserInterfaceOnly:= True End With ActiveWorkbook.Protect SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="", _ filefilter:="Microsoft Office Excel Workbook (*.xls),*xls") If SaveAsFilename < False Then ActiveWorkbook.SaveAs(SaveAsFilename) End Sub I'm wanting the macro to make a SaveAs copy of the workbook with particular cells unlocked and the sheet protected so that only the unlocked cells can be selected. There is just the one sheet in the workbook. When I run the macro on my computer (Windows XP, xl2003), the Save As dialog appears, I select Desktop for Save in, type in a new filename, click OK and the resulting workbook that appears is protected and only the locked cells can be selected, which is what I am wanting. However, when I close the workbook, then reopen it by double clicking the desktop icon it is protected but all cells can be selected. When I run the macro on my son's computer (also XP and xl2003) it works perfectly, ie the reopened SaveAs copy's locked cells cannot be selected. Barb Reinhardt put me on to using UserInterfaceOnly:=True, and the first time I tried it, luckily, was on my son's computer. When I went back to my computer I typed in userinterfaceonly:=true, then when the cursor left that line it changed to userinterfaceonly:= True instead of UserInterfaceOnly:= True, and of course the macro failed to produce the desired result. I thought I had hit on a solution when I went to the Protect Method Help file and copied the following code example... ActiveSheet.Protect Scenarios:= True, UserInterfaceOnly:= True When I pasted this line into my macro (just as an extra line) I noticed that the userinterfaceonly:= True changed to UserInterfaceOnly:= True. I thought that was very weird. Unfortunately, even though the code now looked like it would work, it still failed to produce a SaveAs copy with locked cells that could not be selected after reopening. I tried restarting my computer, but the problem remains. My computer's an old Pentium 3, while my son uses an AMD64. Thanks for replying. Ken Johnson Hi Norman, It is persistent between sessions on my son's computer, but not on mine. I thought this lack of persistence was removed after xl2000. Also, I need to avoid VBA (I will be deleting it from the SavedAs copy) because the final workbook will be used where I'm expecting a lot of reluctance to opening workbooks with macros. Ken Johnson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly question.
Hi Ken.
In xl2002+ the EnableSelection property can be set manually, as a protection option, and will persist. --- Regards, Norman Hi Norman, It is persistent between sessions on my son's computer, but not on mine. I thought this lack of persistence was removed after xl2000. Also, I need to avoid VBA (I will be deleting it from the SavedAs copy) because the final workbook will be used where I'm expecting a lot of reluctance to opening workbooks with macros. Ken Johnson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly question.
On Apr 21, 11:53 pm, "Norman Jones"
wrote: Hi Ken. In xl2002+ the EnableSelection property can be set manually, as a protection option, and will persist. --- Regards, Norman Hi Norman, It is persistent between sessions on my son's computer, but not on mine. I thought this lack of persistence was removed after xl2000. Also, I need to avoid VBA (I will be deleting it from the SavedAs copy) because the final workbook will be used where I'm expecting a lot of reluctance to opening workbooks with macros. Ken Johnson Hi Norman, I just tested on my son's computer and it is as you say. I made sure the worksheet started out manually protected with select unlocked cells and select locked cell, ran the code, exited excel and reopened the SaveAs copy and all cells could be selected, so it does not persist between sessions when set programatically, which is a pity. Thanks for helping me clear that up. Ken Johnson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
UserInterfaceOnly question.
On Apr 22, 12:15 am, Ken Johnson wrote:
On Apr 21, 11:53 pm, "Norman Jones" wrote: Hi Ken. In xl2002+ the EnableSelection property can be set manually, as a protection option, and will persist. --- Regards, Norman Hi Norman, It is persistent between sessions on my son's computer, but not on mine. I thought this lack of persistence was removed after xl2000. Also, I need to avoid VBA (I will be deleting it from the SavedAs copy) because the final workbook will be used where I'm expecting a lot of reluctance to opening workbooks with macros. Ken Johnson Hi Norman, I just tested on my son's computer and it is as you say. I made sure the worksheet started out manually protected with select unlocked cells and select locked cell, ran the code, exited excel and reopened the SaveAs copy and all cells could be selected, so it does not persist between sessions when set programatically, which is a pity. Thanks for helping me clear that up. Ken Johnson Hi Norman, I don't think I'm perfectly clear about this though. There is still a definite difference between my Pentium 3 and my son's AMD64. On his machine if the worksheet started out manually with EnableSelection = xlUnlockedCells it remains so in the SaveAs copy resulting from my macro. On my machine the SaveAs copy ends up with EnableSelection = xlNoRestrictions Also, that weird behaviour of the UserInterfaceOnly argument only occurred on my machine. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Userinterfaceonly | Excel Worksheet Functions | |||
UserInterfaceOnly | Excel Discussion (Misc queries) | |||
UserInterfaceOnly on the Mac | Excel Programming | |||
UserInterfaceOnly on the Mac | Excel Programming | |||
question about worksheet protection using userinterfaceonly:=true | Excel Programming |