Up next in 10
Change Chart Type Dynamically in Excel Using Combo Box and VBA
9K views
Feb 7, 2025
Learn how to make your Excel charts interactive by using a combo box and VBA to dynamically switch between different chart types. In this step-by-step tutorial, you'll discover how to configure a combo box, link it to a cell, and write VBA code that updates the chart type based on your selection. Whether it's Line, Pie, Area, or even an Exploded Pie chart, you'll see how easy it is to customize and expand functionality. Perfect for automating and enhancing your Excel reports!
View Video Transcript
0:00
in this video I'll show you how to
0:01
create an interactive chart in Excel
0:03
using a combo box with this setup you
0:06
can easily switch between different
0:08
chart types like line bar and pie charts
0:10
by simply selecting an option from the
0:12
combo box this is perfect for dashboards
0:14
and dynamic presentations to accomplish
0:17
this we need VBA ensure the developer
0:20
tab is visible on your Ribbon if not
0:22
right click the ribbon select customize
0:25
ribbon and check developer in the
0:26
options once done the developer tab will
0:29
appear on on your Ribbon as demonstrated
0:31
on screen now insert a chart by
0:34
selecting your data go to the insert Tab
0:36
and choose the chart type you prefer
0:38
place the chart on the worksheet where
0:40
it's easily visible in Excel every chart
0:43
is an object with a default name like
0:45
chart one which appears in the name box
0:47
when selected you can rename it but
0:49
we'll stick with chart one for now as
0:52
we'll use this name to modify the chart
0:53
type with VBA next open the VBA editor
0:57
go to the developer tab and in the code
0:59
group click Visual Basic this will
1:01
launch the VBA Editor to write the code
1:04
insert a new module by going to the
1:06
insert menu and selecting module this
1:08
will open a blank window labeled module
1:10
one we'll enter the VBA code to modify
1:13
the chart type create a sub routine
1:15
named change chart type and insert the
1:17
code into it as shown on the screen this
1:20
code works as follows active sheet
1:22
refers to the current worksheet chart
1:24
objects targets the chart named chart
1:26
one chart. chart type equals XL Pi
1:29
changes the chart type to a pie chart
1:31
now Run The Code by clicking the play
1:33
button or pressing F5 you'll see the
1:35
chart update to a pie chart to explore
1:38
further replace XEL Pi with XL line in
1:41
the code and run it again the chart will
1:43
now change to a line chart similarly
1:46
replace it with Excel area and run the
1:48
code to switch the chart type to an area
1:50
chart now you understand how VBA can
1:53
change the chart type to make this
1:55
process interactive we'll use a combo
1:57
box go to the developer tab and in the
2:00
controls group click insert and select
2:02
combo box under form control draw the
2:04
combo box in an empty area near or over
2:07
the chart next configure the combo box
2:09
using format control by providing an
2:11
input range and a cell link for the
2:13
input range list three chart names line
2:17
pi and area in cells D2 to D4 these
2:21
values will appear in the combo box when
2:23
clicked for the cell link use cell D7
2:26
when you select an item from the combo
2:28
box it will return a corresponding
2:29
number number one for the first item two
2:32
for the second and so on this number
2:34
will be written in cell D7 to set this
2:36
up right click the combo box and select
2:38
format control in the control tab enter
2:41
D2 D4 as the input range and D7 as the
2:45
cell link click okay to save the
2:47
settings now try selecting items from
2:49
the combo box for example selecting line
2:52
will return one in cell D7 Pi will
2:55
return two and area will return three
2:58
next right click the combo box again and
3:00
choose assign macro in the assign macro
3:02
dialogue box select the chain chart type
3:04
macro and click okay at this point the
3:07
combo box is linked to the VBA code
3:09
whenever you select an item from the
3:11
combo box it will automatically run the
3:13
change chart type sub routine to test
3:15
this open the VBA editor and change the
3:18
chart type in the code from Excel area
3:20
to excel line return to the Excel Window
3:23
select any option from the combo box and
3:25
watch the chart type update accordingly
3:27
based on your code notice something the
3:29
drop down changes the chart type only
3:31
once to make it update dynamically based
3:34
on each selection we need to read the
3:36
value in cell D7 which updates whenever
3:38
a different option is selected in the
3:39
combo box in the VBA editor we'll use a
3:42
select case statement as shown on screen
3:44
the select case statement reads the
3:46
value in D7 since D7 is linked to the
3:50
combo box Excel writes the index number
3:52
of the selected item to this cell
3:54
whenever an option is chosen for example
3:57
selecting the first option in the combo
3:58
box writes one to D7 the second option
4:01
writes two and so on here's how the code
4:03
Works to determine the selected option
4:06
this code checks the value in D7 if it's
4:08
one a message box displays line if it's
4:11
two it displays Pi if it's three it
4:15
displays area now our code is responding
4:18
to changes in the combo box selection
4:21
it's time to make the chart type update
4:23
dynamically based on the selected option
4:25
to do this we need to edit our sub
4:27
routine and declare a new variable chart
4:29
type type that will store the chart type
4:31
value we use as integer because chart
4:33
types like Exel line XEL pi and Xcel
4:37
area are represented internally as
4:39
integer constants now replace the
4:42
message box lines with chart type equals
4:44
XEL line for case one chart type equals
4:47
XL Pi for case 2 and chart type equals
4:50
XL area for case 3 finally update the
4:53
last line of the code which is
4:55
responsible for changing the chart type
4:57
assign at the value stored in the chart
4:59
type variable
5:00
this ensures that the chart type
5:01
variable updated by the select case
5:03
statement determines the chart type
5:05
based on the combo box selection linked
5:07
to D7 now go back to the Excel Window
5:10
select different options from the combo
5:12
box and you'll see the chart type change
5:14
instantly based on your selection you
5:16
are not limited to just the chart types
5:17
we've used so far you can easily add
5:20
more chart types to the combo box later
5:22
let's add a new chart type called Pi
5:24
exploded and cell D5 to include it in
5:26
the list of chart types next edit the
5:28
input range of the combo box in the
5:30
format control dialog box to include the
5:32
updated range so the new Option appears
5:35
in the drop down now let's update our
5:37
code add another case to the select case
5:39
block here XL Pi exploded is the VBA
5:42
constant for the exploded Pi chart type
5:44
go back to the Excel Window and select
5:47
Pi exploded from the combo box the chart
5:49
will now change to the exploded Pi type
5:52
demonstrating how easy it is to expand
5:54
functionality with VBA and form controls
5:57
in this tutorial you learned how to use
5:59
a combo box and VBA to dynamically
6:02
modify chart types in Excel you explored
6:04
linking a combo box to a cell
6:06
configuring it to display chart options
6:09
and using VBA to read the selected
6:11
option and update the chart accordingly
6:13
we also saw how to expand functionality
6:15
by adding new chart types to the combo
6:17
box and updating the VBA code to handle
6:19
them thank you for watching please share
6:21
this video like subscribe and hit the
6:24
notification Bell for more Excel
6:25
tutorials see you in the next video
#Educational Software
#Programming