Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlock range of cells
Could someone help me please, I have the following 2 macros, the first
unlocks the worksheet and the second is written to unlock a range of cells. I am not getting any errors, the problem is the second macro simply does not unlock the range of cells I need to. I wondered if the problem was with the individual cell protection? Should they be set to locked or unlocked? Sub UnprotectWorksheet() ' ' Macro1 Macro ' Macro recorded 4/03/2007 by Tanya' ' ' ActiveSheet.Unprotect End Sub Sub UnlockCells() ' 'Unlockcell Macro 'Created by Tanya ' Let index2 = 1 'Goto Home Cell "A1" Range("A1").Select 'Find beginning of mechanics Names Do While ActiveCell < "Serial Number" Let index2 = index2 + 1 ActiveSheet.Range(Cells(index2, 1), Cells(index2, 1)).Select Loop ' 'Find next blank line Let index3 = index2 + 1 Do While ActiveCell < "" Let index3 = index3 + 1 ActiveSheet.Range(Cells(index3, 1), Cells(index3, 1)).Select Loop 'Select Range for unprotect ActiveSheet.Range(Cells(index2 + 1, 1), Cells(index3, 8)).Select Selection.Locked = False ActiveSheet.Protect 'Return home Range("A1").Select End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlock range of cells
make sure all cells are protected before running your code.
Then find out which cells get unprotected after you run the code. Look at the logic of your code and figure out why these cells get unprotected. Or, more directly, step through the code and see what the values of the variables are as it progresses. -- Regards, Tom Ogilvy "Tanya" wrote in message ... Could someone help me please, I have the following 2 macros, the first unlocks the worksheet and the second is written to unlock a range of cells. I am not getting any errors, the problem is the second macro simply does not unlock the range of cells I need to. I wondered if the problem was with the individual cell protection? Should they be set to locked or unlocked? Sub UnprotectWorksheet() ' ' Macro1 Macro ' Macro recorded 4/03/2007 by Tanya' ' ' ActiveSheet.Unprotect End Sub Sub UnlockCells() ' 'Unlockcell Macro 'Created by Tanya ' Let index2 = 1 'Goto Home Cell "A1" Range("A1").Select 'Find beginning of mechanics Names Do While ActiveCell < "Serial Number" Let index2 = index2 + 1 ActiveSheet.Range(Cells(index2, 1), Cells(index2, 1)).Select Loop ' 'Find next blank line Let index3 = index2 + 1 Do While ActiveCell < "" Let index3 = index3 + 1 ActiveSheet.Range(Cells(index3, 1), Cells(index3, 1)).Select Loop 'Select Range for unprotect ActiveSheet.Range(Cells(index2 + 1, 1), Cells(index3, 8)).Select Selection.Locked = False ActiveSheet.Protect 'Return home Range("A1").Select End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlock range of cells
Tom I have run through this a dozen times, checking first that the sheet is
protected. Working through the code, the correct range is selected, therefore I cannot understand why "Selection.Locked=False" does not unlock the desired cells, and the whole worksheet is protected. "Tom Ogilvy" wrote: make sure all cells are protected before running your code. Then find out which cells get unprotected after you run the code. Look at the logic of your code and figure out why these cells get unprotected. Or, more directly, step through the code and see what the values of the variables are as it progresses. -- Regards, Tom Ogilvy "Tanya" wrote in message ... Could someone help me please, I have the following 2 macros, the first unlocks the worksheet and the second is written to unlock a range of cells. I am not getting any errors, the problem is the second macro simply does not unlock the range of cells I need to. I wondered if the problem was with the individual cell protection? Should they be set to locked or unlocked? Sub UnprotectWorksheet() ' ' Macro1 Macro ' Macro recorded 4/03/2007 by Tanya' ' ' ActiveSheet.Unprotect End Sub Sub UnlockCells() ' 'Unlockcell Macro 'Created by Tanya ' Let index2 = 1 'Goto Home Cell "A1" Range("A1").Select 'Find beginning of mechanics Names Do While ActiveCell < "Serial Number" Let index2 = index2 + 1 ActiveSheet.Range(Cells(index2, 1), Cells(index2, 1)).Select Loop ' 'Find next blank line Let index3 = index2 + 1 Do While ActiveCell < "" Let index3 = index3 + 1 ActiveSheet.Range(Cells(index3, 1), Cells(index3, 1)).Select Loop 'Select Range for unprotect ActiveSheet.Range(Cells(index2 + 1, 1), Cells(index3, 8)).Select Selection.Locked = False ActiveSheet.Protect 'Return home Range("A1").Select End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
unlock range of cells
Thank you Tom
After a long repetitive streak, I managed to find out where I was going wrong. All is working fine now. Thanks again "Tom Ogilvy" wrote: make sure all cells are protected before running your code. Then find out which cells get unprotected after you run the code. Look at the logic of your code and figure out why these cells get unprotected. Or, more directly, step through the code and see what the values of the variables are as it progresses. -- Regards, Tom Ogilvy "Tanya" wrote in message ... Could someone help me please, I have the following 2 macros, the first unlocks the worksheet and the second is written to unlock a range of cells. I am not getting any errors, the problem is the second macro simply does not unlock the range of cells I need to. I wondered if the problem was with the individual cell protection? Should they be set to locked or unlocked? Sub UnprotectWorksheet() ' ' Macro1 Macro ' Macro recorded 4/03/2007 by Tanya' ' ' ActiveSheet.Unprotect End Sub Sub UnlockCells() ' 'Unlockcell Macro 'Created by Tanya ' Let index2 = 1 'Goto Home Cell "A1" Range("A1").Select 'Find beginning of mechanics Names Do While ActiveCell < "Serial Number" Let index2 = index2 + 1 ActiveSheet.Range(Cells(index2, 1), Cells(index2, 1)).Select Loop ' 'Find next blank line Let index3 = index2 + 1 Do While ActiveCell < "" Let index3 = index3 + 1 ActiveSheet.Range(Cells(index3, 1), Cells(index3, 1)).Select Loop 'Select Range for unprotect ActiveSheet.Range(Cells(index2 + 1, 1), Cells(index3, 8)).Select Selection.Locked = False ActiveSheet.Protect 'Return home Range("A1").Select End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to unlock the cell range limitation? | Excel Discussion (Misc queries) | |||
Lock or Unlock Range of Cells on Worksheet_Change Event | Excel Worksheet Functions | |||
how do i password protect an .xls file? how do i unlock it for automation. e.g. want to unlock and access a .xls from another .xls macro. | Excel Worksheet Functions | |||
Using VBA to Unlock Cells | Excel Programming | |||
Range won't Unlock | Excel Programming |