I have workbook in MS Office which contains several charts where I can ‘move’ the range of an axis by using a slider to change values in a cell, eg B21, have another cell (C21) which is say B21+150 and then use a macro to change the MinimumScale and Maximumscale on the chart according to the contents of B21 and C21.
The macro doesn’t work if I try to use the slider in Calc .
I get a Runtime Error, Type:com.sun.star.container.NoSuchElementException presumably as it doesn’t recognize ‘Minimumscale’ or ‘MaximumScale’
Is there an alternative or, more likely, a better way to get my sliders to work in Calc?
Added upload
I suppose that the Excel macros will not run in the Calc properly even if you use the compatibility options.
Yes, you can do it by usage your own StarBasic+API macros. You must get the chart by your macro, and you must get the slider values, and then you must set the axis scale values by the API functions.
Here is a sample file with embedded macro snippets:
Thanks for the response Zizi.
I’m afraid I know very little about the compatibility options and even less about StarBasic!
I guess you are saying I should remove my charts and rebuild them by using macros and then adjust the axis by API?
As most are 20x23 line charts this could be a bit of a job and a task currently beyond my capabilities. Some serious ‘Googling’ will be required to bring me up to speed on what started out as adding a slider to a chart as a bit of fun!
You can see some lines in my macro code with rem-ed command 'xray…
The XrayTool is an extension for in-situ examining the programming objects.
Download and install one of the excellent object inspection tools: MRI or XrayTool - if you want to work with the macros efficiently.
"I guess you are saying I should remove my charts and rebuild them by using macros "
Not. You can control any charts by the StarBasic+API macros even if they was created manually.
Always use the native ODF file types - if you want use the macros of the LibreOffice.
Please upload your ODF type sample file here.
Don’t know if you have to had a certain number of posts on here before you can upload a file?
Got the knowledge with upload, had to answer!
You can upload attachmets at your first post (at the “question”). Edit your question, and use the attachment icon.
Yes, realised that Zizi
Apologies, I hacked a lot of the content out so there was not a lot of sense left to the chart but the basics are there.
Will take a while to look at your upload in detail and take a couple of hours off to watch the football
!
Well, that was disappointing. Still, Italy were the better team.
I have added a more complete version of my workbook, hope it makes more sense.
Your sample document was converted from .xls or .xlsm file format, and it is full of unusable styles, macro directories. Please clear up them from the document.
Well yes, Zizi, that was pretty much the object of the exercise! :-:
I have an Excel workbook that I want to move over to Libre and the first thing I noticed that glaringly didn’t work was the macro for the sliders.
Last night wasn’t exactly the “Match of the Century”, which was of course England v Hungary in 1953. I was 6 and remember it well (not)!
Regards and stay safe
Have changed my VBA Project macro code to
Private Sub Sheet6ScrollBar3_Change()
With Sheets(“FIA Championships Charts”).ChartObjects(“Chart 1”).Chart
With .Axes(xlValue, xlPrimary)
automax = False
Min = Sheets(“FIA Championships Charts”).Range(“B19”)
Max = Sheets(“FIA Championships Charts”).Range(“B20”)
End With
End With
End Sub
… now the Slider increments and decrements cell B19, doesn’t error but doesn’t change the chart.
Progress I suppose
Forget your VBA project. Study my sample file and the embedded StarBasic+API macros. See it above, at my answer.
Your sample uses two sliders and the macro directly references each slider setting for its max and min values. Can it instead reference the contents of two cells? I can then use one slider instead of two. Are min and max referenced in a manual/tutorial anywhere?
“Can it instead reference the contents of two cells?”
Yes, you can reference to cells too by your macro. See it in my macro: the labels of the axes are referenced to strings of two NAMED cells. And you can reference to unnamed cells, and their numeric values too.
Thanks for the info
Hi Tibor, hope you are well.
Have been overtaken by events, not been 18 months working on this!
I have just re-visited it and have, with the help of another post (61596), Y axis max and min working OK with a couple of Push Buttons . I am trying to tidy it up with a scroll bar which puts the required Max and Min for the axis into cells F12 and F13 but still having a problem getting the contents of the cells into the Axis. The Macro so far…
Sub Scrollbar2click
Dim oSheet As Variant
Dim oCharts As Variant
Dim oChart As Variant
Dim oEmbeddedObject As Variant
Dim oDiagram As Variant
Dim oAxis As Variant
Dim oCellF12 as Variant
oSheet = ThisComponent.getCurrentController().getActiveSheet()
oCharts = oSheet.getCharts()
If oCharts.getCount() < 1 Then Exit Sub
oChart = oCharts.getByIndex(1) ’ Get First Chart or Enumerate all
’ of them with For i = 0 To oCharts.getCount()-1
oEmbeddedObject = oChart.getEmbeddedObject() ’ Get Object in selected frame
oDiagram = oEmbeddedObject.getDiagram() ’ Get diagram from it
Rem Now change values of any property - in this case XAxis and YAxis:
'oAxis = oDiagram.getXAxis()
’ oAxis.AutoMin = False
’ oAxis.AutoMax = False
’ oAxis.Min = oAxis.Min / 2
’ oAxis.Max = oAxis.Max * 1.5
oAxis = oDiagram.getYAxis()
oAxis.AutoMin = False
oAxis.AutoMax = False
oCellF12 = oSheet.GetCellRangeByName(“F12”)
oAxis.Min = oCellF12
End Sub
Which runs to the last line where it errors with “Incorrect Property Value”.
I guess I have not defined oCellF12 correctly, any ideas?
Regards
Uploaded .ods Apologies, it is a bit of a mess.
ScalingAxes3.ods (34.8 KB)