Jump to content
News Ticker
  • Welcome to the Community
  • Use the forum to troubleshoot your Excel & VBA Problems
  • We will be launching a new website soon! www.ExcelWTF.com/Learn ! Join us to learn VBA from the ground up!
  • 0
Sign in to follow this  
ZiyaSepp

Use SelectionChange Event to Switch Chart

Question

Hey Caleeco,

 

I'm upgrading my dashboard with some new charts, however, space is a premium as most people in the office work on small laptop screens! 😶

 

Would you be able to help me with something i've dreamt up? Basically... i want to stack 2 charts one on top of the other. Then be able to click on Cell B3 to bring the first chart to the front (hiding the other one behind it), or click cell B4 to bring the second chart to the front. So it will look like they're coming out of nowhere 😁

 

My Chart names are 'Chart 1' and 'Chart 2' if you need them. Can you write some VBA magic?

 

  • Like 1

Share this post


Link to post
Share on other sites

4 answers to this question

Recommended Posts

  • 0

Hello Ziya

 

Good to see you again. That's an awesome idea! Certainly something we can use the SelectionChange Event to implement. Try this code out and see if it does what you need. 

 

Obviously you'll need to align your charts up first & past the code in the relevant SHEET object and not a VBA module.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'//--- Source: www.ExcelWTF.com
'//--- Purpose: Quick Switch between charts
    If Target.Count > 1 Or Intersect(Target, Range("B3:B4")) Is Nothing Then Exit Sub
    If Target.Address = "$B$3" Then ActiveSheet.Shapes("Chart 1").ZOrder msoBringToFront
    If Target.Address = "$B$4" Then ActiveSheet.Shapes("Chart 2").ZOrder msoBringToFront
End Sub

Hope That Helps

Caleeco 😎

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

Hi Caleeco!

 

That works great... only 3 lines of code!? 😵 I thought it would be way more complicated haha

 

Im trying to decipher what you've done.. i get most of it, except this line:

If Target.Count > 1

Whats that supposed to do?

 

Z

Share this post


Link to post
Share on other sites
  • 0

Ah, that's not a VITAL bit of code.. it's just generally good practice when using the SelectionChange Event. 

 

It basically prevents the code running if you select more than one cell with your mouse. Eg... if you selected both B3 and B4 at the same time, the code will get confused and not trigger either event! 😵

 

Glad it worked 😊

 

Caleeco

 

 

  • Like 1

Share this post


Link to post
Share on other sites
  • 0

oooh I see, very clever! Thanks for this. 

 

I love all the new emojis by the way haha 🤪 I'll no doubt be back soon with other questions, i''ve got some other ideas I'd love to implement 

 

Z

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×