Macro to create drop down menuin a cell

**Zeos6** · 05-09-2013, 01:06 PM

Hi,

I am trying to create a drop down menu in a specific cell on a sheet using a macro. The named list is on a hidden sheet. I am using the following code but it is giving me an error: "Application-defined or object-defined error. " #1004

Sub Add_Drop_Down_Menu_Cell(ByVal menuList As Range)
With ThisWorkbook.Sheets("Filtered Data").Range("B5").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=menuList"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End Sub

The error is being generated by the .Add part of the code. Can anyone please help me resolve this? Thank you.

**HSV** · 05-09-2013, 01:28 PM

Hello,

Your code works so good to me

Please Login or Register  to view this content.

**Zeos6** · 05-09-2013, 01:37 PM

I am not sure what you mean by your post Harry. In the code you posted menuList is not defined. I pass it as a parameter to the sub. I also get an error on the .Add part.

**HSV** · 05-09-2013, 01:43 PM

I have manually created in Menu → Insert → Names the menuList.

**Zeos6** · 05-09-2013, 01:50 PM

Hmmm. Then I a not sure why I am getting an error. I am running this from a macro.

**HSV** · 05-09-2013, 01:58 PM

Try this example.

**Zeos6** · 05-09-2013, 02:08 PM

Does it matter that the menuList comes from a sheet that is hidden?

**HSV** · 05-09-2013, 02:20 PM

If you've tried the file?

**Zeos6** · 05-09-2013, 02:22 PM

Thanks Harry. The sheet does not really run the macro; i.e. nothing happens. One issue I noticed is that menuList in you code is a named range. I need menuList to be a variable because I need to use a number of lists. That is why I pass it as a parameter.