jslaughter
New Member
- Joined
- Jul 1, 2011
- Messages
- 1
Afternoon everyone...I'm completely new here, so i'm guessing what i'm going to ask has been covered as some point in time.
I have a multipage user form that has 4 multiselect listboxes on one of the pages...One of the list boxes is dependednt on the input from the other, and the other two are stand alone.
for this specific page, it creates two pivot tables based on the users input. To be specific, I am looking at forward curve variances and position variances for a trading company. The listboxes determine the internal strategy, the associated forward curve location, the Product type (either Gas, peak power, or off peak power), and the associated contract month.
The contract months span out till dec of 2018 and are the actual data fields.
I have the other 3 listboxes working well and returning what I want when selected for the pivot table creation, but I can't seem to get the code right for the date values. Below is what I have for the other 3, and it works fine.
'Strategy Selection
With PTPrice.PivotFields("Strategy")
.Orientation = xlRowField
.Position = 1
End With
Set StrategyPrice = ThisWorkbook.Worksheets("Interactive").PivotTables("Price Variance").PivotFields("Strategy")
For lbxStrat = 0 To lbxStrategyPrice.ListCount - 1
If lbxStrategyPrice.Selected(lbxStrat) = False Then
StrategyPrice.PivotItems(lbxStrategyPrice.List(lbxStrat)).Visible = False
Else
StrategyPrice.PivotItems(lbxStrategyPrice.List(lbxStrat)).Visible = True
End If
Next lbxStrat
'Curve Selection
With PTPrice.PivotFields("Curve")
.Orientation = xlRowField
.Position = 2
End With
Set LocationPrice = ThisWorkbook.Worksheets("Interactive").PivotTables("Price Variance").PivotFields("Curve")
For lbxLocation = 0 To lbxLocationsPrice.ListCount - 1
If lbxLocationsPrice.Selected(lbxLocation) = False Then
LocationPrice.PivotItems(lbxLocationsPrice.List(lbxLocation)).Visible = False
Else
LocationPrice.PivotItems(lbxLocationsPrice.List(lbxLocation)).Visible = True
End If
Next lbxLocation
'Product Selection
With PTPrice.PivotFields("Product Type")
.Orientation = xlRowField
.Position = 3
End With
Set ProductType = ThisWorkbook.Worksheets("Interactive").PivotTables("Price Variance").PivotFields("Product Type")
For lbxProduct = 0 To lbxProductType.ListCount - 1
If lbxProductType.Selected(lbxProduct) = False Then
ProductType.PivotItems(lbxProductType.List(lbxProduct)).Visible = False
Else
ProductType.PivotItems(lbxProductType.List(lbxProduct)).Visible = True
End If
Next lbxProduct
For the data, the .position would have to be dynamic, and the name of the field has to be a variable I'm assuming, based on the listbox i have named lbxDatePrice or lbxDatePos....ANY HELP is appreciated!!!! Thanks
I have a multipage user form that has 4 multiselect listboxes on one of the pages...One of the list boxes is dependednt on the input from the other, and the other two are stand alone.
for this specific page, it creates two pivot tables based on the users input. To be specific, I am looking at forward curve variances and position variances for a trading company. The listboxes determine the internal strategy, the associated forward curve location, the Product type (either Gas, peak power, or off peak power), and the associated contract month.
The contract months span out till dec of 2018 and are the actual data fields.
I have the other 3 listboxes working well and returning what I want when selected for the pivot table creation, but I can't seem to get the code right for the date values. Below is what I have for the other 3, and it works fine.
'Strategy Selection
With PTPrice.PivotFields("Strategy")
.Orientation = xlRowField
.Position = 1
End With
Set StrategyPrice = ThisWorkbook.Worksheets("Interactive").PivotTables("Price Variance").PivotFields("Strategy")
For lbxStrat = 0 To lbxStrategyPrice.ListCount - 1
If lbxStrategyPrice.Selected(lbxStrat) = False Then
StrategyPrice.PivotItems(lbxStrategyPrice.List(lbxStrat)).Visible = False
Else
StrategyPrice.PivotItems(lbxStrategyPrice.List(lbxStrat)).Visible = True
End If
Next lbxStrat
'Curve Selection
With PTPrice.PivotFields("Curve")
.Orientation = xlRowField
.Position = 2
End With
Set LocationPrice = ThisWorkbook.Worksheets("Interactive").PivotTables("Price Variance").PivotFields("Curve")
For lbxLocation = 0 To lbxLocationsPrice.ListCount - 1
If lbxLocationsPrice.Selected(lbxLocation) = False Then
LocationPrice.PivotItems(lbxLocationsPrice.List(lbxLocation)).Visible = False
Else
LocationPrice.PivotItems(lbxLocationsPrice.List(lbxLocation)).Visible = True
End If
Next lbxLocation
'Product Selection
With PTPrice.PivotFields("Product Type")
.Orientation = xlRowField
.Position = 3
End With
Set ProductType = ThisWorkbook.Worksheets("Interactive").PivotTables("Price Variance").PivotFields("Product Type")
For lbxProduct = 0 To lbxProductType.ListCount - 1
If lbxProductType.Selected(lbxProduct) = False Then
ProductType.PivotItems(lbxProductType.List(lbxProduct)).Visible = False
Else
ProductType.PivotItems(lbxProductType.List(lbxProduct)).Visible = True
End If
Next lbxProduct
For the data, the .position would have to be dynamic, and the name of the field has to be a variable I'm assuming, based on the listbox i have named lbxDatePrice or lbxDatePos....ANY HELP is appreciated!!!! Thanks