• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Auto-changing y-axis number formats

C

Colin Mannion

Member
HI

I have written some simple macros which change the format of the numbers on the y-axis to reflect if they should be currency, numbers or numbers with 2 decimal places. They have been assigned to buttons and the user can change the y-axis number format as required.

However, I would really like to have the axis change automatically depending on what is selected in a cell value - i.e. if the cell is Unit Sales (in this example cell D7), then format should be a number, if it is Value sales, should be a currency etc.

Similarly there are a couple of macros which the user can click on to remove blank series so that 0s don't appear in the chart, and to re-instate them when required.
I have attached a file that has the macros in it and some base data.

Can anyone adviser on how to change the macros so that they run in background (I guess as some kind of private sub-routine).

Thanks in advance!

Colin
 

Attachments

  • Data File for Chandoo v2.0.xlsm
    189.3 KB · Views: 2
Hi Colin

For the chart to automatically change the format, in axis format settings you can link it to the source... with this option you can then have VBA format the source values to reflect the option selected in the dropdown at D7 and it will affect the chart
JPEG.jpg

Using what you already have, you can have a worksheet_change event trigger a macro where you can have your existing code (the macros linked to the 4 format change buttons) with an if statement so that it executes the code according to the value in the cell
 
Last edited:
About the macros, for them to run in the background you can use Worksheet_SelectionChange or Worksheet_Change events to trigger them... the SelectionChange will trigger the macro when you select something in the sheet, the other event will trigger the macro when you edit the contents of a cell.

In this case, you could have a cell where you would select if you want "0" series to show or not, and have an worksheet_change event trigger the macro (it may be necessary to change the macros a little in order to use them with the event properly)
 
Hi PCosta

Thanks for this.

I am still really a beginner in writing code - picking up bits of cVBA from various sources and cobbling them together into solutions that work for me :)

Would it be possible for you to show me some code which will do this stuff?

Regards
Colin
 
Colin Mannion said:
Hi PCosta

Thanks for this.

I am still really a beginner in writing code - picking up bits of cVBA from various sources and cobbling them together into solutions that work for me :)

Would it be possible for you to show me some code which will do this stuff?

Regards
Colin
Click to expand...
Hi,

I was just about to post it :)
Kept the code you already had to speed it up

Please refer to attachment
 

Attachments

  • Data File for Chandoo v2.0.xlsm
    182.9 KB · Views: 4
PCosta87 said:
You're welcome :)
Click to expand...
Hi PCosta

Hope you had a great Christmas, and all the best for a healthy, peaceful and happy 2017.

Thanks for that macro you sent over before Christmas. I am now back at work and have been looking at it. It seems to work fine in the main but there does seem to be a small bug:

If the hide check box is clicked and I add in retailers so the drop-down menu table is populated (B7:B12) then everything works fine. I can also delete a retailer and the chart updates accordingly.

If however, I then re-populate those deleted cells with the check box clicked to hide "0" series, and then click it to unhide the newly populated retailers, then a run time error 91 pops up. I realise this could be dealt with by training the users on how to avoid this, but if there's a way to do this transparently, that would be awesome.

Thanks in advance.
Colin
 

Attachments

  • Data File from PCosta @ Chandoo v2.0.xlsm
    183.2 KB · Views: 1
Colin Mannion said:
Hi PCosta

Hope you had a great Christmas, and all the best for a healthy, peaceful and happy 2017.

Thanks for that macro you sent over before Christmas. I am now back at work and have been looking at it. It seems to work fine in the main but there does seem to be a small bug:

If the hide check box is clicked and I add in retailers so the drop-down menu table is populated (B7:B12) then everything works fine. I can also delete a retailer and the chart updates accordingly.

If however, I then re-populate those deleted cells with the check box clicked to hide "0" series, and then click it to unhide the newly populated retailers, then a run time error 91 pops up. I realise this could be dealt with by training the users on how to avoid this, but if there's a way to do this transparently, that would be awesome.

Thanks in advance.
Colin
Click to expand...
Hi Colin,

My bad :(
I didn't account for every retailer being selected and that was causing the problem.

It is fixed (please refer to the attached file)!

Sorry about that :)
 

Attachments

  • Data File from PCosta @ Chandoo v2.0.xlsm
    183.7 KB · Views: 5
No problem P - thanks for the help :)

One other strange thing I have at the moment relating to a vlookup that won't work - sample attached. I have looked the one cell is formatted General, the other Text - have tried to make them the same but still not working. Have looked at TRIM etc. plus some of the suggestions on line but no joy - if you have any ideas on this I would appreciate it.

Thanks again!
Colin
 

Attachments

  • Vlookup not working.xlsx
    14.9 KB · Views: 2
Hi PCosta

I have found a solution to this. It turns out that there was a carriage return in the string. Using Find & Replace with Ctrl+J replaced by blank removes it and hey presto all sorted!!

Thanks again for the help my friend.
Colin
 
Colin Mannion said:
No problem P - thanks for the help :)

One other strange thing I have at the moment relating to a vlookup that won't work - sample attached. I have looked the one cell is formatted General, the other Text - have tried to make them the same but still not working. Have looked at TRIM etc. plus some of the suggestions on line but no joy - if you have any ideas on this I would appreciate it.

Thanks again!
Colin
Click to expand...
Hi,

You have a extra space (or should I say a carriage return) at the end of B3 which is why it is not working. You must have hit Alt+Enter by accident :)

Edit: Only saw your post after posting... it seems you also figured it out ;)
 
As a matter of interest, how do you spot a CR in a column of data given its not visible to the naked eye - would you use Find: Ctrl+J?

Regards and have a great weekend.
Colin
 
Well, that is the "official" method... but I have used a different approach before and it worked well for identifying those cases visually

Assuming the CR is at the end of the string like in the example, what I do is align everything to the bottom and set all rows to the preset height (15 I believe)... then I remove the "wrap text" (you may have noticed that if you hit Alt+Enter when entering something into a cell, Excel automatically selects "wrap text" for you). The cells which have the CR will appear as if they are empty (because last line is empty and text is aligned to the bottom)... toggling "wrap" on and of makes those cells blink like Christmas decorations :)

Have a nice weekend ;)
 
Last edited:
You must log in or register to reply here.
Back
Top