End if w/o if?

[Toppers]

Hi,

If .Name Like "*[##]" Then _
.Name = Left(.Name, Len(.Name) - 2)
End If ...... Missing here

HTh

"davegb" wrote:

> I've combined 2 macros that I got help writing here.
> Sub AllSheetsToggleProtectWInd()
> '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
> 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"
> 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<-----[end with without with error]
>
> Next wkSht
>
> End Sub
>
>
> But when I run it, I get an error of "End with without if", even though
> there's very clearly a "with wksht" statement at the top. If I remark
> out the End with, I get a "Next without For" error, even though there's
> a "For" statement. So any ideas on why VBA can't see my with or For
> each statements?
> Thanks in advance.
>
>

[Don Guillett]

maybe

..Protect Password:=PWORD
END IF
If .Name Like "*[##]" Then _


--
Don Guillett
SalesAid Software
donaldb@281.com
"davegb" <davegb@safebrowse.com> wrote in message
news:1114524904.137484.83730@f14g2000cwb.googlegroups.com...
> I've combined 2 macros that I got help writing here.
> Sub AllSheetsToggleProtectWInd()
> '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
> 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"
> 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<-----[end with without with error]
>
> Next wkSht
>
> End Sub
>
>
> But when I run it, I get an error of "End with without if", even though
> there's very clearly a "with wksht" statement at the top. If I remark
> out the End with, I get a "Next without For" error, even though there's
> a "For" statement. So any ideas on why VBA can't see my with or For
> each statements?
> Thanks in advance.
>

[JulieD]

Hi Dave


check my comments in line #########


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"davegb" <davegb@safebrowse.com> wrote in message
news:1114524904.137484.83730@f14g2000cwb.googlegroups.com...
> I've combined 2 macros that I got help writing here.
> Sub AllSheetsToggleProtectWInd()
> '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
> 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"
########where is this IF's 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<-----[end with without with error]
>
> Next wkSht
>
> End Sub
>
>
> But when I run it, I get an error of "End with without if", even though
> there's very clearly a "with wksht" statement at the top. If I remark
> out the End with, I get a "Next without For" error, even though there's
> a "For" statement. So any ideas on why VBA can't see my with or For
> each statements?
> Thanks in advance.
>

[Bob Phillips]

No, he has a line continuation, so no End If needed.

--
HTH

Bob Phillips

"Toppers" <Toppers@discussions.microsoft.com> wrote in message
news:26927AED-559E-4246-84E6-1D32263FAB3B@microsoft.com...
> Hi,
>
> If .Name Like "*[##]" Then _
> .Name = Left(.Name, Len(.Name) - 2)
> End If ...... Missing here
>
> HTh
>
> "davegb" wrote:
>
> > I've combined 2 macros that I got help writing here.
> > Sub AllSheetsToggleProtectWInd()
> > '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
> > 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"
> > 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<-----[end with without with error]
> >
> > Next wkSht
> >
> > End Sub
> >
> >
> > But when I run it, I get an error of "End with without if", even though
> > there's very clearly a "with wksht" statement at the top. If I remark
> > out the End with, I get a "Next without For" error, even though there's
> > a "For" statement. So any ideas on why VBA can't see my with or For
> > each statements?
> > Thanks in advance.
> >
> >

[Toppers]

But it could be End If to If Not TopCell Is Nothing which admittedly could
come before If .name as per Don's reply. Both are valid I think.

"Bob Phillips" wrote:

> No, he has a line continuation, so no End If needed.
>
> --
> HTH
>
> Bob Phillips
>
> "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> news:26927AED-559E-4246-84E6-1D32263FAB3B@microsoft.com...
> > Hi,
> >
> > If .Name Like "*[##]" Then _
> > .Name = Left(.Name, Len(.Name) - 2)
> > End If ...... Missing here
> >
> > HTh
> >
> > "davegb" wrote:
> >
> > > I've combined 2 macros that I got help writing here.
> > > Sub AllSheetsToggleProtectWInd()
> > > '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
> > > 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"
> > > 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<-----[end with without with error]
> > >
> > > Next wkSht
> > >
> > > End Sub
> > >
> > >
> > > But when I run it, I get an error of "End with without if", even though
> > > there's very clearly a "with wksht" statement at the top. If I remark
> > > out the End with, I get a "Next without For" error, even though there's
> > > a "For" statement. So any ideas on why VBA can't see my with or For
> > > each statements?
> > > Thanks in advance.
> > >
> > >
>
>
>

[Bob Phillips]

I see your point. As I say, you should indent Ifs, and don't use single line
Ifs, it all gets too confusing.

Bob

"Toppers" <Toppers@discussions.microsoft.com> wrote in message
news:576C31D0-B553-4F68-BC1A-B50EA74828F7@microsoft.com...
> But it could be End If to If Not TopCell Is Nothing which admittedly
could
> come before If .name as per Don's reply. Both are valid I think.
>
> "Bob Phillips" wrote:
>
> > No, he has a line continuation, so no End If needed.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > news:26927AED-559E-4246-84E6-1D32263FAB3B@microsoft.com...
> > > Hi,
> > >
> > > If .Name Like "*[##]" Then _
> > > .Name = Left(.Name, Len(.Name) - 2)
> > > End If ...... Missing here
> > >
> > > HTh
> > >
> > > "davegb" wrote:
> > >
> > > > I've combined 2 macros that I got help writing here.
> > > > Sub AllSheetsToggleProtectWInd()
> > > > '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
> > > > 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"
> > > > 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<-----[end with without with error]
> > > >
> > > > Next wkSht
> > > >
> > > > End Sub
> > > >
> > > >
> > > > But when I run it, I get an error of "End with without if", even
though
> > > > there's very clearly a "with wksht" statement at the top. If I
remark
> > > > out the End with, I get a "Next without For" error, even though
there's
> > > > a "For" statement. So any ideas on why VBA can't see my with or For
> > > > each statements?
> > > > Thanks in advance.
> > > >
> > > >
> >
> >
> >

[davegb]

I've combined 2 macros that I got help writing here.
Sub AllSheetsToggleProtectWInd()
'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
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"
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<-----[end with without with error]

Next wkSht

End Sub


But when I run it, I get an error of "End with without if", even though
there's very clearly a "with wksht" statement at the top. If I remark
out the End with, I get a "Next without For" error, even though there's
a "For" statement. So any ideas on why VBA can't see my with or For
each statements?
Thanks in advance.

[JulieD]

Hi Dave

but as far as i can tell you do need this extra END IF and if you put it in
does your code then run correctly ... error messages aren't always 100%
accurate

--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"davegb" <davegb@safebrowse.com> wrote in message
news:1114529063.255121.75850@o13g2000cwo.googlegroups.com...
> Boy, is my face red! Somehow, as I wrote the message, I put down that
> there was an "End If w/o an If" error. It's an "End with w/o with"
> error, which is what I indicated in the code I posted. Does that make
> more sense? Sorry for the confusion, working on too many things at
> once.
>

[JulieD]

you're welcome

--
Cheers
JulieD

"davegb" <davegb@safebrowse.com> wrote in message
news:1114530168.323623.153710@o13g2000cwo.googlegroups.com...
> It worked! Thanks a lot, Julie!
>

[davegb]

It worked! Thanks a lot, Julie!

[Charlie]

True, the compiler/interpreter can't see the "missing" With statement. The
End With appears to be inside the unclosed If statement.

"JulieD" wrote:

> Hi Dave
>
> but as far as i can tell you do need this extra END IF and if you put it in
> does your code then run correctly ... error messages aren't always 100%
> accurate
>
> --
> Cheers
> JulieD
> check out www.hcts.net.au/tipsandtricks.htm
> ....well i'm working on it anyway
> "davegb" <davegb@safebrowse.com> wrote in message
> news:1114529063.255121.75850@o13g2000cwo.googlegroups.com...
> > Boy, is my face red! Somehow, as I wrote the message, I put down that
> > there was an "End If w/o an If" error. It's an "End with w/o with"
> > error, which is what I indicated in the code I posted. Does that make
> > more sense? Sorry for the confusion, working on too many things at
> > once.
> >
>
>
>

[Charlie]

I agree with Don. Single-line If's save space and there's no lack of clarity
to me. I only indent the If -- Then -- Else -- End If constructs. Your
indenting problems MAY be due to ElseIf's. Those I avoid like the plague.
They are difficult to balance and indent (not one End If per If) But these
are just personal style preferences.

"Bob Phillips" wrote:

> I avoid them like the plague Don, spent too much wasted time trying to line
> up Ifs ... End Ifs. I think the lack of clarity and potential maintenance
> overhead dwarfs the initial effort.
>
> The only time I use anything like that is with Iif.
>
> Regards
>
> Bob
>
> "Don Guillett" <donaldb@281.com> wrote in message
> news:OxhpbJnSFHA.1236@TK2MSFTNGP14.phx.gbl...
> > I use single line ifs much of the time
> >
> > --
> > Don Guillett
> > SalesAid Software
> > donaldb@281.com
> > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > news:A7B69EAA-3F6C-4C82-B0C8-F63590B3FC38@microsoft.com...
> > > Agreed!
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > I see your point. As I say, you should indent Ifs, and don't use
> single
> > line
> > > > Ifs, it all gets too confusing.
> > > >
> > > > Bob
> > > >
> > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > > news:576C31D0-B553-4F68-BC1A-B50EA74828F7@microsoft.com...
> > > > > But it could be End If to If Not TopCell Is Nothing which
> admittedly
> > > > could
> > > > > come before If .name as per Don's reply. Both are valid I think.
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > No, he has a line continuation, so no End If needed.
> > > > > >
> > > > > > --
> > > > > > HTH
> > > > > >
> > > > > > Bob Phillips
> > > > > >
> > > > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > > > > news:26927AED-559E-4246-84E6-1D32263FAB3B@microsoft.com...
> > > > > > > Hi,
> > > > > > >
> > > > > > > If .Name Like "*[##]" Then _
> > > > > > > .Name = Left(.Name, Len(.Name) - 2)
> > > > > > > End If ...... Missing here
> > > > > > >
> > > > > > > HTh
> > > > > > >
> > > > > > > "davegb" wrote:
> > > > > > >
> > > > > > > > I've combined 2 macros that I got help writing here.
> > > > > > > > Sub AllSheetsToggleProtectWInd()
> > > > > > > > '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
> > > > > > > > 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"
> > > > > > > > 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<-----[end with without with error]
> > > > > > > >
> > > > > > > > Next wkSht
> > > > > > > >
> > > > > > > > End Sub
> > > > > > > >
> > > > > > > >
> > > > > > > > But when I run it, I get an error of "End with without if",
> even
> > > > though
> > > > > > > > there's very clearly a "with wksht" statement at the top. If I
> > > > remark
> > > > > > > > out the End with, I get a "Next without For" error, even
> though
> > > > there's
> > > > > > > > a "For" statement. So any ideas on why VBA can't see my with
> or
> > For
> > > > > > > > each statements?
> > > > > > > > Thanks in advance.
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
>
>
>

[davegb]

Boy, is my face red! Somehow, as I wrote the message, I put down that
there was an "End If w/o an If" error. It's an "End with w/o with"
error, which is what I indicated in the code I posted. Does that make
more sense? Sorry for the confusion, working on too many things at
once.

[Bob Phillips]

I avoid them like the plague Don, spent too much wasted time trying to line
up Ifs ... End Ifs. I think the lack of clarity and potential maintenance
overhead dwarfs the initial effort.

The only time I use anything like that is with Iif.

Regards

Bob

"Don Guillett" <donaldb@281.com> wrote in message
news:OxhpbJnSFHA.1236@TK2MSFTNGP14.phx.gbl...
> I use single line ifs much of the time
>
> --
> Don Guillett
> SalesAid Software
> donaldb@281.com
> "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> news:A7B69EAA-3F6C-4C82-B0C8-F63590B3FC38@microsoft.com...
> > Agreed!
> >
> > "Bob Phillips" wrote:
> >
> > > I see your point. As I say, you should indent Ifs, and don't use
single
> line
> > > Ifs, it all gets too confusing.
> > >
> > > Bob
> > >
> > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > news:576C31D0-B553-4F68-BC1A-B50EA74828F7@microsoft.com...
> > > > But it could be End If to If Not TopCell Is Nothing which
admittedly
> > > could
> > > > come before If .name as per Don's reply. Both are valid I think.
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > No, he has a line continuation, so no End If needed.
> > > > >
> > > > > --
> > > > > HTH
> > > > >
> > > > > Bob Phillips
> > > > >
> > > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > > > news:26927AED-559E-4246-84E6-1D32263FAB3B@microsoft.com...
> > > > > > Hi,
> > > > > >
> > > > > > If .Name Like "*[##]" Then _
> > > > > > .Name = Left(.Name, Len(.Name) - 2)
> > > > > > End If ...... Missing here
> > > > > >
> > > > > > HTh
> > > > > >
> > > > > > "davegb" wrote:
> > > > > >
> > > > > > > I've combined 2 macros that I got help writing here.
> > > > > > > Sub AllSheetsToggleProtectWInd()
> > > > > > > '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
> > > > > > > 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"
> > > > > > > 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<-----[end with without with error]
> > > > > > >
> > > > > > > Next wkSht
> > > > > > >
> > > > > > > End Sub
> > > > > > >
> > > > > > >
> > > > > > > But when I run it, I get an error of "End with without if",
even
> > > though
> > > > > > > there's very clearly a "with wksht" statement at the top. If I
> > > remark
> > > > > > > out the End with, I get a "Next without For" error, even
though
> > > there's
> > > > > > > a "For" statement. So any ideas on why VBA can't see my with
or
> For
> > > > > > > each statements?
> > > > > > > Thanks in advance.
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>

[Charlie]

Right, properly indenting the lines after

If Not TopCell Is Nothing Then ' if it found "top"

would've helped.

P.S. One line If statements are OK with me if they fit nicely on one line.


"Toppers" wrote:

> But it could be End If to If Not TopCell Is Nothing which admittedly could
> come before If .name as per Don's reply. Both are valid I think.
>
> "Bob Phillips" wrote:
>
> > No, he has a line continuation, so no End If needed.
> >
> > --
> > HTH
> >
> > Bob Phillips
> >
> > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > news:26927AED-559E-4246-84E6-1D32263FAB3B@microsoft.com...
> > > Hi,
> > >
> > > If .Name Like "*[##]" Then _
> > > .Name = Left(.Name, Len(.Name) - 2)
> > > End If ...... Missing here
> > >
> > > HTh
> > >
> > > "davegb" wrote:
> > >
> > > > I've combined 2 macros that I got help writing here.
> > > > Sub AllSheetsToggleProtectWInd()
> > > > '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
> > > > 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"
> > > > 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<-----[end with without with error]
> > > >
> > > > Next wkSht
> > > >
> > > > End Sub
> > > >
> > > >
> > > > But when I run it, I get an error of "End with without if", even though
> > > > there's very clearly a "with wksht" statement at the top. If I remark
> > > > out the End with, I get a "Next without For" error, even though there's
> > > > a "For" statement. So any ideas on why VBA can't see my with or For
> > > > each statements?
> > > > Thanks in advance.
> > > >
> > > >
> >
> >
> >

[Don Guillett]

I use single line ifs much of the time

--
Don Guillett
SalesAid Software
donaldb@281.com
"Toppers" <Toppers@discussions.microsoft.com> wrote in message
news:A7B69EAA-3F6C-4C82-B0C8-F63590B3FC38@microsoft.com...
> Agreed!
>
> "Bob Phillips" wrote:
>
> > I see your point. As I say, you should indent Ifs, and don't use single
line
> > Ifs, it all gets too confusing.
> >
> > Bob
> >
> > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > news:576C31D0-B553-4F68-BC1A-B50EA74828F7@microsoft.com...
> > > But it could be End If to If Not TopCell Is Nothing which admittedly
> > could
> > > come before If .name as per Don's reply. Both are valid I think.
> > >
> > > "Bob Phillips" wrote:
> > >
> > > > No, he has a line continuation, so no End If needed.
> > > >
> > > > --
> > > > HTH
> > > >
> > > > Bob Phillips
> > > >
> > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > > news:26927AED-559E-4246-84E6-1D32263FAB3B@microsoft.com...
> > > > > Hi,
> > > > >
> > > > > If .Name Like "*[##]" Then _
> > > > > .Name = Left(.Name, Len(.Name) - 2)
> > > > > End If ...... Missing here
> > > > >
> > > > > HTh
> > > > >
> > > > > "davegb" wrote:
> > > > >
> > > > > > I've combined 2 macros that I got help writing here.
> > > > > > Sub AllSheetsToggleProtectWInd()
> > > > > > '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
> > > > > > 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"
> > > > > > 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<-----[end with without with error]
> > > > > >
> > > > > > Next wkSht
> > > > > >
> > > > > > End Sub
> > > > > >
> > > > > >
> > > > > > But when I run it, I get an error of "End with without if", even
> > though
> > > > > > there's very clearly a "with wksht" statement at the top. If I
> > remark
> > > > > > out the End with, I get a "Next without For" error, even though
> > there's
> > > > > > a "For" statement. So any ideas on why VBA can't see my with or
For
> > > > > > each statements?
> > > > > > Thanks in advance.
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >

[Toppers]

Agreed!

"Bob Phillips" wrote:

> I see your point. As I say, you should indent Ifs, and don't use single line
> Ifs, it all gets too confusing.
>
> Bob
>
> "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> news:576C31D0-B553-4F68-BC1A-B50EA74828F7@microsoft.com...
> > But it could be End If to If Not TopCell Is Nothing which admittedly
> could
> > come before If .name as per Don's reply. Both are valid I think.
> >
> > "Bob Phillips" wrote:
> >
> > > No, he has a line continuation, so no End If needed.
> > >
> > > --
> > > HTH
> > >
> > > Bob Phillips
> > >
> > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > news:26927AED-559E-4246-84E6-1D32263FAB3B@microsoft.com...
> > > > Hi,
> > > >
> > > > If .Name Like "*[##]" Then _
> > > > .Name = Left(.Name, Len(.Name) - 2)
> > > > End If ...... Missing here
> > > >
> > > > HTh
> > > >
> > > > "davegb" wrote:
> > > >
> > > > > I've combined 2 macros that I got help writing here.
> > > > > Sub AllSheetsToggleProtectWInd()
> > > > > '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
> > > > > 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"
> > > > > 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<-----[end with without with error]
> > > > >
> > > > > Next wkSht
> > > > >
> > > > > End Sub
> > > > >
> > > > >
> > > > > But when I run it, I get an error of "End with without if", even
> though
> > > > > there's very clearly a "with wksht" statement at the top. If I
> remark
> > > > > out the End with, I get a "Next without For" error, even though
> there's
> > > > > a "For" statement. So any ideas on why VBA can't see my with or For
> > > > > each statements?
> > > > > Thanks in advance.
> > > > >
> > > > >
> > >
> > >
> > >
>
>
>

[Bob Phillips]

Well we are completely at odds. The lack of clarity comes when you have
single line Ifs and Multi line Ifs combined, as you lose some of the power
of indenting then.

I don't have indenting problems, because I indent. And I use ElseIf
liberally, it makes logical sense, and it avoids a myriad of indents in
those cases. Of course, I am just as likely to use Case statements, but I
would never rule out ElseIf. There is one End If per If, just an unlimited
number of ElseIf.

Bob


"Charlie" <Charlie@discussions.microsoft.com> wrote in message
news:EC3D7FE9-FDBD-41E6-A5AA-9B20CC2BC202@microsoft.com...
> I agree with Don. Single-line If's save space and there's no lack of
clarity
> to me. I only indent the If -- Then -- Else -- End If constructs. Your
> indenting problems MAY be due to ElseIf's. Those I avoid like the plague.
> They are difficult to balance and indent (not one End If per If) But
these
> are just personal style preferences.
>
> "Bob Phillips" wrote:
>
> > I avoid them like the plague Don, spent too much wasted time trying to
line
> > up Ifs ... End Ifs. I think the lack of clarity and potential
maintenance
> > overhead dwarfs the initial effort.
> >
> > The only time I use anything like that is with Iif.
> >
> > Regards
> >
> > Bob
> >
> > "Don Guillett" <donaldb@281.com> wrote in message
> > news:OxhpbJnSFHA.1236@TK2MSFTNGP14.phx.gbl...
> > > I use single line ifs much of the time
> > >
> > > --
> > > Don Guillett
> > > SalesAid Software
> > > donaldb@281.com
> > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > news:A7B69EAA-3F6C-4C82-B0C8-F63590B3FC38@microsoft.com...
> > > > Agreed!
> > > >
> > > > "Bob Phillips" wrote:
> > > >
> > > > > I see your point. As I say, you should indent Ifs, and don't use
> > single
> > > line
> > > > > Ifs, it all gets too confusing.
> > > > >
> > > > > Bob
> > > > >
> > > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > > > news:576C31D0-B553-4F68-BC1A-B50EA74828F7@microsoft.com...
> > > > > > But it could be End If to If Not TopCell Is Nothing which
> > admittedly
> > > > > could
> > > > > > come before If .name as per Don's reply. Both are valid I
think.
> > > > > >
> > > > > > "Bob Phillips" wrote:
> > > > > >
> > > > > > > No, he has a line continuation, so no End If needed.
> > > > > > >
> > > > > > > --
> > > > > > > HTH
> > > > > > >
> > > > > > > Bob Phillips
> > > > > > >
> > > > > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > > > > > news:26927AED-559E-4246-84E6-1D32263FAB3B@microsoft.com...
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > If .Name Like "*[##]" Then _
> > > > > > > > .Name = Left(.Name, Len(.Name) - 2)
> > > > > > > > End If ...... Missing here
> > > > > > > >
> > > > > > > > HTh
> > > > > > > >
> > > > > > > > "davegb" wrote:
> > > > > > > >
> > > > > > > > > I've combined 2 macros that I got help writing here.
> > > > > > > > > Sub AllSheetsToggleProtectWInd()
> > > > > > > > > '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
> > > > > > > > > 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"
> > > > > > > > > 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<-----[end with without with error]
> > > > > > > > >
> > > > > > > > > Next wkSht
> > > > > > > > >
> > > > > > > > > End Sub
> > > > > > > > >
> > > > > > > > >
> > > > > > > > > But when I run it, I get an error of "End with without
if",
> > even
> > > > > though
> > > > > > > > > there's very clearly a "with wksht" statement at the top.
If I
> > > > > remark
> > > > > > > > > out the End with, I get a "Next without For" error, even
> > though
> > > > > there's
> > > > > > > > > a "For" statement. So any ideas on why VBA can't see my
with
> > or
> > > For
> > > > > > > > > each statements?
> > > > > > > > > Thanks in advance.
> > > > > > > > >
> > > > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> >
> >
> >

[Don Guillett]

Some like chocolate and some like vanilla.

--
Don Guillett
SalesAid Software
donaldb@281.com
"Bob Phillips" <bob.phillips@notheretiscali.co.uk> wrote in message
news:e3hYErnSFHA.3144@tk2msftngp13.phx.gbl...
> Well we are completely at odds. The lack of clarity comes when you have
> single line Ifs and Multi line Ifs combined, as you lose some of the power
> of indenting then.
>
> I don't have indenting problems, because I indent. And I use ElseIf
> liberally, it makes logical sense, and it avoids a myriad of indents in
> those cases. Of course, I am just as likely to use Case statements, but I
> would never rule out ElseIf. There is one End If per If, just an unlimited
> number of ElseIf.
>
> Bob
>
>
> "Charlie" <Charlie@discussions.microsoft.com> wrote in message
> news:EC3D7FE9-FDBD-41E6-A5AA-9B20CC2BC202@microsoft.com...
> > I agree with Don. Single-line If's save space and there's no lack of
> clarity
> > to me. I only indent the If -- Then -- Else -- End If constructs. Your
> > indenting problems MAY be due to ElseIf's. Those I avoid like the
plague.
> > They are difficult to balance and indent (not one End If per If) But
> these
> > are just personal style preferences.
> >
> > "Bob Phillips" wrote:
> >
> > > I avoid them like the plague Don, spent too much wasted time trying to
> line
> > > up Ifs ... End Ifs. I think the lack of clarity and potential
> maintenance
> > > overhead dwarfs the initial effort.
> > >
> > > The only time I use anything like that is with Iif.
> > >
> > > Regards
> > >
> > > Bob
> > >
> > > "Don Guillett" <donaldb@281.com> wrote in message
> > > news:OxhpbJnSFHA.1236@TK2MSFTNGP14.phx.gbl...
> > > > I use single line ifs much of the time
> > > >
> > > > --
> > > > Don Guillett
> > > > SalesAid Software
> > > > donaldb@281.com
> > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > > news:A7B69EAA-3F6C-4C82-B0C8-F63590B3FC38@microsoft.com...
> > > > > Agreed!
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > I see your point. As I say, you should indent Ifs, and don't use
> > > single
> > > > line
> > > > > > Ifs, it all gets too confusing.
> > > > > >
> > > > > > Bob
> > > > > >
> > > > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > > > > news:576C31D0-B553-4F68-BC1A-B50EA74828F7@microsoft.com...
> > > > > > > But it could be End If to If Not TopCell Is Nothing which
> > > admittedly
> > > > > > could
> > > > > > > come before If .name as per Don's reply. Both are valid I
> think.
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > No, he has a line continuation, so no End If needed.
> > > > > > > >
> > > > > > > > --
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > Bob Phillips
> > > > > > > >
> > > > > > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in
message
> > > > > > > > news:26927AED-559E-4246-84E6-1D32263FAB3B@microsoft.com...
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > If .Name Like "*[##]" Then _
> > > > > > > > > .Name = Left(.Name, Len(.Name) - 2)
> > > > > > > > > End If ...... Missing here
> > > > > > > > >
> > > > > > > > > HTh
> > > > > > > > >
> > > > > > > > > "davegb" wrote:
> > > > > > > > >
> > > > > > > > > > I've combined 2 macros that I got help writing here.
> > > > > > > > > > Sub AllSheetsToggleProtectWInd()
> > > > > > > > > > '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
> > > > > > > > > > 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"
> > > > > > > > > > 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<-----[end with without with error]
> > > > > > > > > >
> > > > > > > > > > Next wkSht
> > > > > > > > > >
> > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > But when I run it, I get an error of "End with without
> if",
> > > even
> > > > > > though
> > > > > > > > > > there's very clearly a "with wksht" statement at the
top.
> If I
> > > > > > remark
> > > > > > > > > > out the End with, I get a "Next without For" error, even
> > > though
> > > > > > there's
> > > > > > > > > > a "For" statement. So any ideas on why VBA can't see my
> with
> > > or
> > > > For
> > > > > > > > > > each statements?
> > > > > > > > > > Thanks in advance.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>

[Charlie]

Yes, the Case statement is best when there's a multitude of ElseIf's.

As I said, it's a personal style thing, considering that either way is
logically correct.


"Bob Phillips" wrote:

> Well we are completely at odds. The lack of clarity comes when you have
> single line Ifs and Multi line Ifs combined, as you lose some of the power
> of indenting then.
>
> I don't have indenting problems, because I indent. And I use ElseIf
> liberally, it makes logical sense, and it avoids a myriad of indents in
> those cases. Of course, I am just as likely to use Case statements, but I
> would never rule out ElseIf. There is one End If per If, just an unlimited
> number of ElseIf.
>
> Bob
>
>
> "Charlie" <Charlie@discussions.microsoft.com> wrote in message
> news:EC3D7FE9-FDBD-41E6-A5AA-9B20CC2BC202@microsoft.com...
> > I agree with Don. Single-line If's save space and there's no lack of
> clarity
> > to me. I only indent the If -- Then -- Else -- End If constructs. Your
> > indenting problems MAY be due to ElseIf's. Those I avoid like the plague.
> > They are difficult to balance and indent (not one End If per If) But
> these
> > are just personal style preferences.
> >
> > "Bob Phillips" wrote:
> >
> > > I avoid them like the plague Don, spent too much wasted time trying to
> line
> > > up Ifs ... End Ifs. I think the lack of clarity and potential
> maintenance
> > > overhead dwarfs the initial effort.
> > >
> > > The only time I use anything like that is with Iif.
> > >
> > > Regards
> > >
> > > Bob
> > >
> > > "Don Guillett" <donaldb@281.com> wrote in message
> > > news:OxhpbJnSFHA.1236@TK2MSFTNGP14.phx.gbl...
> > > > I use single line ifs much of the time
> > > >
> > > > --
> > > > Don Guillett
> > > > SalesAid Software
> > > > donaldb@281.com
> > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > > news:A7B69EAA-3F6C-4C82-B0C8-F63590B3FC38@microsoft.com...
> > > > > Agreed!
> > > > >
> > > > > "Bob Phillips" wrote:
> > > > >
> > > > > > I see your point. As I say, you should indent Ifs, and don't use
> > > single
> > > > line
> > > > > > Ifs, it all gets too confusing.
> > > > > >
> > > > > > Bob
> > > > > >
> > > > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > > > > news:576C31D0-B553-4F68-BC1A-B50EA74828F7@microsoft.com...
> > > > > > > But it could be End If to If Not TopCell Is Nothing which
> > > admittedly
> > > > > > could
> > > > > > > come before If .name as per Don's reply. Both are valid I
> think.
> > > > > > >
> > > > > > > "Bob Phillips" wrote:
> > > > > > >
> > > > > > > > No, he has a line continuation, so no End If needed.
> > > > > > > >
> > > > > > > > --
> > > > > > > > HTH
> > > > > > > >
> > > > > > > > Bob Phillips
> > > > > > > >
> > > > > > > > "Toppers" <Toppers@discussions.microsoft.com> wrote in message
> > > > > > > > news:26927AED-559E-4246-84E6-1D32263FAB3B@microsoft.com...
> > > > > > > > > Hi,
> > > > > > > > >
> > > > > > > > > If .Name Like "*[##]" Then _
> > > > > > > > > .Name = Left(.Name, Len(.Name) - 2)
> > > > > > > > > End If ...... Missing here
> > > > > > > > >
> > > > > > > > > HTh
> > > > > > > > >
> > > > > > > > > "davegb" wrote:
> > > > > > > > >
> > > > > > > > > > I've combined 2 macros that I got help writing here.
> > > > > > > > > > Sub AllSheetsToggleProtectWInd()
> > > > > > > > > > '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
> > > > > > > > > > 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"
> > > > > > > > > > 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<-----[end with without with error]
> > > > > > > > > >
> > > > > > > > > > Next wkSht
> > > > > > > > > >
> > > > > > > > > > End Sub
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > But when I run it, I get an error of "End with without
> if",
> > > even
> > > > > > though
> > > > > > > > > > there's very clearly a "with wksht" statement at the top.
> If I
> > > > > > remark
> > > > > > > > > > out the End with, I get a "Next without For" error, even
> > > though
> > > > > > there's
> > > > > > > > > > a "For" statement. So any ideas on why VBA can't see my
> with
> > > or
> > > > For
> > > > > > > > > > each statements?
> > > > > > > > > > Thanks in advance.
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > >
> > >
> > >
>
>
>

[davegb]

Not sure I'm following some of your comments re indenting properly. I
imagine there are different approaches, just like naming variables, but
can someone give me some guidelines to help avoid this type of problem?
For instance, post how you would have indented my macro?
Thanks!

[JulieD]

Hi Dave

the way i teach it is, consider statements like SUB / END SUB; WITH / END
WITH, IF / ELSEIF / END IF etc as brackets ... everything inside the
brackets should be indented one tab each time ... this way, you can easily
read down a column and spot missing END IF or END WITH etc .. so your macro
(not knowing exactly where your IF statment finishes), i would write as

Sub AllSheetsToggleProtectWInd()
{BLANK}
{TAB}'for all sheets in currently active workbook, assigned to button
{TAB}Dim TopCell As Range
{TAB}Dim TopCol As Range
{TAB}Dim Cols2Hide As Range
{TAB}Dim wkSht As Worksheet
{BLANK}
{TAB}FOR EACH wkSht INActiveWorkbook.Worksheets
{TAB}{TAB}WITH wkSht
{TAB}{TAB}{TAB}IF.ProtectContents Then
{TAB}{TAB}{TAB}{TAB}.Unprotect Password:=PWORD
{TAB}{TAB}{TAB}{TAB}.Name = .Name & "##"
{TAB}{TAB}{TAB}{TAB}.Columns.Hidden = False
{TAB}{TAB}{TAB}ELSE
{TAB}{TAB}{TAB}{TAB}Set TopCell = .Rows(3).Find(What:="top",
LookIn:=xlValues)
{TAB}{TAB}{TAB}{TAB}If Not TopCell Is Nothing Then ' if it found "top"
{TAB}{TAB}{TAB}{TAB}{TAB}Set TopCol = .Columns(TopCell.Column)
{TAB}{TAB}{TAB}{TAB}{TAB}Set Cols2Hide = .Range(TopCol, .Columns("AC"))
{TAB}{TAB}{TAB}{TAB}{TAB}Cols2Hide.Hidden = True
{TAB}{TAB}{TAB}{TAB}{TAB}.Protect Password:=PWORD
{TAB}{TAB}{TAB}{TAB}END IF 'assuming it goes here
{TAB}{TAB}{TAB}{TAB}IF .Name Like "*[##]" Then
{TAB}{TAB}{TAB}{TAB}{TAB}.Name = Left(.Name, Len(.Name) - 2)
{TAB}{TAB}{TAB}{TAB}END IF 'i'm not a big fan of one line IF statements
either
{TAB}{TAB}{TAB}END IF
{TAB}{TAB}END WITH
{TAB}NEXT wkSht
End Sub


--
Cheers
JulieD
check out www.hcts.net.au/tipsandtricks.htm
....well i'm working on it anyway
"davegb" <davegb@safebrowse.com> wrote in message
news:1114552449.889445.84160@o13g2000cwo.googlegroups.com...
> Not sure I'm following some of your comments re indenting properly. I
> imagine there are different approaches, just like naming variables, but
> can someone give me some guidelines to help avoid this type of problem?
> For instance, post how you would have indented my macro?
> Thanks!
>

[davegb]

Thanks, Julie.

[bhofsetz]

Dave,
Did you ever resolve this problem? It is clearly a missing End If that is causing your problem as pointed out by others. The question now is where do you want the End If? I placed it in three different places and all three allow the code to run. It just depends on what the code is trying to do as to where it should be placed for the desired results.

See below for different places it worked for me.


Sub AllSheetsToggleProtectWInd()
'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
With wkSht
If .ProtectContents Then
.Unprotect Password:=PWORD
.Name = .Name & "##"
.Columns.Hidden = False
Else
Set TopCell = .Rows(3).Find(What:="top",LookIn:=xlValues)
End If 'placed here will close your first If ... Else statement without the other If's nested

If Not TopCell Is Nothing Then ' if it found "top"
Set TopCol = .Columns(TopCell.Column)
Set Cols2Hide = .Range(TopCol, .Columns("AC"))
Cols2Hide.Hidden = True
.Protect Password:=PWORD)
End If 'Placed here will close your second If statement without nesting the below one line If...Then statement

If .Name Like "*[##]" Then _
.Name = Left(.Name, Len(.Name) - 2))
End If 'Placed here will close your second If statement with the one line If...Then nested

End If 'This will either close your first or second If statement depending on where you place the first End If
End With<-----[end with without with error]

Next wkSht

End Sub

Hope This Helps

B