Passing textbook answer in userform as a parameter to a separate macro

Question

So I am trying to use a userform to pass in an answer as a means to search for a file within a given folder. For example this is what I want to do.

Choose File 1 __

Choose File 2 __

Button

I want to be able to enter file paths in the textbox and then pass them as parameters to a separate macro which will load data into the current workbook. However, I cant find anything about this anywhere.

Can someone show a basic example of something like this?

Awesome. I'm still confused by how to pass this from a userform.

So I have

Private Sub TextBox1_Change()

End Sub

Private Sub TextBox2_Change()

End Sub

how do I specify the answer as a global variable that I can pass to the GetDataFromSelectedFile macro?

This is the userform layout:

LabelTextBox1 TextBox LabelTextBox2 Textbox

                   Command Button

Subs under the userform:

Private Sub CommandButton1_Click() Call ProcessSelectedFile - This is the same as earlier posted Call RemoveDups - doesnt really matter what it does since it works after
ProcessSelectedFile is implemented End Sub

Private Sub TextBox1_Change() 'ChDrive (sPath) ' This is where I was unclear about what you were doing 'ChDir (sPath) 'vOpen_File = Application.GetOpenFilename

End Sub

Private Sub TextBox2_Change()

End Sub

Basically if you can just show me how to log the answer in the userform and pass it to ProcessSelectedFile and then make the command button click do those two macros it will work.

Thanks

Jon Crowell Jon Crowell · Accepted Answer · 2012-06-07T02:23:08

This should get you pointed in the right direction.

I set up 2 test files in a directory called c:\killme called workbook1.xlsx and workbook2.xlsx. I entered different values in cell A1 of Sheet1 for both files, saved and closed them.

In a third workbook, I added a module and added the code below.

Option Explicit

Sub GetDataFromSelectedFile(sFile As String, sPath As String)
    ' Clear sheet before getting data from selected workbook
    ActiveSheet.UsedRange.Clear

    ' Get data from the selected workbook
    With Sheet1.Range("A1")
        .Formula = "= '" & sPath & "[" & sFile & "]Sheet1'!RC"
        .Value = .Value
    End With
End Sub

Sub ProcessSelectedFile()
    Dim SelectedFile As String
    Dim SelectedPath As String

    ' Set SelectedFile to value selected by user
    SelectedPath = "C:\killme\"
    SelectedFile = "workbook2"

    Call GetDataFromSelectedFile(SelectedFile, SelectedPath)
End Sub

Pass in the path and file to GetDataFromSelectedFile and range A1 on Sheet1 of the workbook you are in will be populated with the corresponding cell from the selected file.

Passing textbook answer in userform as a parameter to a separate macro

2 Answers