CodesiriuS
12-20-2016, 02:36 PM
Hello I have a user form with two command buttons and a text box - The user selects the first command button and and it brings up the c: directory so the user can get a file - once the file is selected it drops the path in a text box - (this part works fine) the problem is I'm trying to get my command button 2 (named process file) to apply my macro to the selected file in the textbox. so far I can only get the macro to work on the active worksheet - Can anybody assist? currently I get a script out of range error - on this line
-----Set wThat = Workbooks(TextBox1.Value)-----
Private Sub CommandButton1_Click()
Dim SelectedFile As String
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select required files"
.AllowMultiSelect = False
.InitialFileName = "Computer:"
.Filters.Clear
.Filters.Add "Excel Documents", "*.Xlsx", 1
.Filters.Add "Excel Documents", "*.Xls", 1
If .Show Then
SelectedFile = .SelectedItems(1)
Me.TextBox1 = SelectedFile
Else
MsgBox "User cancelled." & vbLf & vbLf & _
"Processing terminated."
Exit Sub
End If
End With
End Sub
Private Sub CommandButton2_Click()
Dim ws As Worksheet
Dim wThis As Workbook
Dim wThat As Workbook
Set wThis = ThisWorkbook
Set wThat = Workbooks(TextBox1.Value)
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Call Cleanup(ws)
Next
Application.ScreenUpdating = True
End Sub
-----Set wThat = Workbooks(TextBox1.Value)-----
Private Sub CommandButton1_Click()
Dim SelectedFile As String
With Application.FileDialog(msoFileDialogFilePicker)
.Title = "Select required files"
.AllowMultiSelect = False
.InitialFileName = "Computer:"
.Filters.Clear
.Filters.Add "Excel Documents", "*.Xlsx", 1
.Filters.Add "Excel Documents", "*.Xls", 1
If .Show Then
SelectedFile = .SelectedItems(1)
Me.TextBox1 = SelectedFile
Else
MsgBox "User cancelled." & vbLf & vbLf & _
"Processing terminated."
Exit Sub
End If
End With
End Sub
Private Sub CommandButton2_Click()
Dim ws As Worksheet
Dim wThis As Workbook
Dim wThat As Workbook
Set wThis = ThisWorkbook
Set wThat = Workbooks(TextBox1.Value)
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
Call Cleanup(ws)
Next
Application.ScreenUpdating = True
End Sub