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

Posted on 2013-05-20
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 500 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: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

749 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