Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


create a determined number of comboboxes at runtime on userform and fill each one seperately

Posted on 2013-05-20
Medium Priority
Last Modified: 2013-05-20
vba excel 2013
Mutipage tab control --specifically tab9

The following code generates a combobox at runtime on a userform and specifically tab9

Private Sub UserForm_Initialize()
Dim cbo As MSForms.ComboBox

    Set cbo = Me.MultiPage1.Pages(8).Controls.Add("Forms.Combobox.1")
    cbo.Left = 10
    cbo.Top = 10
End Sub With cbo
       .AddItem "Date"
       .AddItem "Player"
       .AddItem "Team"
       .AddItem "Goals"
       .AddItem "Number"
End With

Now what i need:

01---I need to be able to create and call up to 3 comboboxes.

02--- The comboboxes need to be aligned next to each other.

03-- I may or may not call all three at any given time.

combobox1 would have items:

With cbo1
       .AddItem "1/2 INCH"
       .AddItem "3/4 INCH"
       .AddItem "1 INCH"
       .AddItem "1 1/2 INCH"
       .AddItem "2 INCH"
End With

With cbo2
       .AddItem "ADAPTER"
       .AddItem "BRASS"
       .AddItem "GALVANIZED"
       .AddItem "STEEL"
       .AddItem "312 STEEL"
End With

With cbo3
       .AddItem "BOLT"
       .AddItem "HHCS"
       .AddItem "SHCS"
       .AddItem "SET SCREWS"
       .AddItem "LAG SCREW"
End With

Question by:fordraiders
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
LVL 85

Expert Comment

by:Rory Archibald
ID: 39181400
Why are you creating them at runtime? Why not create them at design time and set the visible property to False until such time as you want them visible.

Author Comment

ID: 39181427
Because sometimes i need certain comboboxes in certain positions..
in certain sequences.

i could just would like to have this as a backup plan.
LVL 85

Accepted Solution

Rory Archibald earned 2000 total points
ID: 39181592
OK - here's an example

Private Sub UserForm_Initialize()
   Dim cbo                    As MSForms.ComboBox
   Dim n                      As Long
   Dim NumControls            As Long
   Dim avItems(1 To 3)
   Dim dComboWidth            As Double

   NumControls = 2
   dComboWidth = 75

   avItems(1) = Array("Date", "Player", "Team", "Goals", "Number")
   avItems(2) = Array("1/2 INCH", "3/4 INCH", "1 INCH", "1 1/2 INCH", "2 INCH")
   avItems(3) = Array("ADAPTER", "BRASS", "GALVANIZED", "STEEL", "312 STEEL")

   For n = 1 To NumControls

      Set cbo = Me.MultiPage1.Pages(8).Controls.Add("Forms.Combobox.1", "cbo" & n, True)
      With cbo
         .Left = (n - 1) * dComboWidth + 10
         .Top = 10
         .Width = dComboWidth
         .List = avItems(n)
      End With
   Next n
End Sub

Open in new window


Author Comment

ID: 39181839
Thanks rorya,
just a quick side:

avItems(3) = Array("ADAPTER", "BRASS", "GALVANIZED", "STEEL", "312 STEEL" )

with the folowinf array.
If i need to carry Items to fill to a next line syntax for that ?

avItems(3) = Array("ADAPTER", "BRASS", "GALVANIZED", "STEEL", "312 STEEL"  &  _


Author Closing Comment

ID: 39181872
Thanks very much

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

670 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question