Can't initialize a userform always saying invalid procedure or call error

nmc

New Member
Joined
Aug 25, 2022
Messages
38
Office Version
  1. 2021
Platform
  1. Windows
Hello,
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
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Forum statistics

Threads
1,223,893
Messages
6,175,240
Members
452,621
Latest member
Laura_PinksBTHFT

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top