Is there a way to make a sheet viewable but not selectable?

[Guest]

I have a work book with multiple sheets. I want the user to be able to
select sheet 1 to access macro buttons. I want them to be able to view the
results of the macro data entries on sheet 2, but I do not want them to be
able to directly modify or even select sheet 2, only view the entered data.

I can't simply protect the worksheet, because new data is being added and
old data changed.

I tried changing the worksheet properties in VBA, EnableSelection to "No
Selection", but that did not seem to have an effect.

Help? Is this possible?

-Bill

 

[Doug Glancy]

Hi Bill,

"I can't simply protect the worksheet, because new data is being added and
old data changed."

If I understand your problem correctly, you actually can protect the sheet,
with the UserInterfaceOnly option, if you are using XL 2000 or later. This
allows you to change the sheet through code, while keeping the sheet
protected. You need to set this property each time the workbook is opened -
it doesn't persist after the workbook is closed.

hth,

Doug

 

[Guest]

Doug,
Thanks for the response . Yes, I am using Excel 2003.
Now, for a newbie at this, comes the next question ...
.... Where do I set the UserInterfaceOnly option, and how can I
automatically set it each time the workbook is opened.

TIA

Bill

 

[Doug Glancy]

Bill,

You need to create a WorkbookOpen event sub in the ThisWorkbook module.

In the VBE (Visual Basic Editor) put this code into the ThisWorkbook module.
This will protect Sheet1:

Private Sub Workbook_Open()
Worksheets("Sheet1").Protect userinterfaceonly:=True
End Sub

hth,

Doug

 

[Guest]

Doug,

Perfect! Just what I ws looking for.
Thank You.

-Bill

 

[Guest]

Hi Bill,

..EnableSelection is a worksheet property that uses built-in constants for
the settings. It's used separately from .Protect -as follows:

ActiveSheet.EnableSelection = xlNoSelection ' (or xlUnlockedCells if
some cells require user access) Note the required "xl" prefix. This should
make it work for you.

Regards,
GS