Excel VBA: Compile Error: Method or data member not found

Question

EDIT: To clarify, the code seen below is within a module and the UserForm is all contained within its own code.

I have the following code. When I go to run it, Excel throws me a compile error: Method or data member not found and highlights the following piece of code: .showInputsDialog. I have no idea how to resolve this error.

To give more information, the sub sportUserForm is supposed to call up a UserForm sportsUsrFrm. Any help with this issue is greatly appreciated.

Option Explicit

Sub sportUserForm()

Dim sSport As String, sPreference As String

If sportsUsrFrm.showInputsDialog(sSport, sPreference) Then
    MsgBox "Your favorite sport is " & sSport & ", and you usually " _
            & sPreference & "."
        Else
    MsgBox "Sorry you don't want to play."
End If
End Sub

Public Function showInputsDialog(sSports As String, sPreference As String) As Boolean
Call Initialize
Me.Show
If Not cancel Then
    If optBaseball.Value Then sSport = "Baseball"
        ElseIf optBasketball.Value Then sSport = "Basketball"
        Elss sSport = "Football"
    End If

    If optTV.Value Then sPreference = "watch on TV" _
        Else: sPreference = "go to games"
    End If

    showInputsDialog = Not cancel
    Unload Me
End Function

UserForm code for sportUsrFrm

Option Explicit

Private Sub cmdCnl_Click()
    Me.Hide
    cancel = True
End Sub

Private Sub cmdOK_Click()

    If Valid Then Me.Hide
    cancel = False
End Sub

What code is in what module? Is everything in the userform? I'd suggest taking a look at this example in documentation for a better approach to form handling. — Comintern
– Comintern, Commented Oct 25, 2016 at 23:00
@Comintern unless that's code that has been added by default, I do not believe I have written any code for sportsUsrFrm_Initialize. — Jonathan Rauscher
– Jonathan Rauscher, Commented Oct 25, 2016 at 23:12

Graham · Accepted Answer · 2017-09-21 21:49:25Z

You're getting the error because showInputsDialog isn't a member of the form, it's a member of the module you're calling it from. You should also be getting compiler errors on these two lines...

Call Initialize
Me.Show

...because you seem to be getting the module and form code mixed up.

That said, you're overthinking this. A UserForm is a class module, and it can be stored in a variable (or in this case, in a With block), and can have properties. I'd add a Cancelled property to the form:

'In sportsUsrFrm
Option Explicit

Private mCancel As Boolean

Public Property Get Cancelled() As Boolean
    Cancelled = mCancel
End Property

Private Sub cmdCnl_Click()
    Me.Hide
    mCancel = True
End Sub

Private Sub cmdOK_Click()
    If Valid Then Me.Hide   '<-- You still need to implement `Valid`
End Sub

And then call it like this:

Sub sportUserForm()
    With New sportsUsrFrm
        .Show
        Dim sSport As String, sPreference As String
        If Not .Cancelled Then
            If .optBaseball.Value Then
                sSport = "Baseball"
            ElseIf .optBasketball.Value Then
                sSport = "Basketball"
            Else
                sSport = "Football"
            End If

            If .optTV.Value Then
                sPreference = "watch on TV"
            Else
                sPreference = "go to games"
            End If
            MsgBox "Your favorite sport is " & sSport & ", and you usually " _
                   & sPreference & "."
        Else
            MsgBox "Sorry you don't want to play."
        End If
    End With
End Sub

Collectives™ on Stack Overflow

Excel VBA: Compile Error: Method or data member not found

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related