CODEWITHSUNDEEP
vbacheckboxuserform
Javier
Javier

Reputation: 3

Function to clear UserForms checkbox in VBA

I am creating a program which has several UserForms. At the end of the program I need to clear every Checkbox inside some UserForm. I have created a Function, but it cannot recognise which UserForm it should clear, can you help me there? Here is the code:

Function ClearUserForm(ByVal userf As String)

Dim contr As Control

For Each contr In userf.Controls

    If TypeName(contr) = "CheckBox" Then
        contr.Value = False
    End If
Next

End Function

And I am calling the function like this, for example:

ClearUserForm ("UserForm2")

It seems not to recognize which UserForm it should act upon.

Upvotes: 0

Views: 4771

Answers (2)

Ambie
Ambie

Reputation: 4977

Shai Rado's advice is good and you should have a look at how he creates the object from its 'key'.

I only post this answer to check if you're aware that you could pass the object itself in the call. So your code could be like so:

Option Explicit

Public Sub RunMe()
    ClearCBoxes UserForm1
End Sub

Private Sub ClearCBoxes(frm As MSForms.UserForm)
    Dim ctrl As Control

    For Each ctrl In frm.Controls
        If TypeOf ctrl Is MSForms.ComboBox Then
            ctrl.Value = False
        End If
    Next

End Sub

Upvotes: 2

Shai Rado
Shai Rado

Reputation: 33682

You don't need a Function (since you are not returning any arguments), in your case a Sub will do.

You need to qualify an Object to the User_Form selected by using:

Set objUserForm = UserForms.Add(userf)

Whole code

(Tested)

Option Explicit

Sub ClearUserForm(ByVal userf As String)

Dim contr As Control
Dim objUserForm As Object

Set objUserForm = UserForms.Add(userf)

For Each contr In objUserForm.Controls

    If TypeName(contr) = "CheckBox" Then
        contr.Value = False
    End If
Next
' just to check that all checkboxes are cleared
objUserForm.Show

End Sub

Upvotes: 0

Related Questions