Excel 2013 - Form Controls in VBA

[Rocky0201]

Hi,

Is it possible to create a ComboBox (Form Controls) on my active sheet in VBA code? Specifically, adding a ComboBox and then setting the ComboBox to point to a sheet and the list in the sheet to display the dropdown in the ComboBox.

Example:

Sheet1 is where I would add a ComboBox (width, height and position on sheet1).

Sheet2 is my list of Values in column A, Rows 1 through x

Point to Sheet2's list in the ComboBox that I added in Sheet1

In advance, thanks for your help.

 

[Rocky0201]

I found a means to add a Combo Box to my sheet.

Set wsSheet1 = ThisWorkbook.Worksheets("Converter")
With wsSheet1.Shapes.AddFormControl(xlDropDown, _
Left:=150, Top:=30, Width:=200, Height:=20)
.ControlFormat.DropDownLines = 10
End With

My question is--- If I were to do "other" things in my code and then wish to come back to this drop down, how can I determine the name of the drop down so that I can add items to the drop down list?

Thanks

 

[kevin.philips]

I found a means to add a Combo Box to my sheet.

Set wsSheet1 = ThisWorkbook.Worksheets("Converter")
With wsSheet1.Shapes.AddFormControl(xlDropDown, _
Left:=150, Top:=30, Width:=200, Height:=20)
.ControlFormat.DropDownLines = 10
End With

My question is--- If I were to do "other" things in my code and then wish to come back to this drop down, how can I determine the name of the drop down so that I can add items to the drop down list?

Thanks

The first time you add it in code it is called "Drop Down 1", you can reference that in the code immediately after it was created and update the name:

wssheet1.Shapes("Drop Down 1").Name = "MyCMB"

Is the state of the workbook destroyed on close ...? Will you recreate the combobox in code again....?

 

[Rocky0201]

Thank you Kevin for getting back to me.

When the workbook is closed, I will not preserve any combo box's that I create.

My design is to create a combo box and then update the box with drop down data. Once the user selects from the drop down data, I will then create another combo box with additional drop down data based off of the user selection.

My project:

When the user opens my Converter workbook, they have a button on the Converter Sheet which, when pressed, an Open File Dialog box appears. The user then selects which Excel Workbook they wish to open.

I then extrapolate all of the sheets in the workbook and, when I get the code running, will create a drop down list with the sheet names.

The user will then select which sheet in the opened workbook to process.

Once the user makes this selection, I will build another drop down with the column headers (row 1).

The user will then select which column that needs to be converted.

I will then perform the conversion, write the data back out to their workbook, save an close their workbook.

Hope this makes sense.

Thanks again Kevin.