Copying and Pasting Multiple Combo Boxes

[wootthepants]

Hello!
I was wondering if someone could help me with the following (please keep in mind that I'm a bit new to VBA):

I have a spreadsheet where I want to create one combobox per cell so the user can type in a keyword selection, pick from the drop down, which will return results and auto populate into other fields. I've pretty much figure out how to do most of this but, since my sheet could possibly have several hundred rows, how do you copy the original combo box multiple times, with the LinkedCell reference automatically changing to the next cell (B3 to B4 etc) without having to do this manually one by one?

I found the following code which is supposed to do this exact thing except it seems to just move the original combo box rather than make copies. It also changes the cell reference to be one more than it should (combobox is hovering over B3 but the LinkedCell is B4).

Essentially, I have a combo box hovering over cell B2 and linked to cell B2. I want the code to copy that combo box, past the copy immediately below the original box (hovering over B3), and change the LinkedCell to B3. Then I want this repeated for however many copies I want to make (I've noted 10 for testing purposes here). Oh and a data validation drop down won't work as I need the combo box to auto fill in real time and to display values from more than one column. Thank you!!


Sub AddFormsComboBoxes()
'assumes 1 combo box named 'Drop Down 1' has
'been placed on the sheet and set up with
'the ListFillRange information so all that is
'needed to do is copy that control and
'change the Link cell address
'
'we can control everything needed for the
'process here
'these describe the LinkedCell address
'for the first/source control
Const sourceControlName = "ComboBox1" ' change as required
Const linkCellCol = "B"
Const firstLinkRow = 2 ' row for original control
'control how many copies to make
Const copiesToMake = 10 ' original + 99 = 100
'you can make this a positive number to
'add spacing between the new controls
'as set to 0 the controls will be
'placed very tightly on the sheet
Const vSpaceBetweenControls = 0
'variables needed to perform the copying and positioning
Dim leftPosition As Single
Dim topPosition As Single
Dim ctlHeight As Single
Dim linkCellRow As Long
Dim LC As Long

ActiveSheet.Shapes.Range(Array(sourceControlName)).Select
leftPosition = Selection.Left
topPosition = Selection.Top
ctlHeight = Selection.Height
linkCellRow = firstLinkRow
Application.ScreenUpdating = False ' speeds up process
Selection.Copy
For LC = 1 To copiesToMake
topPosition = topPosition + ctlHeight + vSpaceBetweenControls
linkCellRow = linkCellRow + 1
ActiveSheet.Paste ' new control becomes selected
With Selection
.Top = topPosition
.Left = leftPosition ' aligned vertically
.LinkedCell = linkCellCol & linkCellRow
End With
Next

End Sub

 

[smozgur]

Hello wootthepants,

Welcome to the MrExcel Board!

Actually I can see that your code is working without problems, and changing the LinkedCell property successfully.

However, I would use Duplicate method instead of Copy.
Again, the existing code already works, but just as an alternative with less code may be:

This code is duplicating the control, and using the same object variable (ddControl) to keep the reference, setting top and left properties of the new control, and setting the LinkedCell by using the duplicated value as reference (sht.Range(ddControl.LinkedCell).Offset(1).Address).

Sub doIt()
Dim sht As Worksheet
Dim ddControl As DropDown
Dim intLeft As Integer
Dim i As Integer
    Set sht = ActiveSheet
    Set ddControl = sht.DropDowns("ComboBox1")
    intLeft = ddControl.Left
    For i = 1 To 10
        Set ddControl = ddControl.Duplicate
        With ddControl
            .Top = .TopLeftCell.Top + .TopLeftCell.Height
            .Left = intLeft
            .LinkedCell = sht.Range(ddControl.LinkedCell).Offset(1).Address
        End With
    Next i
End Sub

 

[wootthepants]

Thank you! However, I gave it a try and it's error-ing with:

Method 'DropDowns' of object'_Worksheet' failed

I'm not sure what I need to change to fix it.

Thanks!
Allison

 

[smozgur]

It means you are not actually using DropDown object. So there is no dropdown in the worksheet and the error complains about it.

Could you please make your object selected in the worksheet, and goto VBA, open immediate (debug) window (Ctrl + G), type the following and tell us the object type name that will be printed below the command line?

print typename(selection)

Edit: I think you are actually using ComboBox form control. I am currently on Mac, so I can't revise my code for that but likely in a few hours. However, I think it won't be too much different than your original code since ComboBox doesn't have Duplicate method as I can remember. But I'll still check it when I have chance.

Edit2: Wait a second - original code has been written for DropDown, just like mine. So it could be the reason why it doesn't work as you need when it is used with a form control instead. You can give both a try with DropDown instead ComboBox, they will definitely work. However, I'll still check it for you.

 

[wootthepants]

Thank you so much for all the help!!

I'm using the ActiveX control ComboBox if that helps at all? I've mostly just been frankensteining together something based on tutorials I've found online. Is it possible to see a code that works for this form control? Or should I be using DropDown? How do I find DropDown? Sorry for all the dumb questions..

 

[smozgur]

Right, ActiveX control is what you are using.

If you are not using it for a specific reason, then you really might want to stick with Worksheet Form controls instead ActiveX controls. You can find those controls in Developer tab on the Ribbon (if it is hidden, then Excel Options, Customize Ribbon, check Developer option in the Customize Ribbon listbox under Main Tabs).

Otherwise, as I said, I would (or someone else if I can't make it in time) revise the code to work with ComboBox when I have chance to do that in Office Windows in a couple hours. See, this might be another reason to stick with Worksheet Form Controls instead ActiveX - portability.

 

[DanteAmor]

Hi ★ wootthepants, welcome to the forum.


First, it is not advisable to have hundreds of comboboxes on your sheet.


You can have DropDown List with Apply data validation to cells:


https://support.office.com/en-us/ar...to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249


Or you can have only one combo and with code it directs the contents of the combo to the cell that you require.


Or you can also create a UserForm with a single combobox and with code in the userform to address the combo data to the cell you need.


But I recommend the first option with data validation.

 

[wootthepants]

Hi ★ wootthepants, welcome to the forum.


First, it is not advisable to have hundreds of comboboxes on your sheet.


You can have DropDown List with Apply data validation to cells:


https://support.office.com/en-us/ar...to-cells-29fecbcc-d1b9-42c1-9d76-eff3ce5f7249


Or you can have only one combo and with code it directs the contents of the combo to the cell that you require.


Or you can also create a UserForm with a single combobox and with code in the userform to address the combo data to the cell you need.


But I recommend the first option with data validation.

Is it possible to use Data Validation to display data from multiple columns in the drop down? (for example I have a column for product code, one for description, and one for harmonized tariff code. I need all three displayed in the drop down)

Essentially I'm trying to create a form that will mimic transcribing a commercial invoice to excel where the user enters a keyword into the description field which pulls up a drop down list of possible options to select from (based on a reference table). Then on the next row, the user enters a different keyword and selects a different option. Ideally once an option is selected, it auto-populates other cells in that row based on the description selected from the drop down. A user could be entering over 100 rows of data.

Any insight would be greatly appreciated!

 

[wootthepants]

Hi smozgur,

I think the idea behind the ActiveX control was so that when a user typed a keyword in, possible options would immediately start popping up below it similar to how google search bars work. Although each time I try and test that, excel stops responding so it might not even work. You can see my reply back to DanteAmor with my end goal for this workbook. I might not even be going in the right direction with this. Any help is much appreciated.

 

[DanteAmor]

Is it possible to use Data Validation to display data from multiple columns in the drop down? (for example I have a column for product code, one for description, and one for harmonized tariff code. I need all three displayed in the drop down)

Essentially I'm trying to create a form that will mimic transcribing a commercial invoice to excel where the user enters a keyword into the description field which pulls up a drop down list of possible options to select from (based on a reference table). Then on the next row, the user enters a different keyword and selects a different option. Ideally once an option is selected, it auto-populates other cells in that row based on the description selected from the drop down. A user could be entering over 100 rows of data.

Any insight would be greatly appreciated!

I understand, if you want to capture a word and the possible options appear, then it must be with a combobox.
You could upload a file with sample data and I'll prepare an example with a userform.

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.