how to enable different macros based on combo box selection?

[juju]

I have a spreadsheet with 2 combo boxes - Combobox 1 enables a user to specify if they want to look at the data on the sheet by week or month - it has just 2 options - "select by week" or "select by month"

The list in combobox 2 changes based on the selection in combobox1 - so if you select "select by week" , a date list by week shows up.

Combobox 2 is supposed to run a macro which updates some backend pivot tables. there are 2 types of backend pivottable - weekly tables and monthly tables.

What i am not sure about is how to get the selections in combobox 2 to run either the weekly macro or the monthly macro based on if combobox1 is set to week or month ...?

 

[mikerickson]

If its a ComboBox from the Forms menu.

Select Case ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value
    Case Is = 1
        Call aRoutine
    Case Is = 2
        Call bRoutine
End Select

 

[juju]

Thanks for a speedy reply ..

quick clarification:

is it possible to tie in how my drop down list in combo box 2 is generated? So I have a compact code which calls routine a or b and also changes the list in combobox 2 based on whether case is 1 or 2?

I am using offset formulas to define the list ranges in combobox 1 and 2 ...

Thank very much ....

 

[juju]

I am geting a compile error with this line:

Select Case ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value

is it maybe related to the name of the sheet? the drop down box is on a tab called "Data Review". or maybe the name of the combo box? I see DropDown1_Change

If its a ComboBox from the Forms menu.

Select Case ActiveSheet.Shapes("Drop Down 1").ControlFormat.Value
    Case Is = 1
        Call aRoutine
    Case Is = 2
        Call bRoutine
End Select

 

[mikerickson]

Is your dropdown from the Forms menu or is it an ActiveX control from the toolbox?

 

[juju]

Is your dropdown from the Forms menu or is it an ActiveX control from the toolbox?

its from the forms menu

 

[mikerickson]

The format for getting the value of a Forms combobox is

MsgBox Workbooks("bookName.xls").Sheets("sheetName").Shapes("controlName").ControlFormat.Value

The workbook, sheet and control names should be adjusted to match your situation.

If you are trying to control the program flow based on two dropdown's entries, something like this should work.

Dim oneDD as Shape
Dim twoDD as Shape

Set oneDD = Workbooks("bookAleph.xls").Sheets("sheetAlpha").Shapes("Combo1"): Rem adjust
Set twoDD = Workbooks("bookBet.xls").Sheets("sheetBeta").Shapes("Combo2"):Rem adjust

Select Case 100*(oneDD.ControlFormat.Value) + (twoDD.ControlFormat.Value)
    Case Is = 101
        Rem first choice from 1DD, first from 2DD
        Rem some code
    Case Is = 102
        Rem first choice from 1DD, second from 2DD
        Rem some code
    Case Is = 201
        Rem second choice from 1DD, first from 2DD
        Rem some code
    Case Is = 202
        Rem second choice from 1DD, second from 2DD
        Rem some code
'etc.

End Select

 

[juju]

The format for getting the value of a Forms combobox is

MsgBox Workbooks("bookName.xls").Sheets("sheetName").Shapes("controlName").ControlFormat.Value

The workbook, sheet and control names should be adjusted to match your situation.

If you are trying to control the program flow based on two dropdown's entries, something like this should work.

Dim oneDD as Shape
Dim twoDD as Shape

Set oneDD = Workbooks("bookAleph.xls").Sheets("sheetAlpha").Shapes("Combo1"): Rem adjust
Set twoDD = Workbooks("bookBet.xls").Sheets("sheetBeta").Shapes("Combo2"):Rem adjust

Select Case 100*(oneDD.ControlFormat.Value) + (twoDD.ControlFormat.Value)
    Case Is = 101
        Rem first choice from 1DD, first from 2DD
        Rem some code
    Case Is = 102
        Rem first choice from 1DD, second from 2DD
        Rem some code
    Case Is = 201
        Rem second choice from 1DD, first from 2DD
        Rem some code
    Case Is = 202
        Rem second choice from 1DD, second from 2DD
        Rem some code
'etc.

End Select

Thank you - I am going to test it right now . for the part which says

"Rem first choice from 1DD, second from 2DD
Rem some code"

do i need to put that in literally, or change that to something?

 

[mikerickson]

Rem denotes that the line is a comment. (My old school eyes sometimes have trouble seeing an ')

It does not need to be in the code.

 

[juju]

I used this but I am getting an "invalid outside procedure" error

Dim oneDD As Shape
Dim twoDD As Shape

Set oneDD = Workbooks("Dashboard - Draft 4.xls").Sheets("Overview Dashboard").Shapes("Dbase1"): Rem adjust
Set twoDD = Workbooks("Dashboard - Draft 4.xls").Sheets("Overview Dashboard").Shapes("Dbase2"): Rem adjust

Select Case 100 * (oneDD.ControlFormat.Value) + (twoDD.ControlFormat.Value)
    Case Is = 101
        Rem first choice from 1DD, first from 2DD
        Rem some code
    Case Is = 102
        Rem first choice from 1DD, second from 2DD
        Rem some code
    Case Is = 201
        Rem second choice from 1DD, first from 2DD
        Rem some code
    Case Is = 202
        Rem second choice from 1DD, second from 2DD
        Rem some code
'etc.

End Select