Trying to Replace a Combo Box with a List Box (Form Control) in Excel Worksheet

[nryan]

Hello,

I'm working with an excel worksheet with macros and there are several Combo Boxes (Form Control) on one worksheet. Most of these Combo Boxes have only 2 items to choose from so I thought it would be nicer to have List Boxes to replace them.

I started by trying to replace just one combo box with a list box. I added a Form Control List Box to the same worksheet (right underneath the combo box) and called it "listCxN". I also assigned it the same input range and the combo box it is replacing.

Here is the VBA code:

Original (using Combo Boxes):

Dim CN As DropDown
Set CN = Instructions.DropDowns("ddCxN")
Set r = Master.Range(CN.ListFillRange)

What I've changed it to (using List Boxes):

Dim CN As ListBox
Set CN = Instructions.DropDowns("listCxN")
Set r = Master.Range(CN.ListFillRange)

The error I get is Run-time error '1004': The item with the specified name wasn't found. The list box called "listCxN" is definitely there and is spelled correctly so I don't know how this error is happening. Do I need to do something in the workbook or worksheet that wouldn't be in the VBA Macro code? I tried [Right-Click] on List Box > "Assign Macro..." and chose the macro, but that didn't fix it.

Any help or insight would be great!

Thank you.

 

[Domenic]

Try...

Set CN = Instructions.[COLOR=#ff0000]ListBoxes[/COLOR]("listCxN")

Hope this helps!

 

[nryan]

Thanks Domenic. It worked!

 

[Domenic]

You're very welcome!