Detecting Change of List Box

Hi All,

I have a workbook with several worksheets. I have this one worksheet that has charts and a list box on it. I can get the data to change in the charts after I click the refresh button. I would like to write some VBA code so the charts change after the user changes the items in the list box. I've tried the following code, but it doesn't work. Nothing happens. I've never worked with a Private Sub before. Maybe I'm doing something work or leaving something out.


Private Sub Worksheet_Change(ByVal Target As Range)
Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache.Refresh
End Sub

CK76,

Thanks for the reply. I figured I needed to capture the change in the list box, but haven't figured it out yet. I do not think I have either. I created the list box by going to the Data Validation tab.

Calvin

Ah ok then. For data validation list, Worksheet_Change is correct event.

Assuming Data validation is in separate sheet (sheet2) from PivotTable (sheet1).

In Sheet2 module. Data validation in Cell B2
0.JPG

Please Login or Register  to view this content.

If it is in the same sheet. Replace Worksheets("Sheet1") with Me. And put the code in the sheet module where PivotTable is located.

CK76,

Thanks. I'll try that and let you know the results. Have a GREAT weekend.

Calvin

CK76,

Thanks! That worked like a charm!!

Calvin