Office hacks: the production of automatic display submenu drop-down menu

Drop-down menus are often used in Excel. Usually, only one level of menu is displayed after clicking the menu. If you want to click on this menu, the menu below it to display the contents of the corresponding sub-menu with this menu, the use of worksheet change event, offset function, match function composed of simple code, can be easily achieved (Figure 1).


First, the construction of submenus and their related content. If you enter a sub-menu in cell A1, enter the content that needs to be displayed after clicking on this sub-menu in A2:A7; enter another sub-menu in cell B1, enter the content related to this sub-menu in B2:B3; others, and so on (Figure 2).


Next, select the “Formulas” tab, click “Name Manager”, click “New” in the pop-up window, enter “Japanese” in the pop-up window, and select A2:A7 for “Reference Location”. In the pop-up window, type “Japanese” for “Name”, and select A2:A7 for “Reference Location”. New name “Korean”, “reference location” select B2:B3, and so on; new name “Temporary selection”, reference location select G1; new name “Extract”, reference location select I1:L1 (Figure 3).


Next, select cell G1, select the “Data” tab, click “Data Validation”, in the pop-up window of the “Settings” tab validation conditions allowed to select “Series”, select A1:D1 at the source, so that the cell in G1 to create a drop-down menu.

Select the “Development Tools” tab, click Visual Basic, in the editing window, click “Insert → Module”, in the right window, enter a few lines of code as shown (Figure 4).


Double-click Sheet1 and enter the following code in the right-hand edit window.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

Call ss

Application.EnableEvents = True

End Sub

Tip: If there is no “Development Tools” tab in the window, you can click “File → Options”, in the pop-up window, check the “Development Tools” on it.

Finally, then select cell G1, set its font, font size, etc., click “File → Save As” in the pop-up window, select the file type “Excel macro-enabled workbook (*.xlsm)”, to save it.

Leave a Comment