VBA to link cell to combobox

March 4, 2013 at 12:34:55
Specs: Windows XP
I have a Production sheet of sorts which uses countif's/indexes and so on by using the Validation List I currently have set up.
I'm trying to get away from Validation list mainly because of it's limitations...

I know how to create a combobox and how to link this to a cell but was hoping there would be some VBA to mass produce this over a couple 1,000 times...

See More: VBA to link cell to combobox

Report •

March 4, 2013 at 12:55:06
I assume by "mass produce this" you mean the creation of the combobox and linking to the cell, correct?

I don't do much with comboboxes, so I don't know if this will work, but have you tried recording a macro as you create the combobox and link it to cell, then placing a loop around the code that is produced to repeat the actions multiple times?

Try recording a macro and then post the code back here so I can see what is produced.

Please click on the following line and read the insructions found via that link before posting the code. Thanks!

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 4, 2013 at 13:40:08
That might work and I could try it but what if we start where I left off...

I have this code to copy+paste a combox and the link cell is automatically changed but still I have to do this one at a time and is still a bit tedious..

Here's a sample of this code:
Private Sub "Combox Name"_Change()
Dim cbo As OLEObject

If TypeOf cbo.Object is ComboBox Then
cbo.LinkedCell = cbo.TopLeftCell.Offset(, 1).Address
End If
End Sub

Report •

March 4, 2013 at 14:37:52
Well, first off, it doesn't appear that you honored my request to read the instructions on how to post code in this forum.

Why can't you put that Code inside a loop and have it create as many combo boxes as you want?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

Related Solutions

March 5, 2013 at 05:57:29
I'm not sure how I would go about looping the code I already have;
Also I tried as you originally suggested and recorded a Macro as I created a combobox;

The recorded Macro did create a combobox in the same location it was originally created but without any of the formatting, LinkedCell, or ListFillRange.

Do you have any other suggestions? or advise on how I might Loop the code I do have

Report •

Ask Question