Hi Everyone, I'm fairly new to Excel VBA but I'm having a go at building a pricing model with vba user forms for selecting job materials etc. My cost_cat forms have 15 comboboxes for choosing items from a specific range. This is working ok but I want to avoid user duplication of items. Is it possible for combobox selections to be removed or hidden from the following comboboxes? My range is 3 columns but only column(1) (item description) populates the combobox list, whilst column(2) (cost code) and column(3) (pack type) populate an adjacent textbox and labelbox via the change routines for each combobox . Here is my no doubt, very basic code. Any help will be very much appreciated.
Code
Dim xRange As Range
Private Sub UserForm_Initialize()
Set xRange = Worksheets("Engine").Range("ListPlaster")
Me.ComboBox1.List = xRange.Columns(1).Value
Me.ComboBox2.List = xRange.Columns(1).Value
Me.ComboBox3.List = xRange.Columns(1).Value
Me.ComboBox4.List = xRange.Columns(1).Value
Me.ComboBox5.List = xRange.Columns(1).Value
Me.ComboBox6.List = xRange.Columns(1).Value
Me.ComboBox7.List = xRange.Columns(1).Value
Me.ComboBox8.List = xRange.Columns(1).Value
Me.ComboBox9.List = xRange.Columns(1).Value
Me.ComboBox10.List = xRange.Columns(1).Value
Me.ComboBox11.List = xRange.Columns(1).Value
Me.ComboBox12.List = xRange.Columns(1).Value
Me.ComboBox13.List = xRange.Columns(1).Value
Me.ComboBox14.List = xRange.Columns(1).Value
Me.ComboBox15.List = xRange.Columns(1).Value
End Sub
Private Sub ComboBox1_Change()
TextBox1.Text = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, _
xRange, 2, False)
LabelBox1.Caption = Application.WorksheetFunction.VLookup(Me.ComboBox1.Value, _
xRange, 3, False)
End Sub
Private Sub ComboBox2_Change()
TextBox2.Text = Application.WorksheetFunction.VLookup(Me.ComboBox2.Value, _
xRange, 2, False)
LabelBox2.Caption = Application.WorksheetFunction.VLookup(Me.ComboBox2.Value, _
xRange, 3, False)
End Sub
Private Sub ComboBox3_Change()
TextBox3.Text = Application.WorksheetFunction.VLookup(Me.ComboBox3.Value, _
xRange, 2, False)
LabelBox3.Caption = Application.WorksheetFunction.VLookup(Me.ComboBox3.Value, _
xRange, 3, False)
End Sub
'Etc for all comboboxes.
Display More