Hide/Unhide in Code; Protect, Unlock, etc.

[Sandy]

Hello -

I have code that works, however, I am sure this is not the correct way to do
it. Can anyone show me how this is normally done more efficiently and with
less lines of code?

Locking the sheet and then unlocking certain cells in a range is really
clunky! Additionally, I do use this same range elsewhere in code on another
sheet. How do I write it once and refer to it from different sheets?

Any help will really be appreciated!

Sub UnhideRowsAndUnlockCells()
Sheet2.Unprotect ("pwdGoldieLocks")
Sheet2.Rows("1:250").Locked = False
If Sheet2.Rows("30:36").Hidden = True Then
Sheet2.Rows("30:36").Hidden = False
Else
Sheet2.Rows("30:36").Hidden = False
End If
<snip>

Sheet2.Range("D2").Value = "Food"

If Sheet2.cmdFood.Visible = False Then
Sheet2.cmdFood.Visible = True
Else
Sheet2.cmdFood.Visible = True
End If
If Sheet2.cmdFood.Enabled = False Then
Sheet2.cmdFood.Enabled = True
Else
Sheet2.cmdFood.Enabled = True
End If
<snip>
Sheet2.Rows("1:250").Locked = True
Sheet2.Range("D816, D2327, D2931, D3342," & _
"D4450, D5257, D5978, D8083, D8586, D8894").Locked = False
Sheet2.Range("D102105, D107111, D113115, D123128, D135136," & _
"D143144, D146, D154156").Locked = False
Sheet2.Range("D158159, D167170, D172174," & _
"D176, D178179, D187189, D191197, D212").Locked = False
Sheet2.Range("D214218, D221230, D237240").Locked = False

Sheet2.Protect ("pwdGoldieLocks")

'Sheet21
Sheet21.Unprotect ("pwdGoldieLocks")
Sheet21.Rows("1:250").Locked = False
If Sheet21.Rows("30:34").Hidden = True Then
Sheet21.Rows("30:34").Hidden = False
Else
Sheet21.Rows("30:34").Hidden = False
End If
<snip>
If Sheet21.cmdCheckFacilities.Visible = True Then
Sheet21.cmdCheckFacilities.Visible = False
Else
Sheet21.cmdCheckFacilities.Visible = False
End If

Sheet21.Rows("1:250").Locked = True
Sheet21.Protect ("pwdGoldieLocks")
'Sheet17
Sheet17.Unprotect ("pwdGoldieLocks")
Sheet17.Rows("1:250").Locked = False
If Sheet17.Rows("18:21").Hidden = True Then
Sheet17.Rows("18:21").Hidden = False
Else
Sheet17.Rows("18:21").Hidden = False
End If
If Sheet17.Rows("23:57").Hidden = True Then
Sheet17.Rows("23:57").Hidden = False
Else
Sheet17.Rows("23:57").Hidden = False
End If
<snip>
Sheet17.Protect ("pwdGoldieLocks")
'Unhide sheets hidden for Facilities
Sheet5.Visible = True
Sheet6.Visible = True
Sheet8.Visible = True
Sheet9.Visible = True
Sheet22.Visible = True
Sheet5.Visible = xlSheetVisible
Sheet6.Visible = xlSheetVisible
Sheet8.Visible = xlSheetVisible
Sheet9.Visible = xlSheetVisible
Sheet22.Visible = xlSheetVisible
End If

 

[Otto Moehrbach]

Sandy
You have a range consisting of scattered cells here and there, and you
say that you use the same cells (in your code) on other sheets.
Here is what I would do.
Let's call each of the long strings of cell addresses you have in your code
a group of cells.
Before I write the code I would go to one of the sheets.
I would select all the cells of one of the groups of cells.
You do this by selecting the first cell (any cell of the group) then hold
the Ctrl key down and select each of the other cells.
Now name that group of cells some name that is meaningful to you.
Click on some other cell to deselect all the cells you have selected.
Now select all the cells in another group and name that range.
Repeat this for each group that you have.
If each group is peculiar to one sheet, then select the group of cells in
that sheet before naming the range, but if you want to use that range in
more than one sheet, do the selecting and naming all on one sheet.
Let's say that you named the ranges Rng1 and Rng2, etc.
To refer to these ranges in your code, you can write the code one way if the
range was defined in the sheet in which you want to use it, and write the
code another way if the range was defined on another sheet. But why not use
the same way of writing the code regardless of what sheet the range was
defined?
You can use something like:
Sheet2.Range(Range("Rng1").Address)
This gives you Rng1 in Sheet2 even if Rng1 was defined in some other sheet.

You can also make your code less clunky by using the With/End With
construct.
Look at your code and you see that you use "Sheet2..........." many times.
Do this:
With Sheet2
Here you place each line of code that has Sheet2 in it.
But delete the "Sheet2"
But leave the period that is currently after Sheet2.
Like:
.Unprotect ("pwdGoldieLocks")
.Rows("1:250").Locked = False
If .Rows("30:36").Hidden = True Then
.Rows("30:36").Hidden = False
Etc
End With
You can do this whenever you have multiple lines of code pertaining to one
sheet.
Please post back if you need more. HTH Otto

 

[Sandy]

Hi Otto!

Thanks so much for your response!

 

[Otto Moehrbach]

I made a mistake in what I told you. The statement:
With Sheet2
should be:
With Sheets("Sheet2")
This is assuming that Sheet2 is the sheet tab text. HTH Otto