#Making drop down menus in excel for mac 2011 how to#
#Making drop down menus in excel for mac 2011 code#

#Making drop down menus in excel for mac 2011 how to#

In Excel, adding drop down list may help us to solve our work efficiently and easily, but, have you ever tried to create drop down list with hyperlinks, when you choose the URL address from the drop down list, it will be open the hyperlink automatically? This article, I will talk about how to create drop down list with activated hyperlinks in Excel.Д¬reate drop down list with hyperlinks by using formulaД¬reate drop down list with hyperlinks by using combo box

#Making drop down menus in excel for mac 2011 code#

Go to the Module Code window and past the below code (predefined user defined function).How to create drop down list with hyperlinks in Excel? Change the module name to mdlDynamicDropDown as shown in below image.(optional step)ДЎ1. To add a blank module, click on Insert menu then select Module.ДЎ0. LetвЂ™s add a blank module in our project to write the function. Now, we are done with designing the UserForm with 4 level of drop-down fields. Design the form as shown in below image with properties:Д©. Click on Insert menu then select UserForm to add a new form in our project.Д©. In VBA window, we need to add a UserForm. Alternatively, you can also press ALT + F11 to jump to Visual Basic Application window. To move to the VBA window, you just need to click on Developer Tab and then click on Visual Basic button available in Code Group (very first button from left in Developer tab ribbon). LetвЂ™s move to the VBA window to create a User Form, 4 level of combo boxes and utilize Custom VBA function to make it dynamic and dependent drop-down lists. Now, we are done with worksheets structures, command button and master table. This sheet will be managed and used by VBA itself to create temporary drop-down lists for combo boxes, lists and other controls as data selection by user.Д§. Here, we are not going to do anything in вЂDrop DownвЂ™ worksheet. Once you done with creating Master Table data then time to move on to вЂDrop DownвЂ™ sheet. If you want to use this to learn then you just need to copy and paste it in your master sheet (or other sheet which is dedicated for master drop-down data). We will be using below master table in this demo. Please see the below image to understand the table format and requirement. Category, Sub Category, Item and Sub Item. Here, I am going to create 4 level of drop-down fields with generic name e.g. This table will be a simple table having all the columns which are required for the drop-down.ДЇor example, if you have three level of drop-down fields in your user form then you will create three columns table and so on. In Master Sheet, we need to create a table for the drop-down fields.

LetвЂ™s create a command button with name вЂ Show My FormвЂ in Home sheet.ДҐ. In this demo, I am going to rename вЂ Sheet1вЂ to вЂ HomeвЂ, вЂ Sheet2вЂ to вЂ Master SheetвЂ and вЂ Sheet3вЂ to вЂ Drop DownвЂ.Д¤. You may use any name for all these sheets as per your project requirement. We will utilize Sheet2 to keep the master table for drop-down and Sheet3 will be for the VBA to create dynamic drop-down lists basis on data selections by user. Here, we will be using Sheet1 to keep a command button so that user can click to launch the User Form. Now Rename all the worksheets as per our requirement. Save this file with the name вЂDynamic Drop DownвЂ™ having Macro enabled extension (.XLSM).ДЈ.

Create a new Excel file and add three worksheets in thatДў.

LetвЂ™s create an Excel file and use the function to understand how it works.

Function will manage all the activities itself. You just need to pass some parameters in that function and itвЂ™s done. To solve this issue, we have developed a Custom VBA function which can be utilized in a very simple way and that function will create auto expanding, multi-level truly dynamic dependent drop-down lists for combo box, list box or cell base lists. All these steps are complex and are prone to errors. To solve this programming requirement, we create or use multiple tables or looping statement for dependent drop-down. Whenever we develop any automated tools/trackers in Excel VBA, we may face some scenarios where we need to create multilevel cascading drop-down fields.