VBA Excel Custom Userform with Checkboxes, wont work in macro code

Question

I need a little VBA help. I created a custom user form with options yes, n/a, no and a commandbutton to enter. Now i wanted to add the logic, it should filter only the shown rows. It always returns error on my first if line (if checkbox1.enabled ). I already tried if checkbox1.value Is True, but it still wont work. Any ideas? Thanks!

Private Sub CommandButton1_Click()

    Dim runner As Variant, row As Variant
    row = 9

    If CheckBox1.Enabled Is True Then
        For Each runner In row
            If Not runner.Hidden Then
                If cell(row, 10).Value Is Empty Then
                    runner.Hide
                End If
            End If

        Next runner
    End If


End Sub

Victor Moraes · Accepted Answer · 2016-09-09 11:56:18Z

To fix the issue in your first If, you need to change it to

If CheckBox1.Enabled = True Then

This is the correct syntax to check if a value is True in VBA. The following is also an acceptable syntax:

If CheckBox1.Enabled Then

If nothing is provided in such validation, VBA will check if the value is True by default. Similarly, to check if value is False you can do:

If CheckBox1.Enabled = False Then

Or also

If Not CheckBox1.Enabled Then

Once you fix the current issue, you will find another problem in the following line:

If cell(row, 10).Value Is Empty Then

To check if a cell is Empty, the correct syntax would be

If IsEmpty(Cells(row, 10).Value) Then

Please note that I'm using Cells and not cell. Plus, this will check for the global reference of a cell, which in turn looks for the cell in the active worksheet. Supposing you want to check for the cell in Sheet1, but user activates Sheet2 while using your UserForm, the cell in Sheet2 will be checked instead. To avoid this, is advisable that you make a proper reference to the cell, like the following:

If IsEmpty(ThisWorkbook.Worksheets("YourSheetName").Cells(row, 10).Value) Then

There are also some problems in your For loop, if your code is exactly as you posted. It will probably not work, but I'd need a better understanding on what is going on there in order to assist you.

I hope this helps.

Collectives™ on Stack Overflow

VBA Excel Custom Userform with Checkboxes, wont work in macro code

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related