Running a VBA script from a button after selecting a chart

0
votes

I’m running Excel 2010 on Windows 7.

I have four charts on a worksheet. What I want to do is select one of the charts and then click a button to open the ‘Format Axis’ dialogue box. I found the following code online to open the dialogue box. If I select a chart and then run the code from the toolbar (Developer tab, Macros, select macro, press ‘Run’), it works well. 

Sub formatXAxis1()
    ActiveChart.Axes(xlCategory).Select
    Application.CommandBars.ExecuteMso "ChartFormatSelection"
End Sub

The trouble I have is when the VBA script is assigned to a button. If I use a shape as the button, I get “Run-time error ‘91’: Object variable or With block variable not set”. I get the same error if I use an ActiveX Command Button. However, with a Form Control button it works as expected. The only problem with this solution is that it is not possible to change the background colour of the button so it looks out of place against the other macro calling buttons on the worksheet.

I'm guessing that in the first two cases (shape and ActiveX buttons), VBA drops or loses the chart selection when I press the button – even though the chart still appears selected on screen. Is there a fix for this, or am I doing something wrong?

2
vbaexcel
A clunky version would be just adding four buttons, each one for every chart. Afterwards you can call it via Chart ID. – Tom K.
You can assign a macro to any shape, including Text boxes, can't you ? These can be formatted. – Patrick Honorez
I tried text boxes. Unfortunately, they behave like a regular shape as described in my original blog. – user3424221

2 Answers

3
votes

For an ActiveX button, in the Properties set the property

TakeFocusOnClick

to False. That will cause Selection to keep on the chart rather than switch to the button and your code should work. The color can also be changed from the properties box, though you probably already know that.

0
votes

You need to reference the chart by name. So either have 4 buttons (one for each chart), or ask the user to input the name of the chart, then:

Dim co as ChartObject
Dim c as Chart
Dim NameOfChart as String

NameOfChart = Inputbox("Enter name of chart")
Set co = ActiveSheet.ChartObjects(NameOfChart)
Set c = co.chart
c.Axes(xlCategory).Select
Application.CommandBars.ExecuteMso "ChartFormatSelection"