Nesting multiple subs with If...Then statements

T

tony_bender

I have a menu system where users can access numerous reports located on
a share drive. The menu is a single UserForm with 3 ListBoxes and 2
OptionButtons. The user makes one geographic zone selection from
ListBox1 that opens and populates ListBox2 with a specific group of
cities. After they make one selection in ListBox2 it opens ListBox3
that has a list of 4 specific reports (Excel files). After they make
their report selection(s) two OptionButtons appear where the user must
select one time period; 12 weeks or 52 weeks.
The results of each of these controls appear on a separate worksheet
called 'inputs'.

When the user makes the final selection from one of the two
OptionButtons it triggers a macro that is supposed to retrieve each of
the selected reports. The problem I'm having is it only retrieves
the first report selected report. Here is the macro that is called up
when the user selects the 12 week OptionButton:

(The code for the requested market falls into cells H10, I10, J10 or
K10 dependant upon which 12 week report(s) they want.)


Sub Check12WkCodes()
Application.ScreenUpdating = False

Sheets("inputs").Select

If Range("H10") <> "" Then GetVS12Report
If Range("I10") <> "" Then GetCR12Report
If Range("J10") <> "" Then GetBR12Report
If Range("K10") <> "" Then GetSR12Report

End Sub

Here is some of the code for the Brand Rank report:

Sub GetBR12Report()
'***These are the brand ranks

Application.ScreenUpdating = False

Sheets("Inputs").Select
Dim BR12 As Range
Set BR12 = Range("J10")

If BR12 = "" Then Exit Sub

'*******NATIONAL Selections...
If BR12 = 1 Then TUS_FOOD
If BR12 = 2 Then TUS_DRUG
If BR12 = 3 Then TUS_FOOD_DRUG
If BR12 = 4 Then TUS_FD_DRG_LIQ

'*****This continues to cover over 200 different geographic selections

End Sub

I have similar code for the remaining 12 week reports. I also have a
similar set of macros covering the 52week selections.

When I began building this the 'GetReport' macros were not
'If...Then' format, they were just one single Excel file. And when
I'd select more than one report from ListBox3, they all appeared.
But when I expanded the 'GetReport' macros to include all the
'If...then' statements it only retrieves the first selection.

I suspect my problem is with the 'Check12WkCodes' macro and I might
have to do something with the 4 'If...Then' statements in this
macro, but I have no idea.

I appreciate any and all help.

TIA
 

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

Top