skip worksheet
Maybe something like:
Option Explicit
Sub AllSheetsToggleProtectWIndColHide()
'for all sheets in currently active workbook, assigned to button
Dim TopCell As Range
Dim TopCol As Range
Dim Cols2Hide As Range
Dim wkSht As Worksheet
Dim PWORD As String
PWORD = "pwd"
For Each wkSht In ActiveWorkbook.Worksheets
If LCase(wkSht.Name) = LCase("County Records") Then
'do nothing
Else
With wkSht
If .ProtectContents Then
.Unprotect Password:=PWORD
.Name = .Name & "##"
.Columns.Hidden = False
Else
Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues)
If TopCell Is Nothing Then ' if it's not found "top"
'what happens here
Else
Set TopCol = .Columns(TopCell.Column)
Set Cols2Hide = .Range(TopCol, .Columns("AC"))
Cols2Hide.Hidden = True
.Protect Password:=PWORD
If .Name Like "*[##]" Then
.Name = Left(.Name, Len(.Name) - 2)
End If
End If
End If
End With
End If
Next wkSht
End Sub
But I'm wondering why you rename the worksheet. Doesn't seem like you should
have to do this.
This may be better:
Option Explicit
Sub AllSheetsToggleProtectWIndColHide()
'for all sheets in currently active workbook, assigned to button
Dim TopCell As Range
Dim TopCol As Range
Dim Cols2Hide As Range
Dim wkSht As Worksheet
Dim PWORD As String
PWORD = "pwd"
For Each wkSht In ActiveWorkbook.Worksheets
If LCase(wkSht.Name) = LCase("County Records") Then
'do nothing
Else
With wkSht
If .ProtectContents Then
.Unprotect Password:=PWORD
.Name = .Name & "##"
.Columns.Hidden = False
Else
Set TopCell = .Rows(3).Find(What:="top", LookIn:=xlValues)
If TopCell Is Nothing Then ' if it's not found "top"
'what happens here
Else
Set TopCol = .Columns(TopCell.Column)
Set Cols2Hide = .Range(TopCol, .Columns("AC"))
Cols2Hide.Hidden = True
End if
.Protect Password:=PWORD
If .Name Like "*[##]" Then
.Name = Left(.Name, Len(.Name) - 2)
End If
End If
End With
End If
Next wkSht
End Sub
davegb wrote:
I got a macro here to toggle protect/unprotect the worksheets in a
workbook. I've added a different type of worksheet to the workbook to
paste extracted data to, but the protect/unprotect macro doesn't work
on it because of formatting issues and I don't want it protected anyway
(so the macro can extract data to it). I tried to add a line to except
the sheet "County Records" from the protect/unprotect macro. So far,
I've got:
Sub AllSheetsToggleProtectWIndColHide()
'for all sheets in currently active workbook, assigned to button
Dim TopCell As Range
Dim TopCol As Range
Dim Cols2Hide As Range
Dim wkSht As Worksheet
For Each wkSht In ActiveWorkbook.Worksheets
If wkSht.Name = "County Records" = True Then<---added this line
Next wkSht<-------"next without for" error message
With wkSht
If .ProtectContents Then
.Unprotect Password:=PWORD
.Name = .Name & "##"
.Columns.Hidden = False
Else
Set TopCell = .Rows(3).Find(What:="top",
LookIn:=xlValues)
If Not TopCell Is Nothing Then ' if it found "top"
End If
Set TopCol = .Columns(TopCell.Column)
Set Cols2Hide = .Range(TopCol, .Columns("AC"))
Cols2Hide.Hidden = True
.Protect Password:=PWORD
If .Name Like "*[##]" Then _
.Name = Left(.Name, Len(.Name) - 2)
End If
End With
Next wkSht
End Sub
I added the line:
If wkSht.Name = "County Records" = True Then
to skip the County Records sheet, but now I get a "Next without for"
error message on the line below it (as noted in the code above). I know
that adding the new lines of code is causing the problem, just can't
figure out where to put another "for" statement to make it all work.
Does anyone see it?
Thanks in advance.
--
Dave Peterson
|