skip worksheet

D

davegb

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.
 
S

Stuart

Try
If Not wkSht.Name = "County Records" Then

Nb: you can always protect "Country Records" and just unprotect when you
need to write to it.

Regards.
 
D

Dave Peterson

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
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.
Click to expand...
 
D

davegb

Dave asked:
But I'm wondering why you rename the worksheet. Doesn't seem like you
should
have to do this.

The macro renames the worksheets so I can tell they're in unprotected
form.

Both your solutions worked great. Thanks Dave!
 
D

Dave Peterson

Maybe you could keep track and then just reprotect the protected worksheets:

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
Dim WksWasProtected As Boolean

PWORD = "pwd"

For Each wkSht In ActiveWorkbook.Worksheets
If LCase(wkSht.Name) = LCase("County Records") Then
'do nothing
Else
With wkSht
If .ProtectContents Then
WksWasProtected = True
.Unprotect Password:=PWORD
.Columns.Hidden = False
Else
WksWasProtected = False
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
If WksWasProtected Then
.Protect Password:=PWORD
End If
End If
End If
End With
End If
Next wkSht

End Sub
 
P

Peter_A_M (NL)

Herewith I just react to the error-message you received.
Your code isn't grammatically correct, as I indicated below.

For Each wkSht In ActiveWorkbook.Worksheets
If wkSht.Name = "County Records" Then GoTo restOfCodeSkipped
'Next wkSht 'leave this line out!

(... rest of code ...)

restOfCodeSkipped: 'destination for GoTo added
Next wkSht


So I made 2 modifications:
1. I added GoTo restOfCodeSkipped and destination in order to skip code in
between;
2. The condition in your If-statement wasn't correct;
' wkSht.Name = "County Records" ' evaluates either to True or to False;
so don't add an ' extra = True '

Many greetings,
Peter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

End if w/o if? 22
Shared Workbooks & Code 1
variable not set error? 4
Protect/unprotect worksheets 2
Qualifying permissions in protect unprotect macro. 4
Auto footers on multiple pages 5
Modifying a protect unprotect macro 10
Protecting worksheet. 1

Top