Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
skip worksheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
skip worksheet
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. "davegb" wrote in message oups.com... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
skip worksheet
Thanks for the reply, Stuart. Unfortunately, the problem remains.
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
skip worksheet
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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
skip worksheet
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 davegb wrote: 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! -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
skip worksheet
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Worksheet to Worksheet skip blanks | Excel Worksheet Functions | |||
using IF to skip | Excel Worksheet Functions | |||
skip to worksheet | Excel Worksheet Functions | |||
skip printing worksheet | Excel Discussion (Misc queries) | |||
Is there a shortcut to skip down the page in a large worksheet? | Excel Discussion (Misc queries) |