multi select Listbox to populate data fields in excel

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
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,224,585
Messages
6,179,704
Members
452,938
Latest member
babeneker

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top