Loop Through CheckBox Controls in VBA UserForm

Question

I have a user form in Excel VBA with a check box for each month.

Selecting one or more cause the required month to be shown on the sheet, I copy-pasted the code 12 times and it works but I'm sure there is a better way doing it with a For loop.

This is a part of my code (it goes on 12 times):

    If CheckBox1.Value = True Then
        ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems("1").Visible = True
    Else
       ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems("1").Visible = False
    End If

    If CheckBox2.Value = True Then
        ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems("2").Visible = True
    Else
       ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems("2").Visible = False
    End If

I tried writing:

for i in range 1 to 12

and then writing my code but there seem to be a problem when I put "i" instead of the numbers.

Mike Woodhouse · Accepted Answer · 2013-08-01 08:25:43Z

2

Assuming you aren't using Tristate checkboxes, then the .Value can only be True or False, so we should be able to get away with something like this:

(Assumes your code runs inside the UserForm, so that Controls is directly accessible)

Dim mthIdx as Long
Dim nm as String
Dim c As Control

With ActiveSheet.PivotTables("PivotTable1").PivotFields("month")
    For mthIdx = 1 To 12
        nm = "CheckBox" & mthIdx
        Set c = Controls(nm)
        .PivotItems(mthIdx).Visible = c.Value
    Next
End With

(The With clause isn't strictly necessary, but it's usually a good idea to resolve nested COM references as infrequently as possible)

answered Aug 1, 2013 at 8:25

Mike Woodhouse

52.3k12 gold badges90 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

matzone · Accepted Answer · 2013-08-01 07:57:06Z

1

Try this ..

Dim i As Integer
Dim sN As String
Dim chx As MSForms.CheckBox
Dim obj As OLEObject

For i = 1 to 12
    sN = format(i) 
    Set obj = OLEObjects("CheckBox" & sN)
    Set chx = obj.Object

    If chx.Value = True Then
        ActiveSheet.PivotTables("PivotTable" & sN).PivotFields("month").PivotItems(sN).Visible = True
    Else
       ActiveSheet.PivotTables("PivotTable" & sN).PivotFields("month").PivotItems(sN).Visible = False
    End If
Next

edited Aug 1, 2013 at 7:57

answered Aug 1, 2013 at 7:30

matzone

5,7293 gold badges19 silver badges20 bronze badges

2 Comments

user1040563 Over a year ago

There seem to be a problen with this line ".OLEObjects" it raises en error "method or data member not found"

Mike Woodhouse Over a year ago

OLEObjects refers to controls in a worksheet, I think. In a UserForm (up to Excel 2003 at least) we probably want Controls...

Palendrone · Accepted Answer · 2013-08-01 07:30:51Z

0

I've not checked the code but this should put you along thr right path if it's not spot on though...

For i = 1 to 12
    If CheckBox(i).Value = True Then
        ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems(i).Visible = True
    Else
       ActiveSheet.PivotTables("PivotTable1").PivotFields("month").PivotItems(i).Visible = False
    End If
Next i

answered Aug 1, 2013 at 7:30

Palendrone

3631 gold badge2 silver badges14 bronze badges

1 Comment

LS_ᴅᴇᴠ Over a year ago

AFAIK, VBA doesn't support controls arrays in forms, so you just can't create CheckBox(...), neither access them this way subsequently.

Collectives™ on Stack Overflow

Loop Through CheckBox Controls in VBA UserForm

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related