VBA Boolean Function type mismatch on return

Viewed 171

I have searched here all morning, but see nothing like this particular error. While debugging an Excel VBA macro I'm getting a very frustrating run-time error - type mismatch 13 error when assigning the return value of function 'Display_Form' to the boolean variable 'success'. The form displays and behaves properly, but upon exiting the form and then the calling function, this error pops up. Here is the code. I'm hitting a wall with this one. Any ideas?

Public Sub Add_New_Account()
    ' Process userforms to create new Bank account

    Dim success As Boolean
    
    success = Display_Form(NewAccountForm1)
    If success Then
        Select Case accountDict(ACCT_TYPE_HEADER)
            Case CREDIT_CD, CREDIT_IFL, CREDIT_DEPT
            Case BANK_CHK, BANK_SAV
                ' Nothing needed at this time
        End Select
    End If
    
End Sub
Public Function Display_Form(targetForm As Object, Optional leftPos As Integer = 0, Optional topPos As Integer = 0) As Boolean
    ' Open the 'targetForm' userform
    Display_Form = True
    On Error GoTo ErrorHandler
    Application.EnableCancelKey = xlErrorHandler

    With ActiveSheet
        Load targetForm
        With targetForm
          .StartUpPosition = 0
          If leftPos Then
            .Left = leftPos
          Else
            .Left = Application.Left + Application.Width - .Width
          End If
          If topPos Then
            .Top = topPos
          Else
            .Top = Application.Top + Application.Height - .Height
          End If
          .Show vbModal
        End With
        Unload targetForm
    End With
    Exit Function
    
ErrorHandler:
    ' Handle Ctrl-Break gracefully
    If Err.Number = 18 Then
        MsgBox "Use the Cancel button instead of Ctrl-Break"
        Resume
    Else
        ' Some other error occurred
        MsgBox "Sorry, something went wrong: " & vbNewLine & _
             Err.Number & " - " & Err.Description & vbNewLine & _
             "Macro ended unexpectedly.", vbExclamation
        Display_Form = False
    End If
End Function
1 Answers

I know this is a work-around rather than an explanation, seems to work for me

Dim success As Boolean, oForm as object

set oForm = NewAccountForm1
success = Display_Form(oForm)

Might prompt someone else to know why

Related