Interactive Chart Creation.
Choose on-the-fly where you want your chart to be drawn, and where to find the data you want to plot. InteractiveChartCreation.zip is a zipped Excel file with an easy demonstration of these steps.
This floating command bar is created when the workbook is opened, and destroyed when the workbook is closed. Go to the Visual Basic Editor (Alt-F11) to see how. Details are in the ThisWorkbook Class Module and in the modMenu Code Module.
The Create A Chart button brings up two input boxes. The first asks the user to define a worksheet range where the chart is to be drawn:
The input box is opened by the following VBA command, which includes the prompt within the input box, the title of the input box, and the type of data being entered (type 8 means a worksheet range):
Set myChtRange = Application.InputBox( _ Prompt:="Select a range where the chart should appear.", _ Title:="Select Chart Position", Type:=8)
The user selects this range by dragging the mouse. When the user clicks on Okay, the variable myChtRange is assigned to the selected range. The second input box asks the user to define another worksheet range (again with the mouse) that contains the data to be plotted:
The second input box is opened by this VBA command:
Set myDataRange = Application.InputBox( _ Prompt:="Select a range containing the chart data.", _ Title:="Select Chart Data", Type:=8)
The variable myDataRange is assigned to the new area selected by the user. The chart is created in the desired range using the selected data range. These two ranges can be on any worksheets, in any workbooks.
Next the chart is created, using the location and dimensions of myChtRange:
Set objChart = .ChartObjects.Add( _ Left:=myChtRange.Left, Top:=myChtRange.Top, _ Width:=myChtRange.Width, Height:=myChtRange.Height)
Finally, the new chart's source data is assigned to myDataRange:
With objChart.Chart .SetSourceData Source:=myDataRange End With
In this last step a handful of other chart parameters are set. Go to the Visual Basic Editor (Alt-F11) to see these. Details are in the modChart Code Module.
You can download the Interactive Chart Creation routine described above, and a similar Interactive Chart Resizer routine, from the convenient links below. Or you can scroll to the VBA listings below the links, copy the code, and paste it into a regular code module in the VB Editor.
This is the CreateChart procedure in the modChart Code Module of the InteractiveChartCreation.xls workbook.
This is the SizeTheChart procedure in the InteractiveChartResizer.xls workbook.
Peltier Technical Services, Inc., Copyright © 2017. All rights reserved.