Solved: Userform Option Button Issues

[Phelony]

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

[p45cal]

There's nothing between
Else
and
End If

If
JQSOption.Value = False
then no code operates, leaving it the same as it was before.
So once the option button has been True and the macro run, there's nothing that will change if it's False.

If that's not the problem, check that it's operating correctly; put a break (F9) on some lines, eg. at
filter1 = ListBox1
and
If JQSOption.Value = True Then
then use F8 to step through the code to ensure it's branching in the right direction as you expect it to.

[Aflatoon]

Is that code in the userform?

[Phelony]

Setting to .value worked.

However, the schoolboy error was that the command button called an externally held sub and so it had no idea if JQSOption was True or False in the first place.

Currently hitting myself over the head... :banghead:


Thanks for the guidance guys, sorry it was such a stupid error in the first instance! :beerchug:


Like I said, couldn't see the wood for the trees!!