End if w/o if?

D

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

Toppers

Hi,

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

HTh
 
J

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 said:
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"
Click to expand...
########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.
Click to expand...
 
B

Bob Phillips

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

--
HTH

Bob Phillips

Toppers said:
Hi,

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

HTh

davegb said:
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.
Click to expand...
Click to expand...
 
T

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 said:
No, he has a line continuation, so no End If needed.

--
HTH

Bob Phillips

Toppers said:
Hi,

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

HTh

davegb said:
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.
Click to expand...
Click to expand...
Click to expand...
 
B

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 said:
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 said:
No, he has a line continuation, so no End If needed.

--
HTH

Bob Phillips

Toppers said:
Hi,

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

HTh

:

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

Toppers

Agreed!

Bob Phillips said:
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 said:
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 said:
No, he has a line continuation, so no End If needed.

--
HTH

Bob Phillips

Hi,

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

HTh

:

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

Don Guillett

I use single line ifs much of the time

--
Don Guillett
SalesAid Software
(e-mail address removed)
Toppers said:
Agreed!

Bob Phillips said:
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 said:
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.

:

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

--
HTH

Bob Phillips

Hi,

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

HTh

:

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

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 said:
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 said:
No, he has a line continuation, so no End If needed.

--
HTH

Bob Phillips

Toppers said:
Hi,

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

HTh

:

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

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 said:
I use single line ifs much of the time

--
Don Guillett
SalesAid Software
(e-mail address removed)
Toppers said:
Agreed!
Click to expand...
single
line
Ifs, it all gets too confusing.

Bob

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.

:

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

--
HTH

Bob Phillips

Hi,

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

HTh

:

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

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

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 :)
 
C

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 said:
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 said:
I use single line ifs much of the time

--
Don Guillett
SalesAid Software
(e-mail address removed)
Toppers said:
Agreed!

:

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

Bob

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.

:

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

--
HTH

Bob Phillips

Hi,

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

HTh

:

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
Click to expand...
or
For
each statements?
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
C

Charlie

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

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 said:
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 said:
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 said:
I use single line ifs much of the time

--
Don Guillett
SalesAid Software
(e-mail address removed)
Agreed!

:

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

Bob

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.

:

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

--
HTH

Bob Phillips

Hi,

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

HTh

:

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
Click to expand...
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
Click to expand...
with
or
For
each statements?
Thanks in advance.
Click to expand...
Click to expand...
Click to expand...
 
D

Don Guillett

Some like chocolate and some like vanilla.

--
Don Guillett
SalesAid Software
(e-mail address removed)
Bob Phillips said:
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 said:
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 said:
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

I use single line ifs much of the time

--
Don Guillett
SalesAid Software
(e-mail address removed)
Agreed!

:

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

Bob

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.

:

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

--
HTH

Bob Phillips

Hi,

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

HTh

:

I've combined 2 macros that I got help writing here.
Sub AllSheetsToggleProtectWInd()
'for all sheets in currently active workbook,
Click to expand...
Click to expand...
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
Click to expand...
Click to expand...
top.
If I
Click to expand...
 
C

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 said:
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 said:
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 said:
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

I use single line ifs much of the time

--
Don Guillett
SalesAid Software
(e-mail address removed)
Agreed!

:

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

Bob

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.

:

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

--
HTH

Bob Phillips

Hi,

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

HTh

:

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

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

variable not set error? 4
skip worksheet 6
Protect/unprotect worksheets 2
Qualifying permissions in protect unprotect macro. 4
Modifying a protect unprotect macro 10
Insert rows by group and copy value 2
Performing a Macro within a range 2
Stop the macro at the end of a certain column #2 13

Top