Hello All,

Is there some way in Excel 2007 VBA macro code to have a block of code
perform if a condition is true and have the code block skipped if the
condition is false? So far I have only been able to get an If...Then
condition to work with a single line of code following the Then statement.
Do I use GoSub...Return?

See the following code:

Range("I6").Select
If ActiveCell.FormulaR1C1 = "HCP Added" Then _
ActiveSheet.Unprotect
Range("I6").Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate
If Sheets("Data Entry").Visible = True Then
Sheets("Data Entry").Select
ActiveWindow.SelectedSheets.Visible = False
End If
If Sheets("Main Menu").Visible = True Then
Sheets("Main Menu").Select
Range("F4:H4").Select
End If
End Sub

Can you assist and tell me what I am doing wrong?
--
Brian

Remove the _ in this line.

If ActiveCell.FormulaR1C1 = "HCP Added" Then _

The _ character is used to continue a line so this is being treated as a one
line section of code (which can be used to do an if then without needed to do
an end if)

--
If this helps, please remember to click yes.


"Brian" wrote:

Hello All,

Is there some way in Excel 2007 VBA macro code to have a block of code
perform if a condition is true and have the code block skipped if the
condition is false? So far I have only been able to get an If...Then
condition to work with a single line of code following the Then statement.
Do I use GoSub...Return?

See the following code:

Range("I6").Select
If ActiveCell.FormulaR1C1 = "HCP Added" Then _
ActiveSheet.Unprotect
Range("I6").Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate
If Sheets("Data Entry").Visible = True Then
Sheets("Data Entry").Select
ActiveWindow.SelectedSheets.Visible = False
End If
If Sheets("Main Menu").Visible = True Then
Sheets("Main Menu").Select
Range("F4:H4").Select
End If
End Sub

Can you assist and tell me what I am doing wrong?
--
Brian

Thanks Paul.

So the "_" character causes the limitation to only use the next line?

The code example below was not the best example since it did not have an
action to perform if the condition was False. Here is a better example:
If Sheets("File Naming Conventions").Visible = False Or Sheets("Grant
Forms").Visible = False _
Then
If MsgBox("Are you ready to add in the Event data for this
Expense from the Master Ledger?", vbYesNo + vbQuestion) = vbYes _
Then
Run ("AddEventDatatoCheckLedger")
End If
End If
If Sheets("File Naming Conventions").Visible = True Then
Sheets("File Naming Conventions").Select
Range("C6").Select
End If
If Sheets("Grant Forms").Visible = True Then
Sheets("Grant Forms").Select
Range("P41").Select
End If
---------------------
In the above, I want Excel to check to see whether either of the referenced
sheets are visible and if not, open up a dialog box asking the user if they
are ready to add event detail by running another macro. If either of the two
sheets are visible, I want Excel to skip over the message box commands and go
to either one of the open forms and park the activecell cursor at a specific
location.

If I remove the "_" in following line:
If Sheets("File Naming Conventions").Visible = False Or Sheets("Grant
Forms").Visible = False _

.... the Then statement turns red on the following line indicating an error.
And if I leave it in the macro (when run) still brings up the dialog box even
though the "Grant Forms" sheet is visible. Any suggestions?

--
Brian


"Paul C" wrote:

Remove the _ in this line.

If ActiveCell.FormulaR1C1 = "HCP Added" Then _

The _ character is used to continue a line so this is being treated as a one
line section of code (which can be used to do an if then without needed to do
an end if)

--
If this helps, please remember to click yes.


"Brian" wrote:

Hello All,

Is there some way in Excel 2007 VBA macro code to have a block of code
perform if a condition is true and have the code block skipped if the
condition is false? So far I have only been able to get an If...Then
condition to work with a single line of code following the Then statement.
Do I use GoSub...Return?

See the following code:

Range("I6").Select
If ActiveCell.FormulaR1C1 = "HCP Added" Then _
ActiveSheet.Unprotect
Range("I6").Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate
If Sheets("Data Entry").Visible = True Then
Sheets("Data Entry").Select
ActiveWindow.SelectedSheets.Visible = False
End If
If Sheets("Main Menu").Visible = True Then
Sheets("Main Menu").Select
Range("F4:H4").Select
End If
End Sub

Can you assist and tell me what I am doing wrong?
--
Brian

So the "_" character causes the limitation to only use the next line?

Strictly speaking, the two character sequence <space<underscore
indicates a line continuation. It is possible that variable name might
end in an underscore, so a single line may thus end in an undersco

Dim X
Dim Y_
Y_ = 123
X = Y_

Since the underscore in the last line here is the end of a variable
name, not a line continuation, VBA requires <space_ as the line
continuation marker.

You cannot use line continuation arbitrarily. You cannot split a
literal string with a _ sequence. E.g, the following won't work:

Dim S As String
S = "one line
two line"

If you need to split a text string, both parts must be enclosed in
quotes and combined with + or &. E.g,

Dim S As String
S = "one line" & _
" two line"

If you want the line split to be displayed to the user, use

S = "one line" & vbNewLine & _
"two line"

You cannot split other language elements except where they naturally
split on word or parameter boundaries. E.g,

' No good
MsgBox("Hello",vbYes _
No)

' OK
MsgBox("hello", _
vbYesNo)


You need to clean up the code so it looks good, to make it easier to
read.

If Sheets("File Naming Conventions").Visible = False Or _
Sheets("Grant Forms").Visible = False Then
If MsgBox("Are you ready to add in the Event data for this" + _
vbNewLine + " Expense from the Master Ledger?", _
vbYesNo + vbQuestion) = vbYes Then
Run ("AddEventDatatoCheckLedger")
End If
End If

This code will display the MsgBox if one or both of "File Naming
Conventions" or "Grant Forms" is not visible. The MsgBox will be
skipped only if both sheet are visible. If you want the MsgBox to
display only if BOTH sheets are not visible, the change the "Or" to an
"And".

I would encourage you not to use True and False when testing a sheet's
Visible property. The Visible property can be one of three, not two,
values: xlSheetVisible (= -1 = True), or xlSheetHidden (= 0 = False),
or xlSheetVeryHidden (= 2, which is neither True nor False). You
should use the constant names xlSheetVisible, xlSheetHidden, or
xlSheetVeryHidden.

If you want to test if a sheet is visible, use code like

If Sheets(1).Visible = xlVisible Then
' sheet is visible
End If

If you want to test if a sheet is not visible, use

If Sheets(1).Visible < xlVisible Then
' sheet is either xlSheetHidden or xlSheetVeryHidden
End If

Using True and False, if Sheet1 is xlSheetVeryHidden, the code

If Worksheets(1).Visible = False Then
Debug.Print "false"
End If

won't work because the Visible property ( = xlSheetVeryHidden = 2) is
not equal to False (2< 0).

A sheet whose visibility is xlVeryHidden will not be displayed in the
Unhide Sheet dialog box. It can be made visible or xlSheetHidden only
via code. Similarly, the xlSheetVeryHidden attribute can be set only
via code. There is no Excel UI element to make a sheet
xlSheetVeryHidden or to set an xlSheetVeryHidden sheet to
xlSheetVisible or xlSheetHidden. The user cannot determine that
worksheet that is xlSheetVeryHidden exists without resorting to code.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)



On Fri, 11 Sep 2009 11:08:02 -0700, Brian
wrote:

Thanks Paul.

So the "_" character causes the limitation to only use the next line?

The code example below was not the best example since it did not have an
action to perform if the condition was False. Here is a better example:
If Sheets("File Naming Conventions").Visible = False Or Sheets("Grant
Forms").Visible = False _
Then
If MsgBox("Are you ready to add in the Event data for this
Expense from the Master Ledger?", vbYesNo + vbQuestion) = vbYes _
Then
Run ("AddEventDatatoCheckLedger")
End If
End If
If Sheets("File Naming Conventions").Visible = True Then
Sheets("File Naming Conventions").Select
Range("C6").Select
End If
If Sheets("Grant Forms").Visible = True Then
Sheets("Grant Forms").Select
Range("P41").Select
End If
---------------------
In the above, I want Excel to check to see whether either of the referenced
sheets are visible and if not, open up a dialog box asking the user if they
are ready to add event detail by running another macro. If either of the two
sheets are visible, I want Excel to skip over the message box commands and go
to either one of the open forms and park the activecell cursor at a specific
location.

If I remove the "_" in following line:
If Sheets("File Naming Conventions").Visible = False Or Sheets("Grant
Forms").Visible = False _

... the Then statement turns red on the following line indicating an error.
And if I leave it in the macro (when run) still brings up the dialog box even
though the "Grant Forms" sheet is visible. Any suggestions?

Your problem was a continuation after the first then _
I would write it like this

With ActiveSheet' you could substitute the sheet name & NOT select
If .Range("i6") = "HCP Added" Then
.Unprotect
.Range("I6").ClearContents
.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate

With Sheets("Data Entry")
If .Visible = True Then .Visible = False
End With

With Sheets("Main Menu")
If .Visible = True Then Application.Goto .Range("F4:H4")
End With

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brian" wrote in message
...
Hello All,

Is there some way in Excel 2007 VBA macro code to have a block of code
perform if a condition is true and have the code block skipped if the
condition is false? So far I have only been able to get an If...Then
condition to work with a single line of code following the Then statement.
Do I use GoSub...Return?

See the following code:

Range("I6").Select
If ActiveCell.FormulaR1C1 = "HCP Added" Then _
ActiveSheet.Unprotect
Range("I6").Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate
If Sheets("Data Entry").Visible = True Then
Sheets("Data Entry").Select
ActiveWindow.SelectedSheets.Visible = False
End If
If Sheets("Main Menu").Visible = True Then
Sheets("Main Menu").Select
Range("F4:H4").Select
End If
End Sub

Can you assist and tell me what I am doing wrong?
--
Brian

Thanks Don & Chip,

Your posts answered some other questions I had and gave me some formating
guidance, however, my particular question (perhaps not phrased properly) was
can an "If ... Then" sequence handle more that a single line of code per
result.

Back in the old days, before VB, we used a GOSUB ... RETURN sequence that
would run a multi-line block of code and RETURN to the next line in sequence
once completed, e.g.
IF var = True THEN GOSUB SubRoutine1

SubRoutine1:
1 Line of code
2 Line of code
3 Lines of code
RETURN

In the above case, if the var = False the GOSUB would be ignored and the
next line of code following the IF...THEN line would be executed. My
question is simply this: Using the above example, how do I get code lines
1-3 to execute only if var= True. Do I simply create a separate macro
subroutine and use this format?

IF var = True THEN RUN("MacroSubRoutine1")

Since the VBA indentation format in the If block does not seem to work for
multiple lines.

Thanks for all of your help.
--
Brian


"Don Guillett" wrote:

Your problem was a continuation after the first then _
I would write it like this

With ActiveSheet' you could substitute the sheet name & NOT select
If .Range("i6") = "HCP Added" Then
.Unprotect
.Range("I6").ClearContents
.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate

With Sheets("Data Entry")
If .Visible = True Then .Visible = False
End With

With Sheets("Main Menu")
If .Visible = True Then Application.Goto .Range("F4:H4")
End With

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brian" wrote in message
...
Hello All,

Is there some way in Excel 2007 VBA macro code to have a block of code
perform if a condition is true and have the code block skipped if the
condition is false? So far I have only been able to get an If...Then
condition to work with a single line of code following the Then statement.
Do I use GoSub...Return?

See the following code:

Range("I6").Select
If ActiveCell.FormulaR1C1 = "HCP Added" Then _
ActiveSheet.Unprotect
Range("I6").Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate
If Sheets("Data Entry").Visible = True Then
Sheets("Data Entry").Select
ActiveWindow.SelectedSheets.Visible = False
End If
If Sheets("Main Menu").Visible = True Then
Sheets("Main Menu").Select
Range("F4:H4").Select
End If
End Sub

Can you assist and tell me what I am doing wrong?
--
Brian

Unless I am not understanding your question the simple if will take care of
it.

IF var = True THEN do this

if false do this will not happen

if var1=2 then
range("a9").copy range("b12")
end if
next thing to do

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brian" wrote in message
...
Thanks Don & Chip,

Your posts answered some other questions I had and gave me some formating
guidance, however, my particular question (perhaps not phrased properly)
was
can an "If ... Then" sequence handle more that a single line of code per
result.

Back in the old days, before VB, we used a GOSUB ... RETURN sequence that
would run a multi-line block of code and RETURN to the next line in
sequence
once completed, e.g.
IF var = True THEN GOSUB SubRoutine1

SubRoutine1:
1 Line of code
2 Line of code
3 Lines of code
RETURN

In the above case, if the var = False the GOSUB would be ignored and the
next line of code following the IF...THEN line would be executed. My
question is simply this: Using the above example, how do I get code lines
1-3 to execute only if var= True. Do I simply create a separate macro
subroutine and use this format?

IF var = True THEN RUN("MacroSubRoutine1")

Since the VBA indentation format in the If block does not seem to work for
multiple lines.

Thanks for all of your help.
--
Brian


"Don Guillett" wrote:

Your problem was a continuation after the first then _
I would write it like this

With ActiveSheet' you could substitute the sheet name & NOT select
If .Range("i6") = "HCP Added" Then
.Unprotect
.Range("I6").ClearContents
.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate

With Sheets("Data Entry")
If .Visible = True Then .Visible = False
End With

With Sheets("Main Menu")
If .Visible = True Then Application.Goto .Range("F4:H4")
End With

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brian" wrote in message
...
Hello All,

Is there some way in Excel 2007 VBA macro code to have a block of code
perform if a condition is true and have the code block skipped if the
condition is false? So far I have only been able to get an If...Then
condition to work with a single line of code following the Then
statement.
Do I use GoSub...Return?

See the following code:

Range("I6").Select
If ActiveCell.FormulaR1C1 = "HCP Added" Then _
ActiveSheet.Unprotect
Range("I6").Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate
If Sheets("Data Entry").Visible = True Then
Sheets("Data Entry").Select
ActiveWindow.SelectedSheets.Visible = False
End If
If Sheets("Main Menu").Visible = True Then
Sheets("Main Menu").Select
Range("F4:H4").Select
End If
End Sub

Can you assist and tell me what I am doing wrong?
--
Brian

Hi Don,

I previously thought that the IF...THEN did not allow me to execute more
than one statement line, but the following test code seems to be working.
The "Yes" in B2 yields the expected formula results in A5 and C3 when the
code executes.

Range("B2").Select
If ActiveCell.FormulaR1C1 = "Yes" = True Then
Range("A5").Select
ActiveCell.FormulaR1C1 = "Hello World!"
Range("C3").Select
ActiveCell.FormulaR1C1 = "How are ya!"
Else
Range("C3").Select
ActiveCell.FormulaR1C1 = "No Comment"
End If
Range("A6").Select
ActiveCell.FormulaR1C1 = "Finished"
End Sub

I then thought perhaps my trouble was with a Message Box Boolean code
condition that would only execute one line of code per result, but the
following test code works as well.

If MsgBox("Do you wish to continue?", vbYesNo + vbQuestion) = vbYes _
Then
Range("A5").Select
ActiveCell.FormulaR1C1 = "Hello World!"
Range("C3").Select
ActiveCell.FormulaR1C1 = "How are ya!"
Else
Range("C3").Select
ActiveCell.FormulaR1C1 = "No Comment"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Choose another planet!"
End If
End Sub

There must have been some other reason my original code did not perform as
expected. I will have to dig deeper.

Thanks for all of your kind efforts!
--
Brian


"Don Guillett" wrote:

Unless I am not understanding your question the simple if will take care of
it.

IF var = True THEN do this

if false do this will not happen

if var1=2 then
range("a9").copy range("b12")
end if
next thing to do

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brian" wrote in message
...
Thanks Don & Chip,

Your posts answered some other questions I had and gave me some formating
guidance, however, my particular question (perhaps not phrased properly)
was
can an "If ... Then" sequence handle more that a single line of code per
result.

Back in the old days, before VB, we used a GOSUB ... RETURN sequence that
would run a multi-line block of code and RETURN to the next line in
sequence
once completed, e.g.
IF var = True THEN GOSUB SubRoutine1

SubRoutine1:
1 Line of code
2 Line of code
3 Lines of code
RETURN

In the above case, if the var = False the GOSUB would be ignored and the
next line of code following the IF...THEN line would be executed. My
question is simply this: Using the above example, how do I get code lines
1-3 to execute only if var= True. Do I simply create a separate macro
subroutine and use this format?

IF var = True THEN RUN("MacroSubRoutine1")

Since the VBA indentation format in the If block does not seem to work for
multiple lines.

Thanks for all of your help.
--
Brian


"Don Guillett" wrote:

Your problem was a continuation after the first then _
I would write it like this

With ActiveSheet' you could substitute the sheet name & NOT select
If .Range("i6") = "HCP Added" Then
.Unprotect
.Range("I6").ClearContents
.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate

With Sheets("Data Entry")
If .Visible = True Then .Visible = False
End With

With Sheets("Main Menu")
If .Visible = True Then Application.Goto .Range("F4:H4")
End With

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brian" wrote in message
...
Hello All,

Is there some way in Excel 2007 VBA macro code to have a block of code
perform if a condition is true and have the code block skipped if the
condition is false? So far I have only been able to get an If...Then
condition to work with a single line of code following the Then
statement.
Do I use GoSub...Return?

See the following code:

Range("I6").Select
If ActiveCell.FormulaR1C1 = "HCP Added" Then _
ActiveSheet.Unprotect
Range("I6").Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate
If Sheets("Data Entry").Visible = True Then
Sheets("Data Entry").Select
ActiveWindow.SelectedSheets.Visible = False
End If
If Sheets("Main Menu").Visible = True Then
Sheets("Main Menu").Select
Range("F4:H4").Select
End If
End Sub

Can you assist and tell me what I am doing wrong?
--
Brian

WithOUT selections

If MsgBox("continue?", vbYesNo + vbQuestion) = vbYes Then
Range("A5")= "Hello World!"
Range("C3")= "How are ya!"
Else
Range("C3")= "No Comment"
Range("A5")= "Choose another planet!"
End If



--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brian" wrote in message
...
Hi Don,

I previously thought that the IF...THEN did not allow me to execute more
than one statement line, but the following test code seems to be working.
The "Yes" in B2 yields the expected formula results in A5 and C3 when the
code executes.

Range("B2").Select
If ActiveCell.FormulaR1C1 = "Yes" = True Then
Range("A5").Select
ActiveCell.FormulaR1C1 = "Hello World!"
Range("C3").Select
ActiveCell.FormulaR1C1 = "How are ya!"
Else
Range("C3").Select
ActiveCell.FormulaR1C1 = "No Comment"
End If
Range("A6").Select
ActiveCell.FormulaR1C1 = "Finished"
End Sub

I then thought perhaps my trouble was with a Message Box Boolean code
condition that would only execute one line of code per result, but the
following test code works as well.

If MsgBox("Do you wish to continue?", vbYesNo + vbQuestion) = vbYes _
Then
Range("A5").Select
ActiveCell.FormulaR1C1 = "Hello World!"
Range("C3").Select
ActiveCell.FormulaR1C1 = "How are ya!"
Else
Range("C3").Select
ActiveCell.FormulaR1C1 = "No Comment"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Choose another planet!"
End If
End Sub

There must have been some other reason my original code did not perform as
expected. I will have to dig deeper.

Thanks for all of your kind efforts!
--
Brian


"Don Guillett" wrote:

Unless I am not understanding your question the simple if will take care
of
it.

IF var = True THEN do this

if false do this will not happen

if var1=2 then
range("a9").copy range("b12")
end if
next thing to do

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brian" wrote in message
...
Thanks Don & Chip,

Your posts answered some other questions I had and gave me some
formating
guidance, however, my particular question (perhaps not phrased
properly)
was
can an "If ... Then" sequence handle more that a single line of code
per
result.

Back in the old days, before VB, we used a GOSUB ... RETURN sequence
that
would run a multi-line block of code and RETURN to the next line in
sequence
once completed, e.g.
IF var = True THEN GOSUB SubRoutine1

SubRoutine1:
1 Line of code
2 Line of code
3 Lines of code
RETURN

In the above case, if the var = False the GOSUB would be ignored and
the
next line of code following the IF...THEN line would be executed. My
question is simply this: Using the above example, how do I get code
lines
1-3 to execute only if var= True. Do I simply create a separate macro
subroutine and use this format?

IF var = True THEN RUN("MacroSubRoutine1")

Since the VBA indentation format in the If block does not seem to work
for
multiple lines.

Thanks for all of your help.
--
Brian


"Don Guillett" wrote:

Your problem was a continuation after the first then _
I would write it like this

With ActiveSheet' you could substitute the sheet name & NOT select
If .Range("i6") = "HCP Added" Then
.Unprotect
.Range("I6").ClearContents
.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate

With Sheets("Data Entry")
If .Visible = True Then .Visible = False
End With

With Sheets("Main Menu")
If .Visible = True Then Application.Goto .Range("F4:H4")
End With

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Brian" wrote in message
...
Hello All,

Is there some way in Excel 2007 VBA macro code to have a block of
code
perform if a condition is true and have the code block skipped if
the
condition is false? So far I have only been able to get an
If...Then
condition to work with a single line of code following the Then
statement.
Do I use GoSub...Return?

See the following code:

Range("I6").Select
If ActiveCell.FormulaR1C1 = "HCP Added" Then _
ActiveSheet.Unprotect
Range("I6").Select
Selection.ClearContents
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, _
Scenarios:=True, AllowInsertingHyperlinks:=True
End If
Calculate
If Sheets("Data Entry").Visible = True Then
Sheets("Data Entry").Select
ActiveWindow.SelectedSheets.Visible = False
End If
If Sheets("Main Menu").Visible = True Then
Sheets("Main Menu").Select
Range("F4:H4").Select
End If
End Sub

Can you assist and tell me what I am doing wrong?
--
Brian