Macro to change date format on horizontal axis on a chart upon button click

[m_research6]

Hi all -

I've been working on creating a simple macro that changes the formatting of the horizontal axis on 9 charts. We have an internal software that refreshes dates always using one format and we want to present those dates in a different format for presentation purposes. What I want to create is a button that someone can click to update the date formatting on those charts. Here's what I've done:

Create macro, go into the "select" data box for each charts' horizontal axis, change the dates by selecting a new worksheet that has just the dates I want, and ending macro after the dates have been applied to the 9 charts.
I then created a shape and assigned my macro to it. Seems like a pretty simple task, but I'm receiving a "run-time the item with the specified name wasn't found" error. See screenshots and VBA code below:

Button --> https://postimg.org/image/3tioyiwax/
Charts horizontal axis I want to change --> https://postimg.org/image/wkfiol24p/
Error --> https://postimg.org/image/pihl2dyix/
New periods I want that are on new tabs --> https://postimg.org/image/4zrab29zd/
VBA code --> https://postimg.org/image/wcwh5tgjt/

Much appreciated to those who can offer feedback/help/direction.

 

[JLGWhiz]

The error message is telling you that it cannot find a chart named "Chart16". You can check the chart names by running this little macro.

Sub t()
For Each s In ActiveSheet.Shapes
    MsgBox s.Name
Next
End Sub

If that confirms that you are using the correct names for the charts, then the next step is to verify your syntax.

 

[m_research6]

Thanks for the quick reply! I ran the code and it showed the following charts:

Chart 10, 11, 14, 15, 17, 20, 16, 21, 22 (with a space)

Looks like 16 appears in there.

 

[JLGWhiz]

Thanks for the quick reply! I ran the code and it showed the following charts:

Chart 10, 11, 14, 15, 17, 20, 16, 21, 22 (with a space)

Looks like 16 appears in there.

Did you try using a space in the code?

 

[m_research6]

Hmm, I'm not sure exactly what you mean. If you're referring to the code you told me to run, no I just ran it exactly as you pasted it and there was a one character space between Chart and the number (10, 11, etc.).

 

[JLGWhiz]

Hmm, I'm not sure exactly what you mean. If you're referring to the code you told me to run, no I just ran it exactly as you pasted it and there was a one character space between Chart and the number (10, 11, etc.).

In post #3 you had a comment (with a space). I assumed you meant the message box was returning chart names lke

ActiveSheet.ChartObjects( "Chart 10")

with a space. The code in the OP does not have spaces and appears to be using code names for the charts vs user assigned string names. I would try changing the code to match exactly what you see in the message box for a name. The error message you describe indicates that the compiler cannot detect the name that is currently in the code in the OP. So the objective now is to determine why and fix the code so that the object name is recognized by the compiler.

 

[m_research6]

Really appreciate you looking into this for me. Yes, you assumed correctly (here's the screenshot of the message box: https://postimg.org/image/5u84zdi99/). I went back into the VBA code and funny enough there is now a space between Chart and the number exactly as it appears in the message box. Here's the screenshot: https://postimg.org/image/3w4trn49z/

I'm not sure why in my first screenshot (https://postimg.org/image/wcwh5tgjt/) there was not a space in the VBA code, but it doesn't seem to matter as the error is still occurring either way when I now try to run the macro.

I'd be happy to PM you my workbook too if that helps.

Just getting into macros so thanks again for the troubleshooting!

 

[JLGWhiz]

Really appreciate you looking into this for me. Yes, you assumed correctly (here's the screenshot of the message box: https://postimg.org/image/5u84zdi99/). I went back into the VBA code and funny enough there is now a space between Chart and the number exactly as it appears in the message box. Here's the screenshot: https://postimg.org/image/3w4trn49z/

I'm not sure why in my first screenshot (https://postimg.org/image/wcwh5tgjt/) there was not a space in the VBA code, but it doesn't seem to matter as the error is still occurring either way when I now try to run the macro.

I'd be happy to PM you my workbook too if that helps.

Just getting into macros so thanks again for the troubleshooting!

If the chart names are as shown in the code, then I have no more ideas on what would cause the error message. I don't work with charts that much, but I recall that when I did some time ago, I had a similar problem and ended up using a variable to resolve the issue. But that was only one chart was easy enough to fix. Don't know if you could do that with nine charts unless they are the only shapes on your sheet, in which case you could use a loop to set the variables. But that would require a lot of code re-write. I don't have anything else to offer on this.
Regards, JLG

 

[m_research6]

Bummer! Yeah I'm not sure if anyone else has any ideas or thoughts, but I appreciate your help. I'm wondering if there's an alternative way I can accomplish my goal (i.e., having a clickable macro button that will update the formatting of the horizontal axis on 9 charts).