Solved

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

Posted on 2013-05-20
5
346 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
[X]
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
5 Comments
 
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.
0
 
LVL 3

Author Comment

by:fordraiders
ID: 39181427
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
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

0
 
LVL 3

Author Comment

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

Thanks
fordraiders
0
 
LVL 3

Author Closing Comment

by:fordraiders
ID: 39181872
Thanks very much
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Suggested Solutions

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

759 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