PDA

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



Djblois
12-26-2006, 01:24 PM
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
12-26-2006, 09:34 PM
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
12-28-2006, 08:22 AM
here is my code:

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

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
12-28-2006, 09:32 AM
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
12-28-2006, 09:44 AM
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
12-28-2006, 09:50 AM
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
12-28-2006, 11:14 AM
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
12-28-2006, 01:10 PM
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.

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



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')


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

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
12-28-2006, 01:50 PM
I keep getting this error "Object doesn't support this property or Method" on this line of code:

For Each cb In fm

XLGibbs
12-28-2006, 02:09 PM
Okay, just had to re-read the code I wrote to make sure.

Change

For each cb in fm


to

For each cb in fm.Checkboxes

Djblois
12-28-2006, 02:25 PM
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:

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

XLGibbs
12-28-2006, 02:39 PM
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
12-28-2006, 02:55 PM
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:

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

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

XLGibbs
12-28-2006, 03:28 PM
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
12-29-2006, 05:12 AM
It's cool I wasn't complaing. I am grateful for all your help. I am using checkboxes.

XLGibbs
12-29-2006, 08:54 AM
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
12-29-2006, 10:04 AM
Your sample keeps on coming up as a blank workbook

XLGibbs
12-29-2006, 10:07 AM
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
01-03-2007, 11:21 AM
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.

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

Djblois
01-03-2007, 11:24 AM
never mind I figured it out. Such a stupid little mistake. Thank you for all your help

XLGibbs
01-03-2007, 01:12 PM
Was it "Check Box" as opposed to "CheckBox" ?

Djblois
01-03-2007, 02:52 PM
no I had Next C after the check if it was zero. Thank you again