Protect Worksheet Macro

[Lenny]

Here's what I need some help with....
Have created a form in Excel that consists of cells in the first 7 rows are
a combination of locked and unlocked cells for the users entries. There are
also 9 rows at the bottom that are also locked and unlocked. Everything in
between these two areas needs to be unprotected so the user can make text
entries or combine cells to insert an object (graphic, chart or other). I
have created a toolbar with buttons that allow the user to unlock the
worksheet to have access to tools and processes they normally would not with
the sheet locked as well as another button that allows them to relock the
worksheet (yeah, like they are really gonna do that).

Is there a way to add code to the lock / unlock buttons that will relock the
two ranges at the top and bottom if the user clicks into either of these two
areas? Assistance as always, is greatly appreciated.

Regards - Lenny

 

[JLatham]

The following is worksheet event code: open your workbook, select the sheet
you need this to work with. Right-click the sheet's name tab and choose
[View Code]. Copy the code below and paste it into the code module presented
to you. Edit the row numbers as required. Close the VB Editor and give it a
try.

Private Sub Worksheet_SelectionChange(ByVal _
Target As Range)
'lock the sheet if a cell in
'rows 1-7 or 20-28 is selected
If Target.Row < 8 Or (Target.Row > 19 And _
Target.Row < 29) Then
ActiveSheet.Protect
MsgBox "Protecting sheet"
'or if you have a password
'ActiveSheet.Protect Password:="mypassword"
End If
End Sub

NOTE: you could modify this to seem really 'smart' by having the sheet
automatically unprotect itself when they click in one of the areas they are
allowed to work in and do away with your buttons!

Private Sub Worksheet_SelectionChange(ByVal _
Target As Range)
'lock the sheet if a cell in
'rows 1-7 or 20-28 is selected
If Target.Row < 8 Or (Target.Row > 19 And _
Target.Row < 29) Then
'clicked in locked areas, protect the sheet
ActiveSheet.Protect
MsgBox "Protecting sheet"
'or if you have a password
'ActiveSheet.Protect Password:="mypassword"
Else
'clicked in area permitted to change
'unprotect the sheet
ActiveSheet.Unprotect
'or if you have a password
'ActiveSheet.Unprotect Password:="mypassword"
End If
End Sub

 

[JLatham]

I left a MsgBox command in the code. You can safely remove that line of code
to make the process "quieter". I used it just to assure myself that things
were happeing properly.