Hi all,
I'm hoping someone can point out why my userform is behaving oddly.
I have a userform with 5 command buttons and 5 text boxes. Each textbox has a unique 'tag' property which is used in the code. Buttons 1 to 4 fire the file dialog for the user to select a raw data excel file and puts the filepath into the corresponding textbox. CommandButton5 contains code which opens these filepaths. TextBox5 is a free text entry where the user can choose the project name. The code for CommandButton5 is below. Basically the form is supposed to get the user to select the 4 data files, open the files, save a temporary file with a filename of the textbox 'tag', open a blank workbook and save it with the filename of TextBox5.Value.
This all works perfectly except the file opened by TextBox4 is saved with the 'tag' property from TextBox5 and I have no idea why. Any ideas? thanks
I'd also appreciate any tips regarding anything I may have missed out of my code or any noob errors I've made.
Private Sub CommandButton5_Click()
Dim CC As Control
Application.DisplayAlerts = False
For Each CC In Me.Controls
If TypeName(CC) = "TextBox" Then
If CC.Value = "" Then
MsgBox "Please select the " & CC.Tag
Exit Sub
End If
End If
Next CC
On Error Resume Next
For Each CC In Me.Controls
Workbooks.Open CC.Value
ActiveWorkbook.SaveAs Filename:=Environ("TMP") & "\" & CC.Tag & ".xlsx"
Next CC
Unload Me
Workbooks.Add
ActiveWorkbook.SaveAs Filename:=Environ("TMP") & "\" & TextBox5.Value & ".xlsx"
Application.DisplayAlerts = True
ThisWorkbook.Close savechanges:=False
End Sub
Bookmarks