Hi all,

I have some code which i have tested and is working great, but i would like to change it slightly so that i can input a file name so it picks the correct .xls workbook.

in my code at the minute it is looking for "sample_BOM" i would like to be able to enter the file name manual every time, i have commented the code so you can see where it is. 

Private Sub OptionButton1_Change()
If Me.OptionButton1.Value = True Then
Me.ComboBox1.List = Array("POWER", "CONTROL")
ElseIf Me.OptionButton2.Value = True Then
Me.ComboBox1.List = Array("01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "CS")
End If
End Sub
Private Sub UserForm_Initialize2()
    Dim ListArray As Variant
        With Sheets("Sheet1")
        ListArray = .Range(.Range("a1"), .Range("a" & Rows.Count).End(xlUp))
        End With
        ListBox1.List = ListArray
End Sub
Private Sub UserForm_Initialize()
    Dim lb As MSForms.ListBox
    Dim rcArray() As Variant
    Dim lrw As Long, lcol As Long
    Dim rSource As Range
    Set rSource = Workbooks("Sample_BOM.xls").Worksheets("Sheet1").Range("A7:D100") 'THIS IS THE LINE OF CODE I WOULD LIKE TO CHANGE.
    ReDim Preserve rcArray(1 To rSource.Rows.Count, 1 To rSource.Columns.Count)
    With rSource
        For lcol = 1 To .Columns.Count
            For lrw = 1 To .Rows.Count
                rcArray(lrw, lcol) = rSource.Cells(lrw, lcol)
            Next lrw
        Next lcol
    End With
    For Each cell In rSource
        If cell.Value <> vbNullString Then
            Set lb = Me.ListBox1
                With lb
                    .ColumnCount = 4
                    .ColumnWidths = "50,150,100;50"
                    .List = rcArray
                End With
        End If
    Next cell
End Sub
Private Sub CommandButton2_Click()
Dim wks As Worksheet
Dim nextAvailableRow As Long
Dim i As Long

Set wks = Sheets(ComboBox1.Value)
Sheets(ComboBox1.Value).Activate
    For i = 0 To ListBox1.ListCount - 1
        nextAvailableRow = wks.Range("C" & Rows.Count).End(xlUp).Row + 1
        wks.Range("A" & nextAvailableRow) = ListBox1.Column(0, i)
        wks.Range("B" & nextAvailableRow) = ListBox1.Column(1, i)
        wks.Range("C" & nextAvailableRow) = ListBox1.Column(2, i)
        wks.Range("D" & nextAvailableRow) = ListBox1.Column(3, i)
    Next i
    
End Sub
I would also like to only open the userform when a button is pressed in the excel worksheet and not automatically when the workbook is opened. I have this macro attached to a button which opens the user form once the BOM is open.

Sub Auto_Open()
    Dim ws As Worksheet
    For Each ws In Worksheets
        UserForm1.ComboBox1.AddItem ws.Name
    Next ws
    UserForm1.Show
End Sub