Position of X-Axis (Top, Left)

[jlaufs]

Hello,
I would to do the following in a Excel Macro:
I want to move a Button to right above the X-Axis of my chart.
This ist my code so far. No matter what I try, the button never
appears directly above the axis, but some inches higher.
What is my mistake?


ActiveSheet.ChartObjects("Diagramm").Activate
ActiveChart.PlotArea.Select
ActiveChart.Axes(xlCategory).Select
'Achse
achselinks = Selection.Left
achseoben = Selection.Top


'Plot
ActiveSheet.ChartObjects("Diagramm").Select
ActiveChart.PlotArea.Select
plotlinks = Selection.Left
plotoben = Selection.Top


'Chartobject
ActiveSheet.ChartObjects("Diagramm").Select
ActiveChart.ChartArea.Select
chartlinks = Selection.Left
chartoben = Selection.Top


'Hier wird der Button verschoeben
ActiveSheet.Shapes("Button").Select
ActiveSheet.Shapes("Button").Top = plotoben + achseoben +
chartoben
ActiveSheet.Shapes("Button").Left = achselinks + chartlinks

Thanks a lot!
Johannes

 

[Jon Peltier]

What kind of chart? If it's a horizontal bar chart, the X axis is the
vertical axis on the left side of the chart.

- Jon

 

[Vergel Adriano]

Try it this way:

With ActiveSheet.ChartObjects("Diagramm")
achselinks = .Chart.Axes(xlCategory).Left
achseoben = .Chart.Axes(xlCategory).Top

chartlinks = .Left + .Chart.ChartArea.Left
chartoben = .Top + .Chart.ChartArea.Top
End With

With ActiveSheet.Shapes("Button")
.Top = chartoben + achseoben - .Height
.Left = chartlinks + achselinks
.ZOrder msoBringToFront
End With

 

[jlaufs]

Hello Vergel Adriano,
thanks a lot for your answer!

Why does your solution only work, when my Zoom is 100% ?



Johannes

 

[jlaufs]

It is avertikal bar chart.

What kind of chart? If it's a horizontal bar chart, the X axis is the
vertical axis on the left side of the chart.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. -http://PeltierTech.com
_______












- Zitierten Text anzeigen -

 

[Vergel Adriano]

Hi Johannes,

I tested it and it does seem to work only at 100% zoom. It works on other
zoom levels but not consistently.. perhaps you can temporarily set the zoom
level to 100%, move the button, then put it back on the starting zoom level...

Dim dblZoom As Double
dblZoom = ActiveWindow.Zoom
ActiveWindow.Zoom = 100

'paste here the code to move the button

ActiveWindow.Zoom = dblZoom

 

[Jon Peltier]

A fair number of things in Excel don't work as expected if the zoom is not
100%. Charts seem to suffer particularly from this.

For fun, create a chart on a worksheet, change the zoom to 50%, copy the
chart, and paste it nearby. It's half the size of the original. This happens
at zooms < 100%, with the pasted chart being approximately the size of the
original times the zoom. Zooms >100% don't affect pasted chart size.

- Jon

 

[jlaufs]

That´s strange.
Thanks again for your answer.
Usenet is always a good place for problems!