VBA Userform works when run directly but not when run through sub

Question

I have a UserForm that prompts the user to both to select a file (Application.FileDialog(.soFileDialogOpen)) and to click a couple of options (various checkboxes). Both of these are required, so I want the OK button to only be enabled if a file has been selected and at least one checkbox has been clicked. I have a sub (CheckAndEnable) that runs both after a file is selected and after any checkbox is clicked (I am using a class to handle this).

Here is a very simplified version of the userform code. The userform has a button called buttonOK, a button that selects a file buttonSelectFile, and a variable number of checkboxes.

Option Explicit
Dim colChkboxes As Collection
Dim intchoice As Integer, AtLeastOneChecked As Boolean, strPath As String

Private Sub buttonOK_Click()
    Hide
End Sub

Private Sub buttonSelectFile_Click()
    Dim intchoice As Integer

    Application.FileDialog(msoFileDialogOpen).AllowMultiSelect = False
    intchoice = Application.FileDialog(msoFileDialogOpen).Show

    If intchoice <> 0 Then
        strPath = Application.FileDialog(msoFileDialogOpen).SelectedItems(1)
        labelPath.Caption = strPath
    End If

    CheckAndEnable
End Sub

Public Sub CheckAndEnable()
    Dim ctrl As Control
    ' checks all checkboxes to determine if at least one is checked    
    AtLeastOneChecked = False
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" Then
            If ctrl.Value = True Then
                AtLeastOneChecked = True
                Exit For
            End If
        End If
    Next ctrl

    ' enable the OK button if file selected and at least one checkbox clicked
    If (AtLeastOneChecked = True) And (Not IsEmpty(strPath)) And (strPath <> "") Then
        buttonOK.Enabled = True
    Else
        buttonOK.Enabled = False
    End If
End Sub

Private Sub UserForm_Initialize()
    buttonOK.Enabled = False
    ' declare vars
    Dim ctrl As Control
    Dim obj As clsCheckBox

    Set colChkboxes = New Collection
    ' set each checkbox to CheckBox Class that handles if checkbox is 
clicked.
    For Each ctrl In Me.Controls
        If TypeName(ctrl) = "CheckBox" Then
            Set obj = New clsCheckBox
            obj.AssignClicks ctrl
            colChkboxes.Add obj
        End If
    Next ctrl
End Sub

I also have a Class Module clsCheckBox with the following code, which calls CheckAndEnable whenever a checkbox is clicked.

Private WithEvents chkbox As MSForms.CheckBox

Public Sub AssignClicks(ctrl As Control)
    Set chkbox = ctrl
End Sub

Private Sub chkbox_Change()
    Call MyUserform.CheckAndEnable
End Sub

When I run the UserForm directly, everything works beautifully. The problem is that when I call the userform in a module (this userform is part of a series of userforms in a larger script), the CheckAndEnable script runs when checkboxes are clicked but doesn't enable the OK button as it is supposed to. I have researched this extensively but haven't been able to find anything. Any help would be appreciated!

Are you launching the userform as MyUserform.Show or creating an instance of it an then launching it? For example Dim frm as new MyUserform and then frm.Show? — Siddharth Rout
It works correctly if I launch the userform using MyUserform.Show. The problem is that this userform is only sometimes displayed (depending on an earlier form), so I have been using Set frm = VBA.UserForms.Add("MyUserform") and then frm.Show because that allows me to flexibly call the userform and others in a loop as necessary. — Raph
See UserForm1.Show -- you can't have anything referring to the form's default instance if you're going to have any code anywhere working off a non-default instance. Specifically, MyUserForm.CheckAndEnable looks like clsCheckBox needs to have a reference to the instance of the form it needs to be working with, rather than assuming the default instance will do. — Mathieu Guindon
Aha! That's what it was. I replaced Call MyUserform.CheckAndEnable with Call chkbox.Parent.CheckAndEnable and now it works without issue. Thank you both! — Raph
Yes Call MyUserform.CheckAndEnable creates a problem if you do the latter as mentioned in my first comment. — Siddharth Rout

Raph Raph · Accepted Answer · 2019-02-25T19:01:37

When you call MyUserform.CheckAndEnable in the chkbox_Change() sub of clsCheckBox, it runs the CheckAndEnable script for the default instance of the UserForm. Since the module showing the userform was creating a non-default instance of the form, the CheckAndEnable script was failing because it had no knowledge of what checkboxes/variables/etc had been changed in the non-default version.

Thanks to Mathieu Guindon for the solution. See UserForm1.Show for more details.

VBA Userform works when run directly but not when run through sub

1 Answers