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