UserInterfaceOnly question.

  • Thread starter Thread starter Ken Johnson
  • Start date Start date
K

Ken Johnson

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 Ken,

In what way does your code not work?
Click to expand...

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,

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
Click to expand...

Oops!

In the 2nd paragraph after the code I meant to type "only the unlocked
cells can be selected"

Ken Johnson
 
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.
 
Hi Ken,

I should have added that, similarly, the EnableSelection property
setting will not persist once the workbook is closed
 
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.
Click to expand...


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 Ken.

In xl2002+ the EnableSelection property can be set manually,
as a protection option, and will persist.
 
Hi Ken.

In xl2002+ the EnableSelection property can be set manually,
as a protection option, and will persist.
Click to expand...


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 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
Click to expand...

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
 
Back
Top