VBA Userform combobox rowsource and folder value help

I have tried looking around here for the answer to my problems but no luck.

I am looking to do two different things....

Firstly, I am simply trying to populate a combobox based on a range in a specified workbook at a different location (I don't want the workbook to have to be open) for example I need the ranges of B5:B41,D5:D40 and F5:F45 of the workbook WC.xls located at C:/WC.xls all compiled into this combobox.


Secondly I am looking for my userform to automatically populate the next number that would come along in a series of file names at a specified location (for example, a folder full of files named ABC1, ABC2, ABC3, ABC4, and ABC5, I would need for the code to produce the number 6 (since that is the next number I would need the file name for)).


Any help or direction would be greatly appreciated!

---------- Post added at 09:46 AM ---------- Previous post was at 08:14 AM ----------

and also protect cells but only allow the userform to enter data?

---------- Post added at 12:26 PM ---------- Previous post was at 10:46 AM ----------

anything will help...

To populate form another workboo the simplest method is probably to open the workbook, grab the data then close. Hide this action by switching off ScreenUpdating

Your code would need to couunt the number of files in the folder, assumung they are all named like this.

Protect with UserInterface is the best way to allow the userform to interact with the worksheet

thanks for the link

any chance you have what the code would be like for the first two things? Still new to VBA so translating what I want to do into the syntax to do it is hard for me.

---------- Post added at 02:59 PM ---------- Previous post was at 01:24 PM ----------

got the protect cells down

Something like this

Option Explicit

Private Sub UserForm_Initialize()
    Dim oWbk   As Workbook
    Dim rdata  As Range

    Application.ScreenUpdating = False
    'check if workbook containing source is open if not open it.
    If Not WorkbookOpen("D:\Accounts\Test.xls") Then
        Set oWkbk = Workbooks.Open(""D:\Accounts\Test.xls"")
    Else: Set oWkbk = Workbooks(""D:\Accounts\Test.xls"")
    End If
    'this is the data to load to combobox
    Set rdata = oWkbk.Worksheets("MASTER ").Range("A5:A103")

    With Me.ComboBox1
        .Clear    'clear any previous data
        .Style = fmStyleDropDownList
        .BoundColumn = 0
        'set RowSource
        .RowSource = rdata.Address(external:=True)
        '0 = no selection
        .ListIndex = 0
    End With


    Set oWbk = Nothing
    Set rdata = Nothing
    Application.ScreenUpdating = True
End Sub

Where is the code erroring?

Try

Dim oWbk   As Workbook
    Dim rdata  As Range

    Application.ScreenUpdating = False
    'check if workbook containing source is open if not open it.
    If Not Workbooks.Open("W:\WC\Forms\WORKCENTERLIST.xls") Then
        Set oWkbk = Workbooks.Open("W:\WC\Forms\WORKCENTERLIST.xls")
    Else: Set oWkbk = Workbooks("WORKCENTERLIST.xls")
    End If
    'this is the data to load to combobox
    Set rdata = oWkbk.Worksheets("Sheet1").Range("B5:B41")

    With Me.ComboBox5
        .Clear    'clear any previous data
        .Style = fmStyleDropDownList
        .BoundColumn = 0
        'set RowSource
        .RowSource = rdata.Address(external:=True)
        '0 = no selection
        .ListIndex = 0
    End With


    Set oWbk = Nothing
    Set rdata = Nothing
    Application.ScreenUpdating = True

solved the file search problem along with some great help

here is my code if anyone is curious

    Dim fn As String, myMax
    fn = Dir("W:\NCR\NCR*.xls*")
    If fn = "" Then
        MsgBox "No file found"
        Exit Sub
    End If
    With CreateObject("VBScript.RegExp")
        .Pattern = "\d+(?=.xls.?$)"
        Do While fn <> ""
            If .test(fn) Then
                If .Execute(fn)(0) > myMax Then myMax = .Execute(fn)(0)
            End If
            fn = Dir
        Loop
    End With
    Me.TextBox1.Value = myMax + 1

if anyone is interested in what that did it searched the NCR folder for file names with NCR#### and returned the #### (and added 1) into my textbox1