VBA Question: Called macro stops unexpectedly

[dmbluv]

Hello,

I'm new to using VBA in Excel. I'm hoping I'm missing something
simple and that someone here can help me.

I have one Excel workbook that has many subroutines/macros in Module
1. I have 5 worksheets in the workbook and each has navigation
buttons to move from sheet to sheet. On one sheet, there are required
fields that must be checked before a user can be moved to the desired
sheet. The buttons call the appropriate macro, like GoTo_Report,
which takes the user to the Report sheet. But, on the Project Info
screen, I need all these buttons to also run the macro that checks
against null values in the required fields. I have that code in
ProjInfoReqFields.

ProjInfoReqFields has 8 IF statements. I want each of these 5 buttons
to call ProjInfoReqFields, have it run through all 8 IF statements,
then call the second macro to go to the appropriate sheet. If I copy
this code in to each of the 5 navigation buttons, it works great. It
goes through all 8 IF statements, then I have the final Else statement
calling the second macro. But, when I try to just call both macros,
as shown below, it will only get through the first IF statement, then
it continues to the second macro.

Button2_Click()

'Macro to check against null values on Project Info sheet
ProjInfoReqFields

'Macro to move user from active sheet to Report sheet
GoTo_Report

End Sub


I made the following modification to the macro, which made it
successfully run through all 8 IF statements, but either wouldn't call
the second macro or something else is awry because it ends up going to
the wrong sheet.

Button2_Click()

'Macro to check against null values on Project Info sheet
ProjInfoReqFields
Exit Sub

'Macro to move user from active sheet to Report sheet
GoTo_Report

End Sub

I appreciate any help.
Thanks!
Tina

 

[Don Guillett]

Might help if you post all of your code for comments

 

[dmbluv]

Don,

Unfortunately, my code is on a different system and I can't copy it
here. But, to give you an idea, I've typed up a quick example below:

Sub ProjInfoReqFields()

If Range("D9") = "" Then
Msgbox "This field is required."
Range("D9").Select
Else
If Range("D11") = "" Then
Msgbox "This field is required."
Range("D11").Select
Else
If (so on for another 6 cells)

For purposes of full-disclosure, or as much as I can, here is the code
I have to move the user to the Report sheet, which is called as the
second macro for Button2_Click:

Sub GoTo_Report()
'Moves user to Report screen
Sheets("Report").Select
Range("A1").Select
End Sub

When I call ProjInfoReqFields from another macro, such as
Button2_Click, it will display the message box for D9, and probably
selects D9 as well (too quick for me to tell), but then immediately
moves on to the second macro. See below:

Button2_Click()
'Macro to check against null values on Project Info sheet
ProjInfoReqFields
'Macro to move user from active sheet to Report sheet
GoTo_Report
End Sub

If I modify it to add Exit Sub after calling ProjInfoReqFields, it
does in fact cycle through all the IF statements, but then does not
run the second macro to take user to desired sheet; it actually takes
user to a sheet that is not called in any macro. (That's why I think
it's exiting the subroutine before running the second macro.) See
below:

Button2_Click()
'Macro to check against null values on Project Info sheet
ProjInfoReqFields
Exit Sub
'Macro to move user from active sheet to Report sheet
GoTo_Report
End Sub

Thanks for any guidance!
Tina

 

[Don Guillett]

Of course, if your field needs filling in you will need to run the sub AGAIN
to re-check. If all is OK, you are then taken to your other sheet.

Sub ProjInfoReqFields()
myarray = Array("d9", "d11", "d13", "d16")

For Each c In myarray
'MsgBox Range(c).Value
If Len(Application.Trim(Range(c))) < 1 Then
MsgBox "Cell " & Range(c).Address & " Not filled in"
Range(c).Select
Exit Sub
End If
Next c

Application.Goto Sheets("Report").Range("a1")
End Sub

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
Don,

Unfortunately, my code is on a different system and I can't copy it
here. But, to give you an idea, I've typed up a quick example below:

Sub ProjInfoReqFields()

If Range("D9") = "" Then
Msgbox "This field is required."
Range("D9").Select
Else
If Range("D11") = "" Then
Msgbox "This field is required."
Range("D11").Select
Else
If (so on for another 6 cells)

For purposes of full-disclosure, or as much as I can, here is the code
I have to move the user to the Report sheet, which is called as the
second macro for Button2_Click:

Sub GoTo_Report()
'Moves user to Report screen
Sheets("Report").Select
Range("A1").Select
End Sub

When I call ProjInfoReqFields from another macro, such as
Button2_Click, it will display the message box for D9, and probably
selects D9 as well (too quick for me to tell), but then immediately
moves on to the second macro. See below:

Button2_Click()
'Macro to check against null values on Project Info sheet
ProjInfoReqFields
'Macro to move user from active sheet to Report sheet
GoTo_Report
End Sub

If I modify it to add Exit Sub after calling ProjInfoReqFields, it
does in fact cycle through all the IF statements, but then does not
run the second macro to take user to desired sheet; it actually takes
user to a sheet that is not called in any macro. (That's why I think
it's exiting the subroutine before running the second macro.) See
below:

Button2_Click()
'Macro to check against null values on Project Info sheet
ProjInfoReqFields
Exit Sub
'Macro to move user from active sheet to Report sheet
GoTo_Report
End Sub

Thanks for any guidance!
Tina