Phelony
07-19-2011, 03:57 AM
Looking for a little help here as I'm stuck to the point of aggrivated assault against my computer.
I've got the following code which is supposed to take instructions from a userform to command a pivot table.
Currently, I'm trying to get the JQSOption button to function, but I'm having some difficulty. I hit the execute button and it takes out the old fields, but completely ignores that the optionbutton is set to True.
The Listboxes (imaginatively named listbox1 and listbox2) contain a list of all the permissable rown options and this works fine, however, when I'm trying to control this as a "quick search" via the option buttons, it's doing absolutely nothing.
I imagine I've missed something, but I've got myself in a "can't see the wood for the trees" situation :bug: and wondered if anyone could cast an idea as to what I've missed?
Sub pivotform()
'PIVOT CONTROLS
Dim filter1 As String
Dim filter2 As String
Dim initial1 As String
Dim initial2 As String
'Dim JQSOption As Object
filter1 = ListBox1
filter2 = ListBox2
initial1 = Range("A3")
initial2 = Range("B3")
ActiveSheet.PivotTables("PivotTable1").PivotFields(initial1). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields(initial2). _
Orientation = xlHidden
'Option button 1 variation
If JQSOption.Value = True Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Legal Entity")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("FRL Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Legal Entity")
.PivotItems("JY_BPBJ").Visible = True
.PivotItems("JY_BWTJ").Visible = True
.PivotItems("IM_BPBI").Visible = False
.PivotItems("IM_BWTI").Visible = False
.PivotItems("GY_BWFG").Visible = False
.PivotItems("GY_BWTG").Visible = False
.PivotItems("AA_BWTH").Visible = False
.PivotItems("AA_BWTS").Visible = False
End With
Else
End If
Unload OptionsMenuForm
End Sub
This will eventually contain another 3 options as well as the listbox functionality all to be controlled by a single command button on the form.
Any help\guidance would be appreciated.
Many thanks
Phel
I've got the following code which is supposed to take instructions from a userform to command a pivot table.
Currently, I'm trying to get the JQSOption button to function, but I'm having some difficulty. I hit the execute button and it takes out the old fields, but completely ignores that the optionbutton is set to True.
The Listboxes (imaginatively named listbox1 and listbox2) contain a list of all the permissable rown options and this works fine, however, when I'm trying to control this as a "quick search" via the option buttons, it's doing absolutely nothing.
I imagine I've missed something, but I've got myself in a "can't see the wood for the trees" situation :bug: and wondered if anyone could cast an idea as to what I've missed?
Sub pivotform()
'PIVOT CONTROLS
Dim filter1 As String
Dim filter2 As String
Dim initial1 As String
Dim initial2 As String
'Dim JQSOption As Object
filter1 = ListBox1
filter2 = ListBox2
initial1 = Range("A3")
initial2 = Range("B3")
ActiveSheet.PivotTables("PivotTable1").PivotFields(initial1). _
Orientation = xlHidden
ActiveSheet.PivotTables("PivotTable1").PivotFields(initial2). _
Orientation = xlHidden
'Option button 1 variation
If JQSOption.Value = True Then
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Legal Entity")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("FRL Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Legal Entity")
.PivotItems("JY_BPBJ").Visible = True
.PivotItems("JY_BWTJ").Visible = True
.PivotItems("IM_BPBI").Visible = False
.PivotItems("IM_BWTI").Visible = False
.PivotItems("GY_BWFG").Visible = False
.PivotItems("GY_BWTG").Visible = False
.PivotItems("AA_BWTH").Visible = False
.PivotItems("AA_BWTS").Visible = False
End With
Else
End If
Unload OptionsMenuForm
End Sub
This will eventually contain another 3 options as well as the listbox functionality all to be controlled by a single command button on the form.
Any help\guidance would be appreciated.
Many thanks
Phel