- #Dynamic xy scatter chart excel update
- #Dynamic xy scatter chart excel portable
- #Dynamic xy scatter chart excel code
- #Dynamic xy scatter chart excel series
MaximumScaleIsAuto = True If MinOrMax = "Min" Then. MinimumScaleIsAuto = True End If End With End If 'Set value of secondary axis If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _Īnd PrimaryOrSecondary = "Secondary" Then With cht.Axes(xlValue, xlSecondary)Įlse If MinOrMax = "Max" Then. MaximumScale = ValueĮlse If MinOrMax = "Max" Then. If IsNumeric(Value) = True Then If MinOrMax = "Max" Then. MinimumScaleIsAuto = True End If End With End If 'Set Category of Primary axis If (ValueOrCategory = "Category" Or ValueOrCategory = "X") _Īnd PrimaryOrSecondary = "Primary" Then With cht.Axes(xlCategory, xlPrimary)
MinimumScale = ValueĮlse If MinOrMax = "Max" Then. 'Set Value of Primary axis If (ValueOrCategory = "Value" Or ValueOrCategory = "Y") _ ValueOrCategory As String, PrimaryOrSecondary As String, Value As Variant)ĭim valueAsText As String 'Set the chart to be controlled by the function Set cht = .Sheets(sheetName) _ Function setChartAxis(sheetName As String, chartName As String, MinOrMax As String, _
#Dynamic xy scatter chart excel code
Here is the VBA code to copy into the Module. The code for UDFs must be within a standard module to work. The Visual Basic Editor window will open, click Insert -> ModuleĪdd the code below to the Module as shown. To create the UDF click Developer -> Visual Basic (or shortcut ALT + F11). Right-click on a blank part of the ribbon and select Customize the Ribbon… from the menuįrom the Excel Options window click Customize Ribbon, enable the Developer option then click OK.
#Dynamic xy scatter chart excel update
Whether these values are typed in the cell or created using formulas, they will update the chart. The values in the cell are automatically applied to the chart. The animated gif below shows the solution in action. Thankfully, the minimum and maximum values of the chart axis are controllable using a UDF. For example, it is possible to change a worksheet’s tab color, or to change a chart title (next week’s post). Whether on purpose or by accident, Microsoft have made it possible to control various objects with UDFs. UDF’s are intended to be custom worksheet functions to calculate a cell value. User Defined Functions (UDFs for short) are just like normal Excel functions, such as VLOOKUP or SUM, but they have been created using VBA.
#Dynamic xy scatter chart excel portable
I turned to my old friend VBA, and started to tinker. I decided to build a more dynamic solution. There are various chart objects we can link to worksheet cells source data, chart titles and data labels can all be linked to cells, but the chart axis is set by hardcoding a number into the Format Axis options window. It only takes a few seconds, but all that time starts to add up. It’s such a tedious task, and I know I’ll be doing it again at a future point. Attached to this post is a simple example.“ Not again…” I think to myself every time I change the minimum and maximum values of a chart axis.
#Dynamic xy scatter chart excel series
From creating formulas based on the PivotTable to inserting a blank chart and using the add series dialog to select the cells. Itâs just regular old cells now.Īs Colin states in one of the comments to the post, thereâs multiple ways to get a scatter chart on PivotTable data. Once itâs converted to formulas, you can create any chart type you want against it, no restrictions. Remember the post where I converted a PivotTable to Cube Formulas using that button on the ribbon? This is a wonderful opportunity to use that feature in an unexpected way. Try to use XY (Scatter), Bubble, or Stock as a PivotChart, and youâll get an alert saying you cannot create it with pivot data. But not all of them are supported as PivotCharts. Rob over at has a interesting post on scatter charts and PowerPivot:Īt right is a list of all chart types in Excel.