Add an item to a combo box in VBA code from the active userform

[JeremyA1976]

Is there a way to add an item to an active userform by clicking on a label on that userform, which activates a userform to add the item? Yeah, I got a little dizzy explaining that. I have a userform that I have made to add metal drop to our inventory. When we cut a metal sheet down to fabrication blanks, we always have left over (drop) metal that we put in a rack for later use. In order to add the new drop to the inventory, I use this userform to enter Metal type, metal thickness, size and a tracking number. I also added a label near the "Metal Type" label so that when it is clicked, it pops up another userform so i can add a new material type through a submit button.

userform1 is the main "Drop Metal Addition" form
The metal type combobox is named "mt"
label8 is the "*Add a Metal Type" label that shows userform2
textbox1 is the box I use to add the new metal type from userform2 to "mt" combo box in userform1
image1 is the submit button on userform2

I currently have this code to fill out the combo boxes.

Is it possible to add an item to this code through userform2? If so, where would i start?

Option Explicit

Private Sub Label8_Click()
UserForm2.Show
End Sub


Private Sub UserForm_Initialize()
    With mt
        .AddItem "Galvanized"
        .AddItem "Galv. Paint Grip"
        .AddItem "Cold Rolled Steel"
        .AddItem "A-36 Hot Rolled"
        .AddItem "6061 Aluminum"
        .AddItem "5052 Aluminum"
        .AddItem "Aluminized"
        .AddItem "Checker Plate"
        .AddItem "304 Stainless 2b"
        .AddItem "304 Stainless [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4]#4[/URL]  Brushed"
        .AddItem "304 Stainless [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8]#8[/URL]  Mirrored"
        .AddItem "316 Stainless 2b"
        .AddItem "316 Stainless [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4]#4[/URL]  Brushed"
        .AddItem ""
    End With
    
    With MThk
        .AddItem "24ga"
        .AddItem "22ga"
        .AddItem "20ga"
        .AddItem "18ga"
        .AddItem "16ga"
        .AddItem "14ga"
        .AddItem "12ga"
        .AddItem "11ga"
        .AddItem "10ga"
        .AddItem "7ga"
        .AddItem "-"
        .AddItem "1/8 in"
        .AddItem "3/16 in"
        .AddItem "1/4 in"
        .AddItem "5/16 in"
        .AddItem "3/8 in"
        .AddItem "1/2 in"
        .AddItem "5/8 in"
        .AddItem "3/4 in"
        .AddItem "1 in"
        .AddItem "-"
        .AddItem ".032 in"
        .AddItem ".04 in"
        .AddItem ".05 in"
        .AddItem ".063 in"
        .AddItem ".08 in"
        .AddItem ".09 in"
        .AddItem ".1 in"
        .AddItem ".125 in"
        .AddItem ".160 in"
        .AddItem ".190 in"
        .AddItem ".250 in"
        .AddItem ""
    End With
    
End Sub

 

[dmt32]

Hi,
avoid hard coding your values to list box - to make the list dynamic, create your lists in a worksheet & read from there.
Your 2nd userform can then update the lists which in turn, can refresh them in main userform.

Add a worksheet & name it Lists

Place following code in standard module & run

Sub MakeLists()
    Dim wsLists As Worksheet
    Dim m As Variant
    
    Set wsLists = ThisWorkbook.Worksheets("lists")


        m = Array("Galvanized", _
         "Galv. Paint Grip", _
         "Cold Rolled Steel", _
         "A-36 Hot Rolled", _
         "6061 Aluminum", _
         "5052 Aluminum", _
         "Aluminized", _
         "Checker Plate", _
         "304 Stainless 2b", _
         "304 Stainless #4   Brushed", _
         "304 Stainless #8   Mirrored", _
         "316 Stainless 2b", _
         "316 Stainless #4   Brushed", _
         "")
         
         wsLists.Cells(1, 1).Resize(UBound(m), 1).Value = Application.Transpose(m)
         
       m = Array("24ga", "22ga", "20ga", "18ga", "16ga", "14ga", "12ga", "11ga", _
         "10ga", "7ga", "-", "1/8 in", "3/16 in", "1/4 in", "5/16 in", _
         "3/8 in", "1/2 in", "5/8 in", "3/4 in", "1 in", "-", ".032 in", _
         ".04 in", ".05 in", ".063 in", ".08 in", ".09 in", ".1 in", _
         ".125 in", ".160 in", ".190 in", ".250 in", "")
         
         wsLists.Cells(1, 2).Resize(UBound(m), 1).Value = Application.Transpose(m)


End Sub

Now update your Main UserForms Intialize event with following code

Private Sub UserForm_Initialize()
    Dim wsLists As Worksheet
    Dim TypeList As Variant, SizeList As Variant
    
    Set wsLists = ThisWorkbook.Worksheets("lists")
    With wsLists
        TypeList = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).Value2
        SizeList = .Range(.Range("B1"), .Range("B" & .Rows.Count).End(xlUp)).Value2
    End With
    
    MT.List = TypeList
    MThk.List = SizeList
End Sub

This should populate your listboxes from the worksheet.

Your 2nd userform can now be amended to write changes to Lists worksheet (and sort if required) & refresh main userform list.

Hope Helpful

Dave

 

[JeremyA1976]

Thanks Dave!
I was soo close. I had to change my sheet name to lists, and i did not include the sizelist because i included every possible thickness, so no need to add on that combo list, but good looking out! The only issue i have is the fact when i click on my label to bring up userform2, i can add a new material, but i have to close down userform1 in order to see the change. Is there a way to have userform2 initiate a refresh or reload on userform1 so i see it immediately without having to close down and re-click? here is my current code for your module:

Sub MakeLists()    Dim wsLists As Worksheet
    Dim m As Variant
    
    Set wsLists = ThisWorkbook.Worksheets("lists")




        m = Array("Galvanized", _
         "Galv. Paint Grip", _
         "Cold Rolled Steel", _
         "A-36 Hot Rolled", _
         "6061 Aluminum", _
         "5052 Aluminum", _
         "Aluminized", _
         "Checker Plate", _
         "304 Stainless 2b", _
         "304 Stainless [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4]#4[/URL]    Brushed", _
         "304 Stainless [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=8]#8[/URL]    Mirrored", _
         "316 Stainless 2b", _
         "316 Stainless [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=4]#4[/URL]    Brushed", _
         "")
         
         wsLists.Cells(1, 1).Resize(UBound(m), 1).Value = Application.Transpose(m)


End Sub

and for the main userform initialize....

[CODE]Option Explicit


Private Sub Label8_Click()
UserForm2.Show
End Sub


Private Sub UserForm_Initialize()
    Dim wsLists As Worksheet
    Dim TypeList As Variant
    
    Set wsLists = ThisWorkbook.Worksheets("lists")
    With wsLists
        TypeList = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).Value2
    End With
    
    mt.List = TypeList
    
End Sub

[/CODE]

 

[JeremyA1976]

i forgot to add my thickness, which i added. fyi. here is what the userform_initialize looks like:

Option Explicit

Private Sub Label8_Click()
UserForm2.Show
End Sub


Private Sub UserForm_Initialize()
    Dim wsLists As Worksheet
    Dim TypeList As Variant
    
    Set wsLists = ThisWorkbook.Worksheets("lists")
    With wsLists
        TypeList = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).Value2
    End With
    
    mt.List = TypeList
    
    With MThk
        .AddItem "24ga"
        .AddItem "22ga"
        .AddItem "20ga"
        .AddItem "18ga"
        .AddItem "16ga"
        .AddItem "14ga"
        .AddItem "12ga"
        .AddItem "11ga"
        .AddItem "10ga"
        .AddItem "7ga"
        .AddItem "-"
        .AddItem "1/8 in"
        .AddItem "3/16 in"
        .AddItem "1/4 in"
        .AddItem "5/16 in"
        .AddItem "3/8 in"
        .AddItem "1/2 in"
        .AddItem "5/8 in"
        .AddItem "3/4 in"
        .AddItem "1 in"
        .AddItem "-"
        .AddItem ".032 in"
        .AddItem ".04 in"
        .AddItem ".05 in"
        .AddItem ".063 in"
        .AddItem ".08 in"
        .AddItem ".09 in"
        .AddItem ".1 in"
        .AddItem ".125 in"
        .AddItem ".160 in"
        .AddItem ".190 in"
        .AddItem ".250 in"
        .AddItem ""
    End With
    
End Sub

Your code for the thickness was much easier, but i had already had them all typed out. lol.

 

[dmt32]

Hi,
sorry, don't really follow - you can add to the worksheet lists as required and should not need to hard code.

to resolve your issue of refreshing list in UserForm1 form Userform2 try this


Place following in STANDARD module

Sub AddLists(ByVal Form As Object)
    Dim wsLists As Worksheet
    Dim TypeList As Variant, SizeList As Variant
    
    Set wsLists = ThisWorkbook.Worksheets("lists")
    With wsLists
        TypeList = .Range(.Range("A1"), .Range("A" & .Rows.Count).End(xlUp)).Value2
        SizeList = .Range(.Range("B1"), .Range("B" & .Rows.Count).End(xlUp)).Value2
    End With
    
    Form.MT.List = TypeList
    Form.MThk.List = SizeList
End Sub

I have included the sizelist as I first posted but you should update if retaining your hard coding method.


Update UserForm1 Intialize event as follows

Private Sub UserForm_Initialize()
    AddLists Me
End Sub

from your update code in Userform2

Private Sub CommandButton1_Click()


' your update code


'refresh list
    AddLists UserForm1
End Sub

Dave

 

[JeremyA1976]

After further thought, I went ahead and took your advice on not hardcoding the thickness. I followed your direction to a T and it worked PERFECT! Thanks a ton for all your effort and focus on this!

 

[dmt32]

After further thought, I went ahead and took your advice on not hardcoding the thickness. I followed your direction to a T and it worked PERFECT! Thanks a ton for all your effort and focus on this!

Your welcome - glad solution worked ok for you.

Dave