Computers, Howto

Howto: Change a menu in excel based on user input from another menu

Let us say there are three types of products:
Cakes, Drinks and Chocolates

Let us say each product type has varying number of sub-types:

  • Two different types of cakes: Cake A, Cake B
  • Four different types of drinks : Drink A, Drink B, Drink C, Drink D
  • Three different types of Chocolates : Chocolate A, Chocolate B, Chocolate C

Let us say we need to give the user two comboboxes, one to select the product type and one to select the product sub-type. In such a case,  the choices and number of choices displayed in the sub-type combobox will depend on the users choice of product type.

Macros can be used change one combobox based on the selection in another.

Let us start by setting up the first combobox.

  • Type values Cakes, Drinks and Chocolates in cells H4:H6.
  • Draw a combobox whose name is say Drop Down1
  • Right click the combobox. Select Format Control. Set the combobox to take inputs from range H4:H6 and return selection to H.

Let us next create the second combobox whose name is Drop Down 2. This combobox is still empty. To fill up this combobox we need four macros.

  • First macro menu_cakes fills the combobox if the user selects cakes.
  • Second macro menu_drinks fills the combobox if the user selects  drinks.
  • Third macro menu_chocolates fills the combobox if the user selects chocolates.
  • Fourth macro menu_product_selector decides which of these macros should be used depending on users selection.

Let us start creating these macros in the VBA editor.

  • Start the editor by pressing (Alt+F11).
  • Select Insert – > Module
  • Type in the code for the macros

Sub menu_cakes()
ActiveSheet.Shapes(“Drop Down 2”).Select
With Selection
.ListFillRange = “$J$4:$J$5”
.LinkedCell = “$J$2”
.DropDownLines = 2
.Display3DShading = False
End With
Range(“A1”).Select
End Sub

Sub menu_drinks()
ActiveSheet.Shapes(“Drop Down 2”).Select
With Selection
.ListFillRange = “$K$4:$K$7”
.LinkedCell = “$K$2”
.DropDownLines = 4
.Display3DShading = False
End With
Range(“A1”).Select
End Sub

Sub menu_chocolates()
ActiveSheet.Shapes(“Drop Down 2”).Select
With Selection
.ListFillRange = “$L$4:$L$5”
.LinkedCell = “$L$2”
.DropDownLines = 3
.Display3DShading = False
End With
Range(“A1”).Select
End Sub

Sub menu_product_selector()
Dim n As Integer
n = Range(“H2”).Value
‘This is where the user input is taken into account
Select Case n
Case 1
Call menu_cakes
Case 2
Call menu_drinks
Case 3
Call menu_chocolates
Case Else
MsgBox “Error is selecting product from Menu”
End Select
End Sub

The last part is to tie-all these macros for execution.

We do this by right clicking on the product type combobox (Drop Down 1), selecting Assign Macro and asking it to run the macro menu_product_selector. This way every time the user makes a selection on this menu, it will invoke the macro menu_product_selector which in turn will invoke other macros that control the second combobox.

One assumption made is that all combobox objects will be on the activesheet. It is not a bad assumption to make as the user would definitely be present on the worksheet containing the comboboxes to make a selection.

Is there a better way of doing this?

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s