# [SOLVED] Create Excel Drop-down list- FROM VBA / Macro (not conventional way)



## Neo_5000 (Nov 17, 2009)

Hi all, I would like to know if its possible to create a drop down list in Excel with a MACRO without selecting values from cells within the worksheet, in other words, I dont want microsoft's standard option but i want the list itself to come from within the macro or VBA coding.
This is because I already have hidden & locked cells within the sheet and to keep it simple, dont want to interfere with the actual data of the worksheet.

Any help will be greatly appreciated...


----------



## macropod (Apr 11, 2008)

*Re: Create Excel Drop-down list- FROM VBA / Macro (not conventional way)*

Hi Neo,

The data to populate the dropdown can come from any worksheet (including a hidden one), another workbook, a text file, etc, or it can be hard-coded into a text string or an array. The choice of method is up to you.


----------



## Neo_5000 (Nov 17, 2009)

*Re: Create Excel Drop-down list- FROM VBA / Macro (not conventional way)*

Hi macropod, the option i'm looking for is having it hardcoded within the macro, (i dont want additional data within cells). 
The problem is I am a bit unfamilar with coding macros, such as the syntax of vba etc.

Could you or anyone else give me an example of the coding required? for eg for cell D2 i want a drop down list for options 1-6...

I also want a similar option for a date drop-down with only 12 options being the 1st of every month eg "20111101" the format being 1st nov 2011.

Thanks in advance!!


----------



## RSpecianjr (Jan 20, 2010)

*Re: Create Excel Drop-down list- FROM VBA / Macro (not conventional way)*

Hey Neo 5000,

I guess what Macropod was getting at, is that it isn't necessary to have a macro for this. You can use Data Validation. Particularly if it is static information. Is there some reason you NEED a macro?

Are you specifically wanting a pop up window to come up? If that is the case, you will use a custom form instead of a macro.

Regards,

Robert D. Specian Jr.


----------



## Neo_5000 (Nov 17, 2009)

*Re: Create Excel Drop-down list- FROM VBA / Macro (not conventional way)*

Well the thing is that I really dont want to enter information within the excel sheet itself as there are already hidden cells etc, the sheet itself is uploaded to a intranet etc....so I dont want to mess with the content of the file itself.

oh, and no a pop-up wont be necessary, just a drop down list would suffice


----------



## RSpecianjr (Jan 20, 2010)

*Re: Create Excel Drop-down list- FROM VBA / Macro (not conventional way)*

I would recommend using data validation on a different worksheet then. Sounds like you are going to have multiple people accessing the file. If you used a macro, you will have to make sure to have a certificate that validates the macro or train the people using the workbook on how to allow macros. For a small group, this isn't really an issue.

As far as code goes for a macro, we need more specifics. Are you wanting to use an active x combo box or something? When you have the developer tab open, click insert > Active X Controls > Combo Box. When in design mode, right click the Combo Box and click Properties. You will sill the list of properties, for a range of cells, select ListFillRange and enter the cell range.

Hope this helps,

Robert D. Specian Jr.


----------



## Neo_5000 (Nov 17, 2009)

*Re: Create Excel Drop-down list- FROM VBA / Macro (not conventional way)*

ok...thanks for the active X control suggestion and on having the validation reference another worksheet, that still seems to overcomplicating things for the users who woudl be using the files. I wanted to keep it simple by using the macro and hard-coding it...


----------



## RSpecianjr (Jan 20, 2010)

*Re: Create Excel Drop-down list- FROM VBA / Macro (not conventional way)*

Hey Neo,

Can you explain what you are trying to do? All you've said is you want a dropdown list using a macro... what is the purpose of the dropdown? What are you trying to accomplish? 

The reason i mentioned having the validation list on the other worksheet is because you are saying you don't want to put anything on the worksheet in question. The validation can go anywhere. Again, we need to know a bit more of what you are trying to accomplish.

Regards,

Robert D. Specian Jr.


----------



## macropod (Apr 11, 2008)

*Re: Create Excel Drop-down list- FROM VBA / Macro (not conventional way)*

Hi Neo,

If you create a new worksheet in the workbook in question, you can both get the dropdown data from that and hide the worksheet without the users seeing any difference in the remainder of the workbook's structure. This has the significant advantage over a macro-solution in that your users won't have to explicitly permit macros to run in order for the dropdown to work.


----------



## Neo_5000 (Nov 17, 2009)

*Re: Create Excel Drop-down list- FROM VBA / Macro (not conventional way)*

alright fine, it isnt worth the hassle, thanks for you suggestions guys.


----------



## KenMacro (Jan 6, 2012)

I recorded a macro and this is the code for dropdown. I tested it and it works fine in Excel 2007 on my laptop. The red text needs to be changed if your list is in a different location.

Sub DropDownList()
'
' DropDownList Macro
' Data validation dynamic drop down list
'
' Keyboard Shortcut: Ctrl+d
'
With Selection.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="=Sheet2!A2:A10"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub


----------

