Insert combo box by macro?

[celfyn]

Hi,

I'd like to use a button to run a macro that inserts a combo box, but can't seem to change the properties of that box within the macro. For example, I need to enter the linked cell, but when I run the macro, the actions in the combo box's properties dialogue don't record (I'm not familar with visual basic, so I'm 'recording' the macro).

Any help would be appreciated,

Thanks

[pike]

Hi celfyn
the recorded macro worked for me?

Please Login or Register  to view this content.

[celfyn]

Thanks for your response,

This is what I'm doing:-

record new macro>enter design mode>draw combo box>combo box properties>enter P0 as linked cell>close properties>exit deign mode>stop recording macro

and this is what I get

Sub test()
'
' test Macro
' Macro recorded 15/01/2010 by cd10
'

'
ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=885, Top:=90.8823529411765, Width:= _
111.176470588235, Height:=34.4117647058824).Select
End Sub

Any ideas where I'm going wrong?

[pike]

Hi celfyn
can yoy use the other forms combobox or the shown macro

[celfyn]

pike,

I tried that, it does work, but there doesn't seem to be as much in the way of properties, e.g. background colour etc?
Also that gives me numerical results in the linked cell rather than whats in my listfillrange.

[pike]

Hi celfyn
why does it have to be created in run time?

[romperstomper]

You can add the ActiveX version in code and set its properties, but I recommend using the Forms version (as per Pike's code) unless you really need the added properties.
You cannot however record setting the properties - if you can tell us what you want set, we can adjust your code accordingly if you like?

[celfyn]

Perhaps if I try to explain what I'm trying to do you can tell me if I'm being over ambitious a) in what excel can do and b) in what my lack of ability can achieve.

I have two worksheets in one work book, the first has rows 6 to 12, cell A6 is to be populated by a dropdown box whos input range is located on worksheet 2 (I also need to be able to type into the dropdown box)
Cell A7 also has its own similar dropdown box and so on until I get to cell A12

What I would like is to assign a macro to a button that inserts another row (13) and then creates another dropdown box with all its properties, list fill range, sizes, linked cell etc in place so that the user who presses the button just gets another row with dropdown box that he can use straight away.

I've got as far as creating the button and macro that inserts the row and dropdown box, but can't get those properties.

Is this beyond excel or is it too complicated to explain to someone of my limited ability?

Thanks again for your responses.

[romperstomper]

Is there a reason for not simply using the Data Validation option set to List with an in-cell dropdown?

[celfyn]

Would that mean that I would have to have my input range in the same worksheet?
That would give me a bit of a headache, not insurmountable but if there was another solution I'd be a lot happier.

[romperstomper]

No - you just need to define a name that points at that range, and then use that name as the source in the data validation dialog:
=MyList
for example

[celfyn]

OK, thanks very much for your help, I'll go and look up how to do that so that I don't take up any more of your time.

[romperstomper]

It's Insert-Name-Define in Excel 2003 and prior or use the Name manager on the Formulas tab in 2007.

[celfyn]

Just tried your suggestion, works great

Thanks again all who helped.

Celfyn