VBA Seachable Dropdown list on multiple rows from same list

[John37obc]

I have gotten as far as creating a dynamic searchable list however I want to be able to use it on several different rows using the same list.
The list I want to use is in Materials! B7:B1069 and for each of the sheets I want a combo box for every row, upto around 100 rows.
In a futuristic ideal world would like the prices to also copy across from this, but is way out of my depth and brain power.
Thanks for looking.

[davesexcel]

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.

[torachan]

Try the attached - first put a quantity into column 'A' (STAGE_ESTIMATE) sheet - double-click in 'B3' downwards - select from combobox (this filters out until last selection) then entry goes to sheet - enter as many selections under one stage heading and transfer to 'ESTIMATE' sheet.
This approach is far simpler than having multiple sheets with masses of formula and comboboxes to maintain (there are no on-sheet formula)