ListBox VBA Question

[PaulF]

Hello ALL!

I have a simple need that I can't figure out

I have a created a DashBoard (Below) and everything you see is working perfectly based on listbox (queue) selection.

I want to add a feature to show the top 10 sub ticket type based on the queue selection. I am using a piviot table to get the top 10 based on a help field triggered off the ListBox selection... I just need it to update when I change the selection...

ListBox1 on Sheet INF-DSK YTD Summary << When a Work Queue is selected I have a nested If(AND function on a data sheet that updated a helper field to a 1 or a 0
PiviotTable1 on Sheet Piviots << Filvers Top 10 based on that helper field in the data table

When I make a new selection in ListBox1 I would like the PiviotTable to refresh.

I am right clicking on the ListBox`1 sheet and entering the following code:

Private Sub ListBox1_Click()
Sheets("Pivots").PivotTables(1).RefreshTable
End Sub

Please not that I am brand new on the VBA side...

Thank you for any assistance you can provide.

Respectfully,
Paul F

 

[Luke M]

Hi Paul,

What part is giving you trouble? The Dashboard looksk very nice overall. IMO, I'd dock a few points for using 3d, exploding, non-vertically aligned pie charts but I know some people prefer the look rather than info. The VB you wrote appears to work fine, assuming the sheet name is correct and your PT is the first PT on the sheet.

If it's actually a filter in the PivotTable that needs to get updated, rather than refreshing from the data source, I would recommend recording yourself changing that filter to see what syntax you need. At the very least, it would help us see what needs to be changed.

 

[PaulF]

Luke M... Thank you for the reply... The Piviot Table is not refreshing, based on the above, and I do not get any errors or debug box.

If I change the code:

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets("Pivots").PivotTables(1).RefreshTable
End Sub

Then type a 5 in a box, the pivot table on the "Pivots" worksheet updates to reflect the changes I've made in the ListBox... I need it to do the same when I click on a selection in ListBox1

 

[Luke M]

Hmm. Taking it to basics, is it a ListBox from the ActiveX menu, or from Forms menu? The latter won't work.

The next check would be to put a stop in the code (either hit F9 to put a flag, or type a line saying "Stop" in the code. Then go back to trying to click on the listbox. This would check to see if the code is even getting fired off.

Next question would be to make sure EnableEvents is still turned on. Since the change_event fired, I would assume so, but I'm starting to stretch at this point.

 

[PaulF]

Luke, my list box is a form control, not an Active X control...

So... Looks like I need to go redo this as an Active X and see how the code works

Thank you Sir !!

 

[Luke M]

Phew, glad it was the first one. It's the easiest one to fix at least.
Good luck with the changes, and you're very welcome.

 

[PaulF]

Luke M !! Take a look see... in the bottom right there is a top 10 (That management asks for every week) subcategory box. It is linked to a Pivot table.

Question on the ActiveX Control... When I had the Form Control in, I could click and the speed was great (almost instant to update all with 50k data table). Now with the ActiveX Control in, it take 3-4 seconds to update and graphics... Any thought there ??

 

[PaulF]

Also when you scroll the page and ActiveX control seems to lag a little when scrolling up and down...

 

[NARAYANK991]

Hi Paul ,

Is there any reason you have replaced a Form control with an ActiveX control ?

The point is that you have used a ListBox_Click event where the correct event procedure might be a ListBox_Change procedure. Have you tried this ?

Narayan

 

[PaulF]

Narayank991, I tried every inserted Form Control I could and none of them seem to be able to trigger an event...

I even tried to used the macro recorder with a series of them and made selections and nothing appeared in the code... supporting the above conclusion...

 

[NARAYANK991]

Hi Paul ,

I still am not sure of what your conclusion is ; can you see the uploaded file ?

Narayan

 

[Luke M]

Kudos to Narayan. I forgot that you can assign a macro to be fired when you click on a Forms object. It's a bit misleading, as even though you can call the assigned macro "ListBox1_Change" it's not really a change event. You could call the macro "FunTimes" and assign it, and the macro would still work, but both times it's getting called because the macro is assigned to run when you click (just like on a command button) rather than because of it's name (like event macros). Still, my apologies for forgetting this.

So, back to your original setup, with a Forms control, and your macro, if you right-click on the ListBox form, you can click on 'assign macro', and select the required macro. That should get things working.

PS. I would suggest making the ListBox big enough, if possible, so that you don't need to scroll. Otherwise, each click on the scroll bar will be firing the macro, which may not be desired.