Toggle Series within a Chart using VBA

K

KristianWalker1993

New Member
Joined
Apr 9, 2018
Messages
2
Good Morning Folks,

I am looking to toggle a chart series using VBA and user-form to select specific series, I've had success with a line chart. However when I try to use a bar chart, it does not seem to update the data, only the legend.The VBA code I am using is:Module:


Code: 
[LEFT][COLOR=#101094][FONT=Consolas]Option[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Explicit
[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] ChartContent[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]()[/FONT][/COLOR][COLOR=#858c93][FONT=Consolas]'Excel VBA process to select the chart and show the userform.[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
ActiveSheet[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]ChartObjects[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Select[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] 
ufChart[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Show 

[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][/LEFT]

Userform:

Code: 
[LEFT][COLOR=#101094][FONT=Consolas]Option[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] Explicit

[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Private[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] cmdApply_Click[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]()[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
    [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Dim[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] iSres [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]As[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Integer[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]    Application[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]ScreenUpdating [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#7d2727][FONT=Consolas]False[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]

    [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]With[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] ActiveChart
        [/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]HasLegend [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#7d2727][FONT=Consolas]False[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
        [/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]HasLegend [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#7d2727][FONT=Consolas]True[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
        [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]For[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] iSres [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]SeriesCollection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Count [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]To[/FONT][/COLOR][COLOR=#7d2727][FONT=Consolas]1[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Step[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]-[/FONT][/COLOR][COLOR=#7d2727][FONT=Consolas]1[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
            [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]If[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] ListBox1[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Selected[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]iSres [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]-[/FONT][/COLOR][COLOR=#7d2727][FONT=Consolas]1[/FONT][/COLOR][COLOR=#303336][FONT=Consolas])[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Then[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
                [/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]SeriesCollection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]iSres[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Border[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]LineStyle [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] xlAutomatic
                [/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]SeriesCollection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]iSres[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]MarkerStyle [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] xlAutomatic
            [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Else[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
                [/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]SeriesCollection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]iSres[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Border[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]LineStyle [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] xlNone
                [/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]SeriesCollection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]iSres[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]MarkerStyle [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] xlNone
                [/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Legend[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]LegendEntries[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]iSres[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Delete
            [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]If[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
        [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Next[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
        [/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Deselect
    [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]With[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
    Unload [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Me[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]

[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Private[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] cmdCancel_Click[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]()[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
  Unload [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Me[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]

[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Private[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] ListBox1_Click[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]()[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]

[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]

[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Private[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] UserForm_Initialize[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]()[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
    [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Dim[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] iSres [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]As[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Integer[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]

    [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]With[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] ActiveChart
        [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]For[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] iSres [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#7d2727][FONT=Consolas]1[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]To[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]SeriesCollection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Count
            ListBox1[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]AddItem [/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]SeriesCollection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]iSres[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Name
            ListBox1[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Selected[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]ListBox1[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]ListCount [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]-[/FONT][/COLOR][COLOR=#7d2727][FONT=Consolas]1[/FONT][/COLOR][COLOR=#303336][FONT=Consolas])[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Not[/FONT][/COLOR][COLOR=#303336][FONT=Consolas](.[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]SeriesCollection[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]([/FONT][/COLOR][COLOR=#303336][FONT=Consolas]iSres[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]).[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]Border[/FONT][/COLOR][COLOR=#303336][FONT=Consolas].[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]LineStyle [/FONT][/COLOR][COLOR=#303336][FONT=Consolas]=[/FONT][/COLOR][COLOR=#303336][FONT=Consolas] xlNone[/FONT][/COLOR][COLOR=#303336][FONT=Consolas])[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
        [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Next[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]
    [/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]With[/FONT][/COLOR][COLOR=#303336][FONT=Consolas]

[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]End[/FONT][/COLOR][COLOR=#101094][FONT=Consolas]Sub[/FONT][/COLOR][/LEFT]

Would anyone have any suggestions on why this may be occurring and possible solutions so I could apply this VBA to a bar chart, I am not quite sure as to why its not updating with a bar chart.Many thanks,<strike></strike>
​
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.
Sort by date Sort by votes
Hi Kristian,

To hide a series in a Bar Chart, use
Code: 
 .SeriesCollection(iSres).Format.Fill.Visible = msoFalse   ' msoTrue to show
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Locked
  • Question
Toggle Series within a Chart using VBA
Replies
1
Views
580
Fluff
Fluff
S
  • Question
Horizontal Average Line on Vertical bar Chart
Replies
1
Views
621
Sherifa
S
B
  • Question
Color Charts in a specific style
Replies
2
Views
526
bobsell
B
B
  • Question
Creating a stacked bar chart in Excel using VBA
Replies
4
Views
21K
B416
B
Z
  • Question
Formula works for Line chart but not Bar chart.
Replies
2
Views
470
ZOwNER
Z

Forum statistics

Threads
1,215,757
Messages
6,126,695
Members
449,331
Latest member
smckenzie2016

Share this page

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