Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro In Excel
Hi Jacob,
Thanks for your reply. I found in the Develper tab there is one function to insert button. Can i use the button instead of the shape? "Jacob Skaria" wrote: Mavis If you would like to keep all sheets hidden except the master you will need to use a macro. Assuming your master sheet is named as "MASTER" try the below and feedback. 1. From autoshapes select the shape of your choice and label it as required. 2. If you are using Excel 2007: Right click each shapeSize and PropertiesAlt Text. (Excel 2003 : Right clickFormat AutoshapeWebAlternative text). Enter the sheet name in the alternative text area for each shape. Please make sure the name is ** exactly same ** as the chart name (even spaces count !). 3. Set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the below code. Save. Close the VBE window and get back to Workbook. Sub GoToMySheet() Application.ScreenUpdating = False For intTemp = 1 To Sheets.Count If UCase(Sheets(intTemp).Name) < "MASTER" Then _ Sheets(intTemp).Visible = False Next Set shp = ActiveSheet.Shapes(Application.Caller) Sheets(Trim(shp.AlternativeText)).Visible = True Sheets(Trim(shp.AlternativeText)).Activate Application.ScreenUpdating = True End Sub 3. Right click the first shapeAssign MacroFrom the list Select 'GoToMySheet' and click OK. Now try mouse click the shape. If that works repeat the same for each shape. PS: 1. You can place a hyperlink in each sheet to get back to index sheet. 2. Incase you need to change the sheet name remember to rename the sheet name in alternative text of the shape. If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Welcome!!! If you dont want to hide your sheets you can get this done without using a macro. Insert a main sheet. From menu drawing toolbar (ViewToolbarsDrawing) select the shape you need and right click to give a suitable color. Right click on the shape 'Hyperlink'. Click 'Place in this document' and select the sheet you need. You can do the same thing in the individual sheets to return back. If this post helps click Yes --------------- Jacob Skaria "Mavis" wrote: Hi All, I am new here. This is my first time doing macro. I have a excel sheet with a few worksheet, each worksheet contain detail of a log. I will need to create a coverpage with buttons for each worksheet. Once the user open up the excel sheet, the coverpage will show, the other worksheet will not show. The user will need to click on the buttons in the coverpage to access to the worksheet. In the worksheet there will also be a main page button to go back to the cover page. can anyone good soul teach me how to do this? Thanks in advance! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro In Excel
Yes you can do that..
If this post helps click Yes --------------- Jacob Skaria "Mavis" wrote: Hi Jacob, Thanks for your reply. I found in the Develper tab there is one function to insert button. Can i use the button instead of the shape? "Jacob Skaria" wrote: Mavis If you would like to keep all sheets hidden except the master you will need to use a macro. Assuming your master sheet is named as "MASTER" try the below and feedback. 1. From autoshapes select the shape of your choice and label it as required. 2. If you are using Excel 2007: Right click each shapeSize and PropertiesAlt Text. (Excel 2003 : Right clickFormat AutoshapeWebAlternative text). Enter the sheet name in the alternative text area for each shape. Please make sure the name is ** exactly same ** as the chart name (even spaces count !). 3. Set the Security level to low/medium in (Tools|Macro|Security). From workbook launch VBE using short-key Alt+F11. From menu 'Insert' a module and paste the below code. Save. Close the VBE window and get back to Workbook. Sub GoToMySheet() Application.ScreenUpdating = False For intTemp = 1 To Sheets.Count If UCase(Sheets(intTemp).Name) < "MASTER" Then _ Sheets(intTemp).Visible = False Next Set shp = ActiveSheet.Shapes(Application.Caller) Sheets(Trim(shp.AlternativeText)).Visible = True Sheets(Trim(shp.AlternativeText)).Activate Application.ScreenUpdating = True End Sub 3. Right click the first shapeAssign MacroFrom the list Select 'GoToMySheet' and click OK. Now try mouse click the shape. If that works repeat the same for each shape. PS: 1. You can place a hyperlink in each sheet to get back to index sheet. 2. Incase you need to change the sheet name remember to rename the sheet name in alternative text of the shape. If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Welcome!!! If you dont want to hide your sheets you can get this done without using a macro. Insert a main sheet. From menu drawing toolbar (ViewToolbarsDrawing) select the shape you need and right click to give a suitable color. Right click on the shape 'Hyperlink'. Click 'Place in this document' and select the sheet you need. You can do the same thing in the individual sheets to return back. If this post helps click Yes --------------- Jacob Skaria "Mavis" wrote: Hi All, I am new here. This is my first time doing macro. I have a excel sheet with a few worksheet, each worksheet contain detail of a log. I will need to create a coverpage with buttons for each worksheet. Once the user open up the excel sheet, the coverpage will show, the other worksheet will not show. The user will need to click on the buttons in the coverpage to access to the worksheet. In the worksheet there will also be a main page button to go back to the cover page. can anyone good soul teach me how to do this? Thanks in advance! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Need syntax for RUNning a Word macro with an argument, called from an Excel macro | Excel Programming | |||
translate lotus 1-2-3 macro into excel macro using excel 2000 | Excel Programming |