Solved

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

Posted on 2013-05-20
5
308 Views
Last Modified: 2013-05-20
vba excel 2013
Userform
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



Thanks
fordraiders
0
Comment
Question by:fordraiders
  • 3
  • 2
5 Comments
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
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.
0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
Because sometimes i need certain comboboxes in certain positions..
in certain sequences.

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

Accepted Solution

by:
Rory Archibald earned 500 total points
Comment Utility
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

0
 
LVL 3

Author Comment

by:fordraiders
Comment Utility
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"  &  _
& "MAGNETIC", "GRAY STEEL")

Thanks
fordraiders
0
 
LVL 3

Author Closing Comment

by:fordraiders
Comment Utility
Thanks very much
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Viewers will learn the basics of slicers and timelines for both PivotTables and standard Excel tables in Excel 2013.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now