A userform may be better instead of having a ton of comboboxes.
Select the range in Ground Works. The Userform pops up, start typing in the combobox and it will autofill.
The codes are in the worksheet module and the userform module.
Worksheet Module code
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Count > 2 Then Exit Sub
If Not Application.Intersect(Target, Me.Range("A6:A11,A13:A1000")) Is Nothing Then
UserForm1.Show
End If
End Sub
UserForm Codes
Private Sub CommandButton1_Click()
ActiveCell.Value = Me.ComboBox1
Unload Me
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim sh As Worksheet, rng As Range
Set sh = Sheets("Materials")
With sh
Set rng = .Range("B7:B" & .Cells(.Rows.Count, "B").End(xlUp).Row)
Me.ComboBox1.List = rng.Value
End With
'-------------------------------
Me.Left = Application.Left + (0.5 * Application.Width) - (0.5 * Me.Width)
Me.top = Application.top + (0.5 * Application.Height) - (0.5 * Me.Height)
'-------------------------------
End Sub
See the attached.
Bookmarks