Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 155
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 155
Default 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   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 155
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How to unlock the cell range limitation? manng118 Excel Discussion (Misc queries) 1 October 23rd 09 05:55 AM
Lock or Unlock Range of Cells on Worksheet_Change Event Grahame Coyle Excel Worksheet Functions 3 July 14th 08 12:27 AM
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. Daniel Excel Worksheet Functions 1 June 24th 05 02:59 PM
Using VBA to Unlock Cells Office User Excel Programming 7 May 11th 05 10:58 PM
Range won't Unlock Matt Excel Programming 1 April 19th 04 08:59 PM


All times are GMT +1. The time now is 10:13 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"