Hello,
I have a userform and everytime that I try to run appear the error Invalid Procedure or call error
I have a userform and everytime that I try to run appear the error Invalid Procedure or call error
VBA Code:
Option Explicit
Dim folderPath As String
Dim templatesFolderPath As String
Dim format As String
Dim xlApp As Object
Dim xlWorkbook As Object
Private Sub Back_Click()
Me.Hide
Upload.Show
End Sub
Private Sub Cancel_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
Dim xlApp As Object
Dim xlWorkbook As Object
Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = False
Set xlWorkbook = xlApp.Workbooks.Open("C:\TempPrint\Setup.xlsx")
folderPath = xlWorkbook.Sheets(1).range("A2").value
xlWorkbook.Close False
Set xlWorkbook = Nothing
xlApp.DisplayAlerts = False
xlApp.Quit
Set xlApp = Nothing
' Fill the first ComboBox with the subfolders inside the Templates folder.
templatesFolderPath = folderPath
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Dim subfolder As Object
For Each subfolder In FSO.GetFolder(templatesFolderPath).SubFolders
SetAttr subfolder.path, vbNormal
ComboBox1.AddItem subfolder.Name
Next subfolder
End Sub
Private Sub ComboBox1_Change()
' Fill the second ComboBox with the subfolders inside the selected folder
Dim selectedFolderPath As String
selectedFolderPath = templatesFolderPath & "\" & ComboBox1.value & "\"
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Dim subfolder As Object
ComboBox2.Clear
ComboBox3.Clear
On Error GoTo ErrorHandler
For Each subfolder In FSO.GetFolder(selectedFolderPath).SubFolders
SetAttr subfolder.path, vbNormal
ComboBox2.AddItem subfolder.Name
Next subfolder
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Description, vbCritical
End Sub
Private Sub ComboBox2_Change()
' Fill the third ComboBox with the Word template files inside the selected folder.
Dim selectedFolderPath As String
selectedFolderPath = templatesFolderPath & "\" & ComboBox1.value & "\" & ComboBox2.value & "\"
Dim FSO As Object
Set FSO = CreateObject("Scripting.FileSystemObject")
Dim file As Object
ComboBox3.Clear
On Error GoTo ErrorHandler
For Each file In FSO.GetFolder(selectedFolderPath).Files
If Right(file.Name, 5) = ".docx" Or Right(file.Name, 5) = ".docm" Then
SetAttr file.path, vbNormal
ComboBox3.AddItem file.Name
End If
Next file
Exit Sub
ErrorHandler:
MsgBox "Error: " & Err.Description, vbCritical
End Sub
Private Sub Confirm_Click()
' Check that all three ComboBoxes have values selected.
If ComboBox1.ListIndex = -1 Or ComboBox2.ListIndex = -1 Or ComboBox3.ListIndex = -1 Then
MsgBox "Please select the document.", vbExclamation
Exit Sub
End If
' Check that filename and filepath have values entered.
If TextBox1.value = "" And TextBox2.value = "" Then
MsgBox "Please fill the information.", vbExclamation
Exit Sub
End If
If TextBox1.value = "" And TextBox2.value <> "" Then
MsgBox "Please fill the information.", vbExclamation
Exit Sub
End If
If TextBox1.value <> "" And TextBox2.value = "" Then
MsgBox "Please fill the information.", vbExclamation
Exit Sub
End If
' Check that a Word template file has been selected and create a copy of it.
Dim selectedFolderPath As String
selectedFolderPath = templatesFolderPath & "\" & ComboBox1.value & "\" & ComboBox2.value & "\"
Dim selectedFileName As String
selectedFileName = selectedFolderPath & ComboBox3.value
If Not Dir(selectedFileName) <> "" Then
MsgBox "The selected file " & selectedFileName & " does not exist.", vbCritical
Exit Sub
End If
' Create a copy of the selected file in the temporary folder.
Dim tempDestinationFolderPath As String
tempDestinationFolderPath = "C:\TempPrint\"
FileCopy selectedFileName, tempDestinationFolderPath & "wordTemplate.docx"
' Extract gender.
Dim gender As String
If OptionButton1.value = True Then
gender = "Female"
ElseIf OptionButton2.value = True Then
gender = "Male"
ElseIf OptionButton3.value = True Then
gender = "Non applicable"
Else
MsgBox "Please select one of the gender options.", vbOKOnly + vbExclamation, "No Selection Made"
Exit Sub
End If
' Extract format.
Dim format As String
If OptionButton7.value = True Then
format = "Word"
ElseIf OptionButton8.value = True Then
format = "PDF"
ElseIf OptionButton6.value = True Then
format = "Both"
Else
MsgBox "Please select one of the format options.", vbOKOnly + vbExclamation, "No Selection Made"
Exit Sub
End If
' Use the gender and format strings in the next macro
Me.Hide
Conditionals.Show
End Sub
Private Sub UserForm_Terminate()
' Delete the uploaded Word template file if it exists.
Dim uploadedFilePath As String
uploadedFilePath = "C:\TempPrint\wordTemplate.docx"
If Dir(uploadedFilePath) <> "" Then
On Error Resume Next
Kill uploadedFilePath
End If
' Release the memory used by Excel and Word.
If Not xlWorkbook Is Nothing And Not xlApp Is Nothing Then
xlWorkbook.Close False
xlApp.DisplayAlerts = False
xlApp.Quit
Set xlWorkbook = Nothing
Set xlApp = Nothing
End If
End Sub