Problem with IF Statements Within VBA Code

G

golf4

AGAIN.... Midnight here and I can't seem to wrap my brain around the
problem I'm having with my code. I've posted it below and this is what
I'm trying to do. The code is designed to print out worksheets that
will calculate the prorated amount of rent while populating check
boxes. It worked fine until I tried adding the IF statements: IF H47
="X", then four copies of the worksheet will print out; IF H49 ="X",
then it should print out the three copies. The different cell
references represent two different programs. The problem is that my
code is now not working (with the two different IF statements). I have
a feeling that it has to do with the transition strip:

Exit Sub
Else
End If

....... but I can't seem to figure it out. Any help would be
fantastic!!!


Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
..Unprotect "led52not"
..Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..PrintOut
..Shapes("Text Box 1").Select
Selection.Characters.Text = ""
..Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..Range("A12").Select
Sheet2.Select
[a1].Select
..Protect "led52not"

Exit Sub
Else
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
..Unprotect "led52not"
..Shapes("Text Box 1").Select
Selection.Characters.Text = ""
..Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..Range("A12").Select
Sheet2.Select

End With
End If
End With
End If
End Sub


Thanks so much,

Golf
 
T

Tom Ogilvy

Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
..Unprotect "led52not"
..Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..PrintOut
..Shapes("Text Box 1").Select
Selection.Characters.Text = ""
..Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..Range("A12").Select
Sheet2.Select
[a1].Select
..Protect "led52not"
End With
Exit Sub
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
..Unprotect "led52not"
..Shapes("Text Box 1").Select
Selection.Characters.Text = ""
..Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 2").Select
Selection.Characters.Text = ""
..Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..Range("A12").Select
Sheet2.Select

End With
End If

End Sub

--
Regards,
Tom Ogilvy

golf4 said:
AGAIN.... Midnight here and I can't seem to wrap my brain around the
problem I'm having with my code. I've posted it below and this is what
I'm trying to do. The code is designed to print out worksheets that
will calculate the prorated amount of rent while populating check
boxes. It worked fine until I tried adding the IF statements: IF H47
="X", then four copies of the worksheet will print out; IF H49 ="X",
then it should print out the three copies. The different cell
references represent two different programs. The problem is that my
code is now not working (with the two different IF statements). I have
a feeling that it has to do with the transition strip:

Exit Sub
Else
End If

...... but I can't seem to figure it out. Any help would be
fantastic!!!


Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select
[a1].Select
.Protect "led52not"

Exit Sub
Else
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select

End With
End If
End With
End If
End Sub


Thanks so much,

Golf
Click to expand...
 
H

Henry

Golf4,
Just to make things clearer for future reference.

In VBE (like most other programming languages) you MUST end For loops,
Withs, Ifs, etc in the reverse order to the order they were set up.

eg:
For A......
If J = 5 Then
For B.....
For C......
With XYZ
For K
Some code here
Next K 'Reverse order
End With 'Reverse order
Next C 'Reverse order
Next B 'Reverse order
End If 'Reverse order
Next A 'Reverse order

Otherwise you will not only confuse yourself, but the compiler as well.

For A....
For B....
some code here
Next A 'same order
Next B 'same order

will either not compile or, if it does, will give you some very strange
results.

HTH
Henry


Tom Ogilvy said:
Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select
[a1].Select
.Protect "led52not"
End With
Exit Sub
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select

End With
End If

End Sub

--
Regards,
Tom Ogilvy

golf4 said:
AGAIN.... Midnight here and I can't seem to wrap my brain around the
problem I'm having with my code. I've posted it below and this is what
I'm trying to do. The code is designed to print out worksheets that
will calculate the prorated amount of rent while populating check
boxes. It worked fine until I tried adding the IF statements: IF H47
="X", then four copies of the worksheet will print out; IF H49 ="X",
then it should print out the three copies. The different cell
references represent two different programs. The problem is that my
code is now not working (with the two different IF statements). I have
a feeling that it has to do with the transition strip:

Exit Sub
Else
End If

...... but I can't seem to figure it out. Any help would be
fantastic!!!


Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select
[a1].Select
.Protect "led52not"

Exit Sub
Else
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select

End With
End If
End With
End If
End Sub


Thanks so much,

Golf
Click to expand...
Click to expand...
 
G

golf4

Hi, Tom -

Thanks so much for the response. Your corrections of my code works
like a charm --- should really help out with further streamlining my
rent calculation tool.

I really appreciate the help.

Take care,

Golf



Tom Ogilvy said:
Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select
[a1].Select
.Protect "led52not"
End With
Exit Sub
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select

End With
End If

End Sub

--
Regards,
Tom Ogilvy

golf4 said:
AGAIN.... Midnight here and I can't seem to wrap my brain around the
problem I'm having with my code. I've posted it below and this is what
I'm trying to do. The code is designed to print out worksheets that
will calculate the prorated amount of rent while populating check
boxes. It worked fine until I tried adding the IF statements: IF H47
="X", then four copies of the worksheet will print out; IF H49 ="X",
then it should print out the three copies. The different cell
references represent two different programs. The problem is that my
code is now not working (with the two different IF statements). I have
a feeling that it has to do with the transition strip:

Exit Sub
Else
End If

...... but I can't seem to figure it out. Any help would be
fantastic!!!


Sub PrintProrateWorksheet() 'Print copies of Standard Prorate Sheets'

If Sheets("Data_Entry_Sheet").[H47] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = "P"
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.PrintOut
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select
[a1].Select
.Protect "led52not"

Exit Sub
Else
End If

If Sheets("Data_Entry_Sheet").[H49] = "X" Then

shtSProrate.Activate
With shtSProrate
.Unprotect "led52not"
.Shapes("Text Box 1").Select
Selection.Characters.Text = ""
.Shapes("Text Box 2").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 2").Select
Selection.Characters.Text = ""
.Shapes("Text Box 3").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Shapes("Text Box 4").Select
Selection.Characters.Text = ""
.Range("A12").Select
Sheet2.Select

End With
End If
End With
End If
End Sub


Thanks so much,

Golf
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

Maintaining Text Box Font Setting through VB 4
Changing the test of checkboxes (shapes) programmatically? 3
Query on Control Source in Userform Textbox 4
Problem with If,Then, Else 8
VBA code causes Find and Replace dialog box to lose focus 2
Copy Selected Text from Text Box 2
How to add text box with macro attached 1
Need Help Troubleshooting 2

Top