VBA to Link Chart Options to Cells

[Phillycheese5]

Hi,
I have a line chart with 2 items and I want to have the minimum and maximum y-axis link to specific cells, as well as the major units for the x-axis. I found some code to do this and I added this into the VBA screen, but it doesn't work for some reason. Note that I put a single quote in front of the code that I don't need right now, but may need in the future.
Any suggestions would be appreciated.

Thanks,
Phillycheese

[MrShorty]

This forum can be pretty strict about putting code in code tags. You should do this to avoid getting your topic locked.

I think the problem is the location of your code, not the code itself. Fix the code tag issue, and we can discuss further, if needed.

[Phillycheese5]

Hey MrShorty, sorry about that---I haven't posted in the VBA section before (just the formula/function). The only reason I put the code in because it didn't seem too long and figured it would be easier for someone to view instead of having to download and check out the file. I don't know what a code tag is, but I will edit my original post to remove the code. Again, apologies.

[MrShorty]

Putting code into your posts will be a useful skill. Follow the "Forum Rules" link above and read #3, which describes how to use code tags.

Like I said, in this case, the problem isn't the code itself, but that you appear to have placed the code in a normal module. Event code needs to be placed in the module associated with the object it applies to. In this case, it seems to work just fine if you move the code from "Module1" to the "Sheet1" module.

[Phillycheese5]

Hi MrShorty, thanks for the direction!! I moved it as you suggested and it works as expected! I will check out the code tags as well.

Thanks again!!
Phillycheese

[Phillycheese5]

Hi,
I have added an second chart to my original file (only 1 chart is in the uploaded file) and copied the code so I can apply it to the second chart in the same worksheet. But I got the following compile error "Ambiguous name detected: Worksheet_Change". I tried to resolve it without success.

I plan to have 4 charts in the same worksheet, each whose options I would like to link to different cells, so any help would be appreciated.

Thanks,
Phillycheese

[MrShorty]

I have added an second chart to my original file (only 1 chart is in the uploaded file) and copied the code so I can apply it to the second chart in the same worksheet. But I got the following compile error "Ambiguous name detected: Worksheet_Change".

I cannot be sure exactly what you have done. If I read this correctly, did you end up with two "worksheet_change" event procedures for the same worksheet? When you make a change in the spreadsheet, how should Excel/VBA know which change procedure to execute?

If this is correct, the solution is to structure the code so that there is only one "worksheet_change" procedure that is able to determine -- which cell was changed, which chart (or charts) that change should apply to, and then perform the change. You cannot have 4 identical change procedures.