Up next in 10
Create an Interactive Dynamic Excel Chart with Real-Time Updates as You Scroll #VBA #Excel
2K views
Jul 6, 2024
00:00 Introduction 00:15 Modify Chart Data Range with VBA using Chart.SetSourceData Method 01:45 Enable the Developer Tab 02:00 Insert a Scroll Bar (Form Control) 02:52 Access and Modify Scroll Bar Properties with VBA (Current, Minimum, and Maximum Values) 04:38 Connect the Scroll Bar to VBA Code for Automatic Execution 05:03 Create a Dynamic Range Using the Scroll Bar Value so using the Scroll Bar Updates the Chart in Real-Time 05:58 Set the Chart's Category Axis with VBA 06:54 Completed Dynamic Chart in Action In this video I’ve explained how to change a chart's source data and category axes using VBA. I also covered how to insert a scroll bar and assign it a macro. Each time you use the scroll bar, it triggers a subroutine that gets the scroll bar value, manipulates it, and creates a new chart range based on that value. This process results in a dynamic chart that updates as you scroll. #Excel #VBA #Scrollbar #Charts #Graphs
View Video Transcript
0:00
Hi everyone. Did you know you can tweak your charts with VBA
0:03
For example, you can dynamically adjust the data source or set the charts axis
0:08
Today, we'll be using VBA to make our charts interactive. As we scroll, our chart will update automatically
0:14
Let's get started. To observe how VBA can dynamically modify the data range of a chart
0:20
press Alt F11 to open VBA Editor, insert a new module and create a subroutine called Update Chart
0:26
Inside this subroutine, insert the code shown on the screen. The sheet 1.chart objects identifies the chart object named chart 1 on the worksheet
0:35
The chart objects represents a collection of all the chart objects on that worksheet
0:39
and chart one specifies the particular chart object within that collection. The dot chart is used to access the chart itself
0:46
This allows us to perform actions and access properties related to the chart
0:50
Set source data source code assigns the data range for the chart, and sheet 1.Range specifies the range of cells from A1 to E5 on sheet 1 as the data source for the chart
1:00
Make sure to replace the chart name with the actual name of your chart object
1:05
If you're unsure, click on the chart and you'll find its name in the name box at the top left of the worksheet
1:10
You can also rename it as per your preference. Now, to see the magic happen, set a new source data range with fewer rows
1:17
Then, click inside the subroutine and press F5 to execute the code
1:22
Open your Excel sheet, and you'll notice the chart dynamically adjusting its data range as per your VBA instructions
1:28
Each time you adjust the chart's data range in the VBA editor, these changes will be immediately reflected on the chart when the subroutine is executed
1:35
And next step, we'll integrate a scroll bar beneath the chart. This scroll bar will empower users to dynamically modify the cell range associated with the chart as they interact with it
1:45
To add the scroll bar go to the developer tab If it not visible click on the file tab choose options then customize ribbon Check the Developer tab in the list then click OK The Developer tab should now appear on
1:58
your ribbon. Now, go to Developer tab, click on Insert, and then choose Scrollbar under the Form
2:04
Control section. Draw the scroll bar below the chart on the worksheet. Right click on the scroll
2:08
bar and choose Format Control. The Format Control dialog box will appears. The current value refers
2:15
to the current position of the scroll bar within its defined range. For example, if the scroll bar has a minimum value of 1 and a maximum value of 100
2:23
its current value could be anywhere between 1 and 100, indicating its relative position within that range
2:29
In VBA, we will access this value to dynamically adjust the data range of a chart
2:33
This means that as we move the scroll bar, the charts data range will automatically update based on the scroll bar's value
2:39
We'll input 2 in the minimum value box and 13 in the maximum value box
2:43
as our charts data begins from row 2 and ends at row 13. We also can modify these values through VBA
2:49
when accessing the value of the scroll bar. To access a scroll bar in VBA, you must know its name
2:55
Simply click on it, and you'll find its name displayed in the name box
2:59
Let's set the minimum and maximum values of the scroll bar dynamically through VBA
3:03
The CodeSheet 1. Shapes identifies the scrollbar object named Scrollbar 2 on the worksheet named Sheet 1
3:10
The shapes object represents the collection of all shape objects on a worksheet
3:14
including scroll bars, images, etc. The control format property returns a control format object
3:20
which contains properties and methods specific to form controls that we saw when
3:24
right-clicking the scroll bar and choosing format control. The min property indicates the minimum value for the scroll bar
3:30
We assign two to it to match it with the first row number of our table
3:34
The max property indicates the maximum value for the scroll bar. We assign 13 to it to match it with the last with the last row number of our table Next we declare a variable named scroll value to store the current position of the scroll bar To streamline our code and avoid repetitive object naming
3:51
we'll utilize the with statement. The with statement allows us to perform multiple actions on the same object
3:56
without repeatedly specifying its name. It's particularly useful when we need to access properties
4:02
or methods of a single object several times within a block of code
4:05
Within the with statement, we'll simply write dot value to assign the code
4:09
current value of the scroll bar to the scroll value variable. This eliminates the need for a lengthy code line and ensures code readability
4:19
To test our code, we'll create a message box and write the scroll value variable to display
4:23
the current value of the scroll bar. Press F5 to run the subroutine
4:28
Excel will display a message box showing 2. Now, click the scrollbar arrows to change its position, then run the subroutine again
4:35
Excel will display the new current value of the scroll bar. So automate the execution of the subroutine when we interact with the scrollbar, right-click
4:42
on the scroll bar and click Assign Macro, then choose Update Chart
4:46
Now, whenever we use the scroll bar, the Subroutine will automatically execute, and Excel
4:51
will show the current value of the scroll bar in the message box. This method creates a custom scroll bar event, so whenever the scroll bar changes its position
4:58
the VBA code responds accordingly. Go back to the editor, remove the message box, and relocate the code that changes the charts
5:08
data source after the scroll bar code chunk. This adjustment allows us to manipulate the scroll
5:12
value variable to create a new data range for the chart. Next, declare a new variable named
5:18
chart range to hold the updated chart range based on the scroll bar value. Assign chart range
5:22
variable a range is shown on the screen. The An Sign concatenates the letter A with the value
5:27
of the scroll value variable and then concatenates E with the same value This creates a range starting from column A and ending at column E with the row number determined by scroll value Finally assign the chart range variable to the set source data method
5:42
Now, whenever we use the scroll bar, the subroutine runs. It retrieves the current value of the scroll bar, manipulates it to create a new dynamic
5:50
data range, and assigns it to the chart. This entire process creates a dynamic chart that changes its source based on the scrollbar position
5:58
Notice that the chart is displaying numerical values instead of category. labels on the X-axis, indicating that Excel is interpreting your data as numerical rather than
6:06
categorical. To address this issue, insert a new code line to update the charts axis. First
6:11
we modify the code line that adjusts the charts data range to use the with statement. This
6:16
streamlines our code and prevents repetition. Next, we add a new line of code. The axis object
6:22
represents all the axes in the chart, and Excel category is a constant for the category axis
6:27
The category name's property sets the labels for the categories on this axis, which appear as the labels on the X axis of your chart
6:34
The new line of code sets the category labels for the chart's category axis to the values in cells A1 to E1 on sheet
6:41
This allows you to dynamically specify the category labels based on the data in your worksheet
6:47
Close the VBA editor and use the scroll bar. You will see that the numbers on the category axis are now replaced with the headings from the data table
6:54
Hope you enjoy this video. I've explained how to change a chart's source data and category axes using VBA
7:00
I also covered how to insert a form control scroll bar and assign it a macro
7:05
Each time you use the scroll bar, it triggers a subroutine that gets the scroll bar value
7:09
manipulates it, and creates a new chart range based on that value. This process results in a dynamic chart that updates as you scroll
7:16
Thank you for watching. If you enjoyed this tutorial, please like, share, and subscribe to our channel
7:22
If you have any questions, feel free to ask in the comments below