Switching sheets formula

[TheDarkLord]

Hi there,

I am currently working on an Excel presentation, using buttons to display 3 charts on 3 separate sheets. Please advise on formulas I could use with the buttons to switch between the charts.


thanks

 

[Peter_SSs]

Hi there,

I am currently working on an Excel presentation, using buttons to display 3 charts on . Please advise on formulas I could use with the buttons to switch between the charts.


thanks

Welcome to the MrExcel board!

Tell us more about these 'buttons'. Are they
- toolbar buttons,
- Command Buttons from the 'Control Toolbox' toolbar (if so what sheet are they on?),
- Command Buttons from the 'Forms' toolbar (if so, what sheet are they on?),
- something else?

Tell us more about the '3 separate sheets'. What are their names?

By 'Please advise on formulas ...', I assume you mean 'Please advise on vba code ...'?

 

[TheDarkLord]

Command buttons from Forms toolbar in the first sheet labelled Presentation'

The three charts are on :

Sales sheet, marketing sheet, production sheet.

Using the buttons to present selected chart on the first presentation sheet.

The 3 buttons will be on the first sheet, and on clicking on each one, its respective chart will appear.

Do we need VBA code or will If functions be enough?

Kindly advise

thanks

 

[Peter_SSs]

If you want something to happen when a Command Button is clicked, you will need vba code.

Working with charts is not a forte of mine but let's see if either of these ideas are any use to you. I suggest testing on a copy of your workbook.

1. You have said the charts are on separate sheets. To switch to the relevant sheet when one of your buttons is clicked, try this.

a) Right click one of the sheet name tabs and choose "View Code".

b) Use the menus in the VB window ... Insert|Module

c) Copy the code below and Paste into the main right hand pane that opened at step b)

d) Right click one of your buttons inserted from the Forms toolbar and choose "Assign Macro..."

e) Select the appropriate macro for that button|OK.

Repeat d) and e) for the other two buttons

Test

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Show_Sales()<br>    Sheets("Sales").Activate<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Show_Marketing()<br>    Sheets("Marketing").Activate<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Show_Production()<br>    Sheets("Production").Activate<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>


If that's not what you want, try

2. If you want the charts to actually appear on the 'Presentation' sheet.

a) On the 'Presentation' sheet make 3 columns (I have used G:I) wide (enough to contain one of your charts).

b) Go to 'Sales' sheet (if that is where the actual sales data is)and re-chart the data but choose to place the chart on the 'Presentation' sheet. Position it within the first wide column (G in my example)

c) Repeat for the other two charts placing them in the other two wide columns.

d) Try assigning these macros to your buttons instead of the previous ones.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> Show_Sales()<br>    Columns("G:I").Hidden = <SPAN style="color:#00007F">True</SPAN><br>    Columns("G").Hidden = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Show_Marketing()<br>    Columns("G:I").Hidden = <SPAN style="color:#00007F">True</SPAN><br>    Columns("H").Hidden = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br><br><SPAN style="color:#00007F">Sub</SPAN> Show_Production()<br>    Columns("G:I").Hidden = <SPAN style="color:#00007F">True</SPAN><br>    Columns("I").Hidden = <SPAN style="color:#00007F">False</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[Chris Bode]

1.right click on the toolbar<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
2.from the popup select control box<o></o>
3.form the control box select a command button and draw to sheet (similarly draw 3 buttons)<o></o>
use following code to switch between sheets<o></o>

Private Sub CommandButton1_Click()<o:p></o:p>
Sheet1.Activate<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
Private Sub CommandButton2_Click()<o:p></o:p>
Sheet2.Activate<o:p></o:p>
End Sub<o:p></o:p>
<o:p></o:p>
Private Sub CommandButton3_Click()<o:p></o:p>
Sheet3.Activate<o:p></o:p>
End Sub<o:p></o:p>

 

[TheDarkLord]

Heya, sorry for very late reply.

But I wanna say a hearty thank you to Peter_SSs and Chris Bode. It worked beautifully. Thanks again for your help. I learned something new.

Regards