4

I am trying to create, place and fill in elements of a ComboBox in VBA. I don't understand what I am missing, but I cannot possibly figure out how to do it. I do not have MSForms in my API (i.e. I cannot write Dim someComboBox As MSForms.ComboBox as it fails. Is it possible to import it somehow? Can someone point me in the right direction?

What I want to achieve is that when the user clicks a button a new form is created with various items (ComboBoxes, RadioButtons, etc.) and thus I want to do this programmatically.

Improve this question

2 Answers 2

Reset to default
15

You can rely on the following code:

 Set curCombo = ActiveSheet.Shapes.AddFormControl(xlDropDown, Left:=Cells(1, 1).Left, Top:=Cells(2, 1).Top, Width:=100, Height:=20)
 With curCombo
        .ControlFormat.DropDownLines = 2
        .ControlFormat.AddItem "Item 1", 1
        .ControlFormat.AddItem "item 2", 2
        .Name = "myCombo"
        .OnAction = "myCombo_Change"
 End With
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

@varocarbas If I load this control on Workbook_Open, then how can I delete it when the workbook closes again? I have found that it created a control obviously every time the workbook opens, on top of the existing control. Which may be a problem after there are 500 controls on top of another...
@AlfaBravo A couple of basic ideas that you don't seem to have too clear. Please, don't get offended: 1. This/I is/am not a consultancy for you to get a personalised answer for whatever problem you might have (I get paid for that). It wasn't the case over 2 years ago when I wrote this answer and is certainly not the case now. The main idea here, in SO, is providing some help to the OP's question, ideally as generically as possible. In fact, this answer of mine might be considered way too specific for what was asked and for what is the expected behaviour of the answerers here...
@AlfaBravo... 2. What you are asking has absolutely nothing to do with either my answer/code or the original question. Actually (again without trying to offend you), it doesn't even seem a too sensible question in general for VBA or for any other language. Let me rephrase it to help you understand my point: "why do I have 500 things of whatever when what I do is creating them and not deleting them afterwards?". The answer seems clear, isn't it? What about trying one of the following proceedings: deleting them afterwards (e.g., in the book closing event?) or relying on an smarter...
@AlfaBravo... creation process, for example adding a new one only after having confirmed that none is present or simply not saving the changes to the file (i.e., add the shape, use it to perform whatever action and close the workbook afterwards without saving it). In summary, your question isn't about this question/answer or VBA, but what seem your problems to adequately understand the given situation as a required pre-step before thinking about what might be the best solution. Trick to never forget: actually typing the commands in whatever...
@AlfaBravo... programming language is usually the less important part of the software development process. You should focus most of your efforts on properly understanding the given situation and on having clear ideas regarding how to proceed. Even if you are a novice (in general or in that language), you should try to think carefully before start typing, copy/pasting, asking, etc.
0

You need a reference to Microsoft Forms 2.0 Object Library. An easy way to do this is to just insert a UserForm in your project.

Alternatively, find this reference in the Tools/ References dialog.

You can programmatically create a UserForm, a ComboBox, etc., but it would be easier to just insert a UserForm and leave it hidden until needed. You can programmatically add new controls to it still, if needed.

Added The Forms Object Library won't enable you to create entirely new forms and controls. You need the Microsoft Visual Basic for Applications Extensibility library. This link is old, but still relevant.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.