Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limiting a worksheet's selection range.
xl2003, Windows XP:
Sub SaveSelectUnlocked() Dim SaveAsFilename ActiveSheet.Unprotect With Range("A1,B2,C3") .Locked = False .FormulaHidden = False End With With ActiveSheet .EnableSelection = xlUnlockedCells .Protect End With ActiveWorkbook.Protect SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="") If SaveAsFilename < False Then ActiveWorkbook.SaveAs (SaveAsFilename) End Sub I was hoping the above code would result in a SavedAs copy of the workbook with the only selectable cells being A1, B2 and C3. As soon as the code has finished running that is the case. However, after closing and reopening the workbook, all cells are selectable. Even manually saving the workbook resulting from the code, closing and reopening results in all cells being selectable. Is there a way of programatically limiting a worksheet's selection range to unlocked cells so that it is retained between sessions? Ken Johnson |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limiting a worksheet's selection range.
Change your
SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="", _ FileFilter:="Microsoft Office Excel Workbook (*.xls),*xls") "Ken Johnson" wrote: xl2003, Windows XP: Sub SaveSelectUnlocked() Dim SaveAsFilename ActiveSheet.Unprotect With Range("A1,B2,C3") .Locked = False .FormulaHidden = False End With With ActiveSheet .EnableSelection = xlUnlockedCells .Protect End With ActiveWorkbook.Protect SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="") If SaveAsFilename < False Then ActiveWorkbook.SaveAs (SaveAsFilename) End Sub I was hoping the above code would result in a SavedAs copy of the workbook with the only selectable cells being A1, B2 and C3. As soon as the code has finished running that is the case. However, after closing and reopening the workbook, all cells are selectable. Even manually saving the workbook resulting from the code, closing and reopening results in all cells being selectable. Is there a way of programatically limiting a worksheet's selection range to unlocked cells so that it is retained between sessions? Ken Johnson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limiting a worksheet's selection range.
On Apr 19, 12:20 pm, Barb Reinhardt
wrote: Change your SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="", _ FileFilter:="Microsoft Office Excel Workbook (*.xls),*xls") Hi Barb, Thanks for the FileFilter improvement. I've added it, however, when I run the code, the result is the same. I have run the code to save the workbook with a new file name and have saved it to my desktop. As before, after the code has finished only A1, B2, and C3 are selectable. I've checked the active sheet's EnableSelection property and it is xlUnLockedCells, as expected. I then exit Excel then reopen the same file from my desktop. Now, all cells are selectable because the worksheet's EnableSelection property has changed to xlNoRestrictions. Here's my new code, maybe I haven't properly incorporated your suggestion... 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 End With ActiveWorkbook.Protect SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="", _ filefilter:="Microsoft Office Excel Workbook (*.xls),*xls") If SaveAsFilename < False Then ActiveWorkbook.SaveAs (SaveAsFilename) End Sub Thanks for your help. Ken Johnson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limiting a worksheet's selection range.
I used exactly the code I posted in a workbook here and it worked fine. Do
you have it in a separate module? "Ken Johnson" wrote: On Apr 19, 12:20 pm, Barb Reinhardt wrote: Change your SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="", _ FileFilter:="Microsoft Office Excel Workbook (*.xls),*xls") Hi Barb, Thanks for the FileFilter improvement. I've added it, however, when I run the code, the result is the same. I have run the code to save the workbook with a new file name and have saved it to my desktop. As before, after the code has finished only A1, B2, and C3 are selectable. I've checked the active sheet's EnableSelection property and it is xlUnLockedCells, as expected. I then exit Excel then reopen the same file from my desktop. Now, all cells are selectable because the worksheet's EnableSelection property has changed to xlNoRestrictions. Here's my new code, maybe I haven't properly incorporated your suggestion... 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 End With ActiveWorkbook.Protect SaveAsFilename = Application.GetSaveAsFilename(InitialFileName:="", _ filefilter:="Microsoft Office Excel Workbook (*.xls),*xls") If SaveAsFilename < False Then ActiveWorkbook.SaveAs (SaveAsFilename) End Sub Thanks for your help. Ken Johnson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limiting a worksheet's selection range.
On Apr 19, 10:38 pm, Barb Reinhardt
wrote: I used exactly the code I posted in a workbook here and it worked fine. Do you have it in a separate module? Hi Barb, the code is in a standard module in that workbook. I've tried it again by just pasting the code I last posted into a standard module of a new workbook (Book 1). I ran the code from sheet 1 saving it to the desktop as testing.xls. The cells A1, B2 and C3 are the only selectable cells, as expected. I then exited Excel, then re-opened testing.xls from the destop. Locked and unlocked cells on sheet 1 can all be selected, not as expected. Do you get the same result? Ken Johnson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limiting a worksheet's selection range.
It seems that it's related to what you can choose to display (Locked cells,
unlocked cells) when you protect the worksheet. I generally allow a display of unlocked cells only and that may be why we are seeing a disconnect. try replacing the PROTECT (for the worksheet) with this. I'm not sure it will work .Protect UserInterfaceOnly:=True "Ken Johnson" wrote: On Apr 19, 10:38 pm, Barb Reinhardt wrote: I used exactly the code I posted in a workbook here and it worked fine. Do you have it in a separate module? Hi Barb, the code is in a standard module in that workbook. I've tried it again by just pasting the code I last posted into a standard module of a new workbook (Book 1). I ran the code from sheet 1 saving it to the desktop as testing.xls. The cells A1, B2 and C3 are the only selectable cells, as expected. I then exited Excel, then re-opened testing.xls from the destop. Locked and unlocked cells on sheet 1 can all be selected, not as expected. Do you get the same result? Ken Johnson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Limiting a worksheet's selection range.
On Apr 20, 1:46 am, Barb Reinhardt
wrote: It seems that it's related to what you can choose to display (Locked cells, unlocked cells) when you protect the worksheet. I generally allow a display of unlocked cells only and that may be why we are seeing a disconnect. try replacing the PROTECT (for the worksheet) with this. I'm not sure it will work .Protect UserInterfaceOnly:=True Thanks for solving that problem for me Barb. I must do some reading on that UserInterfaceOnly thing. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table limiting user selection | Excel Discussion (Misc queries) | |||
Limiting selection in a cell AND linking that selection to a list | Excel Discussion (Misc queries) | |||
Limiting the range of AutoFilter? | Excel Worksheet Functions | |||
Limiting the range of a lookup function | Excel Worksheet Functions | |||
Excel VBA - Range(Selection, Selection.End(xlDown)).Name issue. | Excel Programming |