3

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.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Krøllebølle
  • 2,878
  • 6
  • 54
  • 79

2 Answers2

14

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
varocarbas
  • 12,354
  • 4
  • 26
  • 37
  • @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... – Alfa Bravo Aug 30 '18 at 08:18
  • 1
    @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... – varocarbas Aug 31 '18 at 09:26
  • 1
    @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... – varocarbas Aug 31 '18 at 09:27
  • 1
    @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... – varocarbas Aug 31 '18 at 09:29
  • 2
    @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. – varocarbas Aug 31 '18 at 09:31
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.

Andy G
  • 19,232
  • 5
  • 47
  • 69