Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlUnlocked Cells Not Working
I have a file where sheet 1 is protected but has two unlocked cells. I have
set in VBA browser the EnableSelection to xlUnlockedCells. Returning to the spreadsheet, sheet 1 correctly only allows movement between the two unlocked cells. However, if I close the file after saving, when opened the xlUnlockedCells has been turned off. Any ideas? Thanks, Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlUnlocked Cells Not Working
Hi
This setting is not saved with the file You must add code to the workbook open event to set it every time you open the workbook If you copy this event in the Thisworkbook module http://www.mcgimpsey.com/excel/modules.html It will run automatic when you open the workbook and Protect each sheet in the workbook and set the EnableSelection Test it on a test workbook Private Sub Workbook_Open() Dim Sh As Worksheet Application.ScreenUpdating = False For Each Sh In ThisWorkbook.Worksheets Sh.Select Sh.Protect userinterfaceonly:=True Sh.EnableSelection = xlUnlockedCells Next Sheets(1).Select Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Robert" wrote in message ... I have a file where sheet 1 is protected but has two unlocked cells. I have set in VBA browser the EnableSelection to xlUnlockedCells. Returning to the spreadsheet, sheet 1 correctly only allows movement between the two unlocked cells. However, if I close the file after saving, when opened the xlUnlockedCells has been turned off. Any ideas? Thanks, Robert |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlUnlocked Cells Not Working
Thanks Ron for pointing me in the right direction. Will test out the sample
code. Regards, Robert "Ron de Bruin" wrote in message ... Hi This setting is not saved with the file You must add code to the workbook open event to set it every time you open the workbook If you copy this event in the Thisworkbook module http://www.mcgimpsey.com/excel/modules.html It will run automatic when you open the workbook and Protect each sheet in the workbook and set the EnableSelection Test it on a test workbook Private Sub Workbook_Open() Dim Sh As Worksheet Application.ScreenUpdating = False For Each Sh In ThisWorkbook.Worksheets Sh.Select Sh.Protect userinterfaceonly:=True Sh.EnableSelection = xlUnlockedCells Next Sheets(1).Select Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Robert" wrote in message ... I have a file where sheet 1 is protected but has two unlocked cells. I have set in VBA browser the EnableSelection to xlUnlockedCells. Returning to the spreadsheet, sheet 1 correctly only allows movement between the two unlocked cells. However, if I close the file after saving, when opened the xlUnlockedCells has been turned off. Any ideas? Thanks, Robert |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlUnlocked Cells Not Working
Hi Robert
Do you know that you have this option in Excel 2002-2003 when you protect a sheet? ToolsProtection....Protect sheet -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Robert" wrote in message ... Thanks Ron for pointing me in the right direction. Will test out the sample code. Regards, Robert "Ron de Bruin" wrote in message ... Hi This setting is not saved with the file You must add code to the workbook open event to set it every time you open the workbook If you copy this event in the Thisworkbook module http://www.mcgimpsey.com/excel/modules.html It will run automatic when you open the workbook and Protect each sheet in the workbook and set the EnableSelection Test it on a test workbook Private Sub Workbook_Open() Dim Sh As Worksheet Application.ScreenUpdating = False For Each Sh In ThisWorkbook.Worksheets Sh.Select Sh.Protect userinterfaceonly:=True Sh.EnableSelection = xlUnlockedCells Next Sheets(1).Select Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Robert" wrote in message ... I have a file where sheet 1 is protected but has two unlocked cells. I have set in VBA browser the EnableSelection to xlUnlockedCells. Returning to the spreadsheet, sheet 1 correctly only allows movement between the two unlocked cells. However, if I close the file after saving, when opened the xlUnlockedCells has been turned off. Any ideas? Thanks, Robert |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
xlUnlocked Cells Not Working
Ron,
I'm, using 2000 and the sheet protection as you suggest. I've played with the code and initially get the Object 91 error, however, I now refer to the only sheet that I want to set xlUnlockedCells by Sheets ("Main").EnableSelection = xlUnlockedCells. I guess the 91 error is to do with not being on the sheet that I need to make the changes to. Thanks, Robert "Ron de Bruin" wrote in message ... Hi Robert Do you know that you have this option in Excel 2002-2003 when you protect a sheet? ToolsProtection....Protect sheet -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Robert" wrote in message ... Thanks Ron for pointing me in the right direction. Will test out the sample code. Regards, Robert "Ron de Bruin" wrote in message ... Hi This setting is not saved with the file You must add code to the workbook open event to set it every time you open the workbook If you copy this event in the Thisworkbook module http://www.mcgimpsey.com/excel/modules.html It will run automatic when you open the workbook and Protect each sheet in the workbook and set the EnableSelection Test it on a test workbook Private Sub Workbook_Open() Dim Sh As Worksheet Application.ScreenUpdating = False For Each Sh In ThisWorkbook.Worksheets Sh.Select Sh.Protect userinterfaceonly:=True Sh.EnableSelection = xlUnlockedCells Next Sheets(1).Select Application.ScreenUpdating = True End Sub -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "Robert" wrote in message ... I have a file where sheet 1 is protected but has two unlocked cells. I have set in VBA browser the EnableSelection to xlUnlockedCells. Returning to the spreadsheet, sheet 1 correctly only allows movement between the two unlocked cells. However, if I close the file after saving, when opened the xlUnlockedCells has been turned off. Any ideas? Thanks, Robert |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Merging cells not working | New Users to Excel | |||
Working with pairs of cells | Excel Worksheet Functions | |||
SUMPRODUCT not working, some cells with #N/A | Excel Discussion (Misc queries) | |||
Some Cells Not Working | Excel Discussion (Misc queries) | |||
xlUnlocked cells? | Excel Discussion (Misc queries) |