Form Fields

  • Thread starter Thread starter Jeff Harbin
  • Start date Start date
J

Jeff Harbin

I am creating a data entry form that contains 2 controls: one is a combo
box and the other is a list box. I am populating the control box using
the .AddItem method (property?) during the Form.Initialize Event and
have successfully accomplished this.

The list box is intended to be a dynamic list based on whichever choice
is selected in the ComboBox. All the data is listed in 2 rows of a
single worksheet - PartsList!A:B. Column A contains the Customer Name
which would correspond with the value selected in the Combobox and
Column B contains the Part Number.

I know I could programmically build the list box by comparing the value
selected in the combobox with the value in Column A and then use the
..AddItem mechanism to add the value from Column B. And them loop
through the entire spreadsheet.

I was just wondering if there was an easier way.

Thanks...
 
I created a small userform with a listbox, a combobox, and two commandbuttons.

This is the code I had behind the userform:

Option Explicit
Private Sub ComboBox1_Change()

Dim myCell As Range
Dim myRng As Range

With Worksheets("PartsList")
Set myRng = .Range("a1", .Cells(.Rows.Count, "a").End(xlUp))
End With

Me.CommandButton2.Enabled = False

With Me.ListBox1
.Clear
If Me.ComboBox1.ListIndex < 0 Then
.Enabled = False
Else
.Enabled = True
For Each myCell In myRng.Cells
If LCase(myCell.Value) = LCase(Me.ComboBox1.Value) Then
.AddItem myCell.Offset(0, 1).Value
End If
Next myCell
End If
End With

End Sub
Private Sub CommandButton1_Click()
Unload Me
End Sub
Private Sub CommandButton2_Click()
Dim iCtr As Long

With Me.ListBox1
For iCtr = 0 To .ListCount - 1
If .Selected(iCtr) Then
MsgBox .List(iCtr)
End If
Next iCtr
End With

End Sub
Private Sub ListBox1_Change()
Dim iCtr As Long

Me.CommandButton2.Enabled = False
With Me.ListBox1
For iCtr = 0 To Me.ListBox1.ListCount - 1
If .Selected(iCtr) Then
Me.CommandButton2.Enabled = True
Exit For
End If
Next iCtr
End With
End Sub
Private Sub UserForm_Initialize()

With Me.ComboBox1
'just some test data
.AddItem "1A"
.AddItem "2A"
.AddItem "3A"
End With

With Me.CommandButton1
.Cancel = True
.Caption = "Cancel"
End With

With Me.CommandButton2
.Default = True
.Caption = "Ok"
.Enabled = False
End With

With Me.ListBox1
.ColumnCount = 1
.Enabled = False
.MultiSelect = fmMultiSelectMulti '???
End With

End Sub
 
Back
Top