Solved: Change to column if more than one item is checked

[Djblois]

I am using a userform to create pivot tables for the user. I want to have it switch to Columns for the data field, automatically when the user checks off two data fields. I want to know if it is possible to get excel to know if the user checks off more than box.

[XLGibbs]

Yes, there is/are ways to do this, but it would be impractical for performance.

Record a macro manipulating two data fields to the column position, that should give you a start on the code necessary. I do a lot of this kind of thing, but it is actually easier to help you modify code you wrote than provide a solution...

You can have the field list included in your userform, since that userform is generating the pivot table correct?

You should be able to record yourself formatting a two+ data point pivot to see the syntax necessary. At that point, your form code would just have to count the selected items from the multi select combo or list box (the user forms field list)

[Djblois]

here is my code:

[VBA]Sub SalesReportOptions()

If PivotTableOptions.Cases.Value = True Then
With pt.PivotFields("Cases")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "(Cases)"
End With
End If
If PivotTableOptions.Units.Value = True Then
With pt.PivotFields("Units")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "(Units)"
End With
End If
If PivotTableOptions.Amount.Value = True Then
With pt.PivotFields("Amt ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "(Amount)"
End With
End If
If PivotTableOptions.Cost.Value = True Then
With pt.PivotFields("Total Cost ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "(Total Cost)"
End With
End If
If PivotTableOptions.UnitCost.Value = True Then
With pt.PivotFields("Unit Cost ($)")
.Orientation = xlDataField
.Function = xlAverage
.NumberFormat = "#,##0.00"
.Name = "(Unit Cost)"
End With
End If
If PivotTableOptions.Profit.Value = True Then
With pt.PivotFields("Profit ($)")
.Orientation = xlDataField
.Function = xlSum
.NumberFormat = "#,##0"
.Name = "(Profit)"
End With
End If
If PivotTableOptions.Price.Value = True Then
With pt.PivotFields("Price ($)")
.Orientation = xlDataField
.Function = xlAverage
.NumberFormat = "#,##0.00"
.Name = "(Average Price)"
End With
End If
If PivotTableOptions.ProfitPerc.Value = True Then
With pt.PivotFields("%")
.Orientation = xlDataField
.Function = xlAverage
.NumberFormat = "0.00%"
.Name = "(Profit %)"
End With
End If

If PivotTableOptions.Column = True Then

If PivotTableOptions.Year.Value = True Then
With pt.DataPivotField
.Orientation = xlColumnField
.Position = 2
End With
Else
With pt.DataPivotField
.Orientation = xlColumnField
.Position = 3
End With
End If

End Sub[/VBA]

As you can see, I give the user a choice of what data they won't to view in the Pivot table. And as of right now I give the user a choice of whether to switch to Columns or not. Well I want to do three things with this code:

1)Whenever a user checks off two or more items it goes to Columns
2)Whenever a user checks off only one item it goes to rows
3)Whenever a user dosn't check off any items, they will get a message that they must chose one item and the form will show again.

[XLGibbs]

Kind of hard to modify that code without a working sample. If you upload the file, I can make suggestions a bit easier.

With only 1 or 2 items, it would seem making columns the default choice would be an easier formatting choice as the resutling value calculation header would always be appearing as a column header...

You can wrap some of that existing code inside other checks, or put some other small subroutines to pass variables through as a means of checking the options chosen by the user.

[Djblois]

There is so much code. I created an add-in to be used on reports imported from our database software. If you give me your email address, I will send you the .xla file. It won't let me post it on the forum.

[XLGibbs]

There is so much code. I created an add-in to be used on reports imported from our database software.

Okay, so lets tackle them this way.

First, have you considered modifying it so that the default presentation is columns? That would eliminate the need for the extra code, and seems like a practical alternative for both coding it and ensuring consistent output..

In most cases, whether it is 1 or 2 or more data values calculationed, the columnar presentation is neat and easy..

Next... is PivotTableOptions the form and "Cases" "Units" etc are checkboxes?

It may be easier to have a multi-select list box (click an item to select)...where you could just For...each through the ListBox.Selected(item) array... possibly.

My first thought though would be to make columns the only output option.

[Djblois]

Yes, PivotTableOptions is the form and cases, units, etc... are checkboxes. I can't have columns as default, you get an error if you only chose one item. Also, I don't know how to use the multi-select list box. Thank you for all your help

[XLGibbs]

This might help a little. I took the logic you had, and just converted into one operation that handles the checkbox evaluations and the pivot table field options. At the end is your code, using a cbCounter variable which keeps track of how many options are checked off.

[vba]Sub SalesReportOptions()

Dim fm As UserForm
Dim cb As CheckBox
Dim strFieldName As String
Dim strColName As String
Dim fnType As Integer
Dim cbCounter As Integer
cbCounter = 0 ' reset the counter to zero here

Set fm = PivotTableOptions
For Each cb In fm ' lets loop through the checkboxes
If cb.Value = True and cb.Name <> "Year" Then Then 'only do it for the other checkboxes
cbCounter = cbCounter + 1 'increment the "True" count
' If the checkbox is checked, lets assign the relevant values
Select Case cb.Name
Case "Cases": strFieldName = "Cases": fnType = 1: strColName = "(Cases)"
Case "Units": strFieldName = "Units": fnType = 1: strColName = "(Units)"
Case "Amount": strFieldName = "Amt ($)": fnType = 1: strColName = "(Amount)"
Case "Cost": strFieldName = "Total Cost ($)": fnType = 1: strColName = "(Total Cost)"
Case "UnitCost": strFieldName = "Unit Cost ($)": fnType = 2: strColName = "(Unit Cost)"
Case "Profit": strFieldName = "Profit ($)": fnType = 1: strColName = "(Profit)"
Case "Price": strFieldName = "Price ($)": fnType = 2: strColName = "(Average Price)"
Case "ProfitPerc": strFieldName = "%": fnType = 2: strColName = "(Profit %)"
Case Else
End Select
' we will also do this if we see the box is checked
With pt.PivotFields(strFieldName)
.Orientation = xlDataField
Select Case fnType
Case 2: .Function = xlAverage
Else: .Function = xlSum
End Select
.NumberFormat = "#,##0"
.Name = strColName
End With

End If 'end of the If cb.Value = True loop
Next cb

If cbCounter = 0 Then
MsgBox "You must select at least one Value to calculate!"
Set fm = Nothing
Exit Sub
End If

If cbCounter > 1 Then
If PivotTableOptions.Year.Value = True Then
With pt.DataPivotField
.Orientation = xlColumnField
.Position = 2
End With
Else
With pt.DataPivotField
.Orientation = xlColumnField
.Position = 3
End With
End If

Set fm = Nothing

End Sub[/vba]



Below is a just a simple function you can try to count how many checkboxes are checked. It would be pretty simple to tie that to an if statement (Such as IF CheckboxCount> 1 then 'do stuff' else 'do other stuff')


[vba] Function CheckBoxCount()
Dim cb As CheckBox
Dim fm As UserForm
Dim howManychecked As Integer

howManychecked = 0

Set fm = PivotTableOptions
For Each cb In fm
If cb.Value = True Then howManychecked = howManychecked + 1
Next cb

Set fm = Nothing

CheckBoxCount = howManychecked

End Function[/vba]

Hope that helps. Best I could without the actual material to test with...

No promises on it actually working perfectly, but it should be pretty close.

[Djblois]

I keep getting this error "Object doesn't support this property or Method" on this line of code:

[VBA]For Each cb In fm[/VBA]

[XLGibbs]

Okay, just had to re-read the code I wrote to make sure.

Change
[VBA]
For each cb in fm
[/VBA]

to
[VBA]
For each cb in fm.Checkboxes [/VBA]

[Djblois]

Now I have this and it doesn't give me an error but when I uncheck all of them the message box doesn't pop up:

[VBA]DoEvents
Dim cb As CheckBox
Dim fm As UserForm
Dim howManychecked As Integer

howManychecked = 0

Set fm = PivotTableOptions
For Each cb In fm.CheckBoxes
If cb.Value = True Then howManychecked = howManychecked + 1
Next cb

Set fm = Nothing

If howManychecked = 0 Then
MsgBox "You must select at least one value to view!"
GoTo nonechecked
End If
Set fm = Nothing[/VBA]

[XLGibbs]

Sorry, I am way off today...must be distracted.. I forgot that you can't loop through the controls that way, and that you have to loop through all controls, check for type first, then do stuff...

see if this produces better results.

DoEvents
Dim cb As Control
Dim fm As UserForm
Dim howManychecked As Integer

howManychecked = 0

Set fm = PivotTableOptions
For Each cb In fm.Controls
If TypeName(cb) = "Checkbox" then

If cb.Value = True Then howManychecked = howManychecked + 1
Next cb
End if

Set fm = Nothing

If howManychecked = 0 Then
MsgBox "You must select at least one value to view!"
Goto nonechecked
End If
Set fm = Nothing

[Djblois]

Lol, I appreciate all your help. You are doing a lot better then I would have done alone. I did have to clean up your code a little:

[VBA]Dim cb As CheckBox
Dim fm As UserForm
Dim howManychecked As Integer

howManychecked = 0

Set fm = PivotTableOptions

For Each cb In fm.Controls
If TypeName(cb) = "Checkbox" Then
If cb.Value = True Then howManychecked = howManychecked + 1
End If
Next cb

Set fm = Nothing

If howManychecked = 0 Then
MsgBox "You must select at least one value to view!"
GoTo nonechecked
End If
Set fm = Nothing[/VBA]

Unfortunately, it always runs through as if none are checked.

[XLGibbs]

Strange.

Well, the only thing different in what I typed and what I see on my screen that works is that it needs to be CheckBox instead of Checkbox (the Capitalization is important).


Are they checkboxes (Square with checkmarks) or are they OptionButtons (round)....figured I would ask.

I can successfully identify the checked boxes on a quick form with 3 of them.

See sample attached. JUst open the code window and run the form...

[Djblois]

It's cool I wasn't complaing. I am grateful for all your help. I am using checkboxes.

[XLGibbs]

Did you get it fixed? It was probably just the Checkbox vs CheckBox issue with the Capital B needed for a proper positive ID of the control type..

My sample should have worked for you as well..

[Djblois]

Your sample keeps on coming up as a blank workbook

[XLGibbs]

It is blank, just open the code window (Alt F11)

Click on the Userform1 in the project and hit the Play button or hit F5. The form will pop up...

[Djblois]

I have been out of this for a few days but I got it almost working now with your help. This is what I have so far. It counts it right the first time around but if it is 0 and starts it over, it still thinks it is 0 when I hit the button again.

[VBA]Private Sub CommandButton1_Click()
nonechecked:
Dim c
Dim i As Integer
On Error Resume Next
PivotTableOptions.Show
Application.ScreenUpdating = True
PivotTableOptions.Hide
DoEvents

i = 0
For Each c In PivotTableOptions.DataView.Controls
If TypeName(c) = "CheckBox" Then
If c.Value = True Then i = i + 1
End If

If i = 0 Then
MsgBox "You must select at least one value to view!"
GoTo nonechecked
End If
Next c
MsgBox i

Application.ScreenUpdating = False

End Sub[/VBA]

[Djblois]

never mind I figured it out. Such a stupid little mistake. Thank you for all your help