| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is there a way to make a sheet viewable but not selectable?
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a way to make a sheet viewable but not selectable?
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 "Bill" wrote in message ... 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a way to make a sheet viewable but not selectable?
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" wrote: 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 "Bill" wrote in message ... 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a way to make a sheet viewable but not selectable?
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 "Bill" wrote in message ... 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" wrote: 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 "Bill" wrote in message ... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a way to make a sheet viewable but not selectable?
Doug,
Perfect! Just what I ws looking for. Thank You. -Bill "Doug Glancy" wrote: 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 "Bill" wrote in message ... 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" wrote: 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 "Bill" wrote in message ... 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Is there a way to make a sheet viewable but not selectable?
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Can I make a worksheet protected, locked AND not viewable? | Excel Discussion (Misc queries) | |||
| Can I make rows of an excell spreadsheet 'selectable' (on/off)? | Excel Worksheet Functions | |||
| How do I make a line non-selectable? | Excel Discussion (Misc queries) | |||
| How to make Add-In worksheets viewable. | Excel Programming | |||
| Write data to a cell, then make it undeletable/selectable | Excel Programming | |||
Hybrid Mode
