Excel VBA Userform option button starts a loop that searches worksheet and populates listbox on userform?

Question

I have a workbook that contains a few lookup sheets and a userform. The userform has 2 option buttons and a listbox on the form as well as a create new button. When you click on an option the listbox should populate with a specific list of items found on a lookup worksheet in the same workbook. When the user selects the item the want from the list box and click create new, a new worksheet with the same of the item is created. The lookup sheet is 2 columns on it, column A is an item code and column B is an item. The usercodes are specific to the type of item so an example would be:

1005 Cups
1006 Plates
2001 Towels
2003 Table Cloth

The sequence should go like this if possible, the user opens the form via a button on a worksheet. The form opens with a blank list box and two option buttons one labled Serving and another labled Setup. when a user selects for instance serving as an option, a loop searches all items on the lookup sheet for items starting with the number "1". It then puts these items on the listbox. When a user selects for instance cups and hits create new a new worksheet is created titled Cups. I would like to be able to add new items to the item list by typing a code and item name on my lookup sheet at the end of the current list. I would like if possible for the lookup list to take my item and sort it automatically by number so that the form will still work... Thank you!

Answer 1

Try these three event handlers. This example uses 'Lookup' as the sheet containing the codes and items. It assumes the Listbox is named 'ListBox1'.



Attach this code to the command button on your userform (change the Sub name to reflect the name of your command button):



Private Sub cboCreateNew_Click()

On Error GoTo errhandler

Sheets.Add

ActiveSheet.Name = Me.ListBox1.Value

Exit Sub

errhandler:

MsgBox "A worksheet already exists for " & UCase(Me.ListBox1.Value), vbCritical, "Duplicate Entry"

Application.DisplayAlerts = False

ActiveSheet.Delete

End Sub



Attach this code to the option button for 'Serving' (change the Sub name to reflect the name of your option button):



Private Sub optServing_Click()

Dim i, LastRow

LastRow = Sheets("Lookup").Range("A" & Rows. _

Count).End(xlUp).Row

Me.ListBox1.Clear

For i = 1 To LastRow

If Left(Sheets("Lookup").Cells(i, "A"), 1) = 1 Then

With Me.ListBox1

.AddItem Sheets("Lookup").Cells(i, "B").Value

End With

End If

Next

End Sub



Attach this code to the option button for 'SetUp' (change the Sub name to reflect the name of your option button):



Private Sub optSetup_Click()

Dim i, LastRow

LastRow = Sheets("Lookup").Range("A" & Rows. _

Count).End(xlUp).Row

Me.ListBox1.Clear

For i = 1 To LastRow

If Left(Sheets("Lookup").Cells(i, "A"), 1) = 2 Then

With Me.ListBox1

.AddItem Sheets("Lookup").Cells(i, "B").Value

End With

End If

Next

End Sub





Create a button on your Lookup sheet and attach this macro to it:



Sub AddNew_and_Sort()

AddCode = InputBox("Please enter the Code to add.", "New Code")

If AddCode = "" Then

Exit Sub

End If

AddItem = InputBox("Please enter the Item to add.", "New Item")

If AddItem = "" Then

Exit Sub

End If

Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Value = AddCode

Range("A" & Rows.Count).End(xlUp).Offset(0, 1).Value = AddItem

Columns("A:B").Select

Selection.Sort Key1:=Range("A1"), _

Order1:=xlAscending, Header:=xlGuess, _

OrderCustom:=1, MatchCase:=False, _

Orientation:=xlTopToBottom, _

DataOption1:=xlSortNormal

Range("A1").Select

End Sub





The above macro will query for a new code and item name, add it to the table, then sort the table ascending based on the codes.

Answer 2

1

Answer 3

Excel Vba Userform Listbox