Macro In Excel

[Mavis]

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!

 

[Jacob Skaria]

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 (View>Toolbars>Drawing)
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

 

[Gary''s Student]

On the master sheet place a set of buttons (any AutoShape or piece of clipart
will do)

I like the Bevel.

Then assign a hyperlink to each shape, jumping to the correct data sheet.

By right-clicking the shape you can:

1. color the shape
2. add text
3. adjust transparency

The additional advantage to this method is that you can asign a pop-up
message to the hyperlink to give detailed instructions to the user

Use the same aproach to get back to the master sheet from the data sheets.

This can be setup manually or with the a aid of a macro:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 6/10/2009 by James Ravenswood
'

'
ActiveSheet.Shapes.AddShape(msoShapeBevel, 322.5, 48#, 90.75,
33.75).Select
Range("E2").Select
ActiveSheet.Shapes("AutoShape 1").Select
Selection.Characters.Text = "Go Forth!"
With Selection.Characters(Start:=1, Length:=9).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("I10").Select
ActiveSheet.Shapes("AutoShape 1").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection.ShapeRange.Item(1),
Address:= _
"", SubAddress:="Sheet3!A1"
Range("E17").Select
End Sub

 

[Jacob Skaria]

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 shape>Size and
Properties>Alt Text>. (Excel 2003 : Right click>Format
Autoshape>Web>Alternative 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 shape>Assign Macro>From 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

 

[Mavis]

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]

Yes you can do that..

If this post helps click Yes