Creating an input box in VBA with multiple entries allowed

I have a macro that will filter a column allowing one value to be referenced. I would like to be able to reference multiple values
Sub FilterByWeeks()
Application.ScreenUpdating = False
Dim i As Long, targWk As Long

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Week #")
  For i = 1 To 40
    .PivotItems(i).Visible = True
  Next i
End With
targWk = InputBox("What week?")
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Week #")
For i = 1 To 40
    If i = targWk Then GoTo here
    .PivotItems(i).Visible = False
here:
Next i
End With
Application.ScreenUpdating = True
End Sub

Open in new window

I would like to be able to filter by more than value in PivotFields("Week #"). What is the most compact way to create the input box and add all the selected values to show. Radio buttons won't work since there will be up to 52 values.

Thanks!
John
LVL 1
John CarneyReliability Business Tools Analyst IIAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtCommented:
John,
One approach might be to display a userform with a grid of 52 checkboxes. The checkboxes would be arranged in 6 rows, with 10 checkboxes on each of the first 5 rows and 2 on the last. You could put a gutter between the first 5 columns and the second so it would be easy for a user to figure out which week he is checking without needing a caption for each checkbox.

Once the user is happy with the checkboxes he has checked, he could click a command button and VBA would use that information to update the PivotFields.

Brad
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Okay, so I've got code that will display a userform with checkboxes 1 thru 40 (I'll deal with the rest of the year later). Here's the code I'm using:
Private Sub UserForm_Initialize()

Dim LastCol     As Long
Dim i           As Long
Dim chkBox      As MSForms.CheckBox

LastCol = [GG2].End(xlToLeft).Column - 1
For i = [AL2].Column To LastCol
    Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "CheckBox_" & i)
    chkBox.Caption = Cells(2, i).Value
    If i >= 38 And i < 48 Then
        chkBox.top = 10
        chkBox.Left = 0 + ((i - 37) * 30)
    ElseIf i >= 48 And i < 58 Then
        chkBox.top = 40
        chkBox.Left = 0 + ((i - 47) * 30)
    ElseIf i >= 58 And i < 68 Then
        chkBox.top = 70
        chkBox.Left = 0 + ((i - 57) * 30)
    ElseIf i >= 68 Then
        chkBox.top = 100
        chkBox.Left = 0 + ((i - 67) * 30)
    End If
Next i

End Sub

Open in new window

How do I get my original code to show all the columns whose row 2 value is checked?

Thanks,
John
0
byundtCommented:
You drag the checkbox from the toolbox into the userform. The toolbox will appear in the VBA Editor when you create a userform of click on it. I suggest resizing the checkbox to a minimal size, then positioning it where you want. Copy that checkbox and CTRL + V to paste another one on the userform. You will then need to drag it into position. You will also want to verify in the Property pane on the left that the newly added checkbox is named according to its position in the checkbox array. You do this by setting its (Name) property to something like Checkbox11.

In the attached workbook, I created an array of 10 checkboxes. I only did 10 because it was somewhat tedious creating the checkboxes and I wasn't sure whether you would like the idea. You may think of this as the first row of your 52 week array.

You display the userform by issuing the VBA command Userform1.Show. Among other things, the TestUserform sub does this for you. Once displayed, you may check and uncheck the checkboxes as you wish.

If you click OK to exit the userform, then VBA remembers the TRUE/FALSE values of the checkboxes in a Public array called cbWeeks. If you click Cancel, the checkboxes will be restored to their previous values as recorded in cbWeeks (i.e. overwriting any changes you may have made). The userform will be unloaded after either a Cancel or an OK. Clicking the X in the top right corner terminates the userform and is equivalent to a Cancel.

There are two subs you can run in the attached workbook. The first one (TestUserform) displays the userform and displays a Msgbox with the number of boxes that are checked in the saved state of the userform, which it gets by counting the number of TRUE values in cbWeeks. The second one (ClearCheckboxes) will clear the values in cbWeeks and also the checkboxes in the userform.
'These four subs go in the code pane for the userform
Private Sub cbCancel_Click()
Dim i As Long, n As Long
Me.Hide
n = UBound(cbWeeks)     'A public Boolean array
For i = 1 To n
    Me.Controls("Checkbox" & i).Value = cbWeeks(i)
Next
Unload Me
End Sub

Private Sub cbOK_Click()
Dim i As Long, n As Long
n = UBound(cbWeeks)     'A public Boolean array
For i = 1 To n
    cbWeeks(i) = CBool(Me.Controls("Checkbox" & i).Value)
Next
Me.Hide
Unload Me
End Sub

Private Sub UserForm_Initialize()
Dim i As Long, n As Long
n = UBound(cbWeeks)     'A public Boolean array
For i = 1 To n
    Me.Controls("Checkbox" & i).Value = cbWeeks(i)
Next
End Sub

Private Sub UserForm_Terminate()
cbCancel_Click
End Sub

Open in new window

'The Public declaration and two subs go in a regular module sheet
Public cbWeeks(1 To 10) As Boolean

Sub TestUserform()
Dim i As Long, n As Long, nChecks As Long
n = UBound(cbWeeks)
UserForm1.Show
For i = 1 To n
    If cbWeeks(i) = True Then nChecks = nChecks + 1
Next
MsgBox nChecks & " checkboxes were checked"
End Sub

Sub ClearCheckboxes()
Dim i As Long, n As Long
On Error Resume Next
n = UBound(cbWeeks)
For i = 1 To 10
    cbWeeks(i) = False
    UserForm1.Controls("Checkbox" & i).Value = False
Next
On Error GoTo 0
End Sub

Open in new window

CheckboxArrayOnUserformQ28737410.xlsm
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

byundtCommented:
John,
If you use the code that I just posted, you modify your original code using the TRUE/FALSE values in cbWeeks to decide whether to display the corresponding PivotItem.

It is also worth noting that you named your checkboxes with an underscore (like Checkbox_11), whereas I omitted the underscore (like Checkbox11). Either way is valid--just make sure the code is consistent in naming approach.
Brad
0
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, Brad. I spent about two hours trying to adapt your code to my needs but couldn't figure it so I'm uploading a test workbook. Using the attached workbook, I need a userform and all code required to be able to click a button and

1.

present a userform with checkboxes 1 thru 40 (if there's a macro like the one I initially posted that adds the checkboxes) or 1-10 if not.

2.

Filter the "Week#" column so that it filters out all columns who row 4 values are not checked.Does that make sense?

Thanks, John
JC_FilterPivotTable2.xlsm
0
byundtCommented:
John,
Your code to create checkboxes was putting them on a worksheet. They need to be in a userform, however. So I dragged the userform from my previous workbook into your workbook. Remember that I was somewhat lazy, and only put 10 checkboxes on the userform.

Next, I modified your sub from original Question so it would display the userform and make the PivotTable columns visible or not according to the Boolean array cbWeeks that was updated by the userform checkboxes. Note that the dimension of Public Boolean array cbWeeks must match the number of checkboxes on the userform.
'The Public declaration and two subs go in a regular module sheet
Public cbWeeks(1 To 10) As Boolean      'This array size must match number of checkboxes on userform

Sub FilterByWeeks()
Dim i As Long, n As Long, targWk As Long

n = UBound(cbWeeks)
UserForm1.Show
Application.ScreenUpdating = False  'This statement must appear after Userform1.Show. You do want to see the userform, don't you?

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Week #")
    For i = 1 To n
        .PivotItems(i).Visible = cbWeeks(i)
    Next i
End With
End Sub

Sub ClearCheckboxes()
Dim i As Long, n As Long
On Error Resume Next
n = UBound(cbWeeks)
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Week #")
    For i = 1 To n
        cbWeeks(i) = False
        UserForm1.Controls("Checkbox" & i).Value = False
        .PivotItems(i).Visible = Not cbWeeks(i)
    Next i
End With
On Error GoTo 0
End Sub

Open in new window

I added two buttons to your worksheet. The first one calls your sub FilterByWeeks, which displays the userform and then makes the PivotTable columns visible accordingly. The second one clears the userform and makes all columns visible in the PivotTable.

Brad
JC_FilterPivotTable2_Q28737410.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
John CarneyReliability Business Tools Analyst IIAuthor Commented:
Thanks, Brad! You always make it simple and easy to understand.   ~ John
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.