Macro that creates a shape on each sheet

bobshah2010

New Member
Joined
Nov 29, 2016
Messages
39
Hi Guys,

I'm currently working on a macro that creates a shape on each visible sheet. And then I want to assign a Macro to those shapes.
This is what i have so far

Code:
Sub AddNavigationPage()
Dim ws1 As Worksheet, ws As Worksheet
With ThisWorkbook
    .Sheets.Add(Before:=.Sheets(1)).Name = "Navigation"
End With
Set ws1 = ActiveSheet
For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws1.Cells(i + 1, 1) = ws.Name
            'change the multiplier in next line to space the rectangles to suit
            ws1.Shapes.AddShape(1, 100, 25 + i * 50, 100, 40).Select
            With Selection
              .Formula = "=A" & i + 1
              .OnAction = "ActivateSheet"
              End With
            i = i + 1
        End If
    Next ws
    
        For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Shapes.AddShape(1, 100, 25 + i * 50, 100, 40).Select
                With Selection
                .Formula = "=A" & 2
                .OnAction = "ShapeLink"
                End With
            i = i + 1
        End If
    Next ws
    
    Columns("A").EntireColumn.Hidden = True
    End Sub
Sub ActivateSheet()
Dim shp As Shape
    Set shp = ActiveSheet.Shapes(Application.Caller)
    s = shp.TextFrame.Characters.Text
    Sheets(s).Select
End Sub

It currently does what it needs to in terms of creating a shape and naming all the shapes onto one page at the start.
I just need it to create a shape on every other sheet, and make a link back to the "navigation" page.

Think of it like a Home button, that takes me back to the page with all the other buttons.

help will be much appreciated.
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Your code is inserting a shape on every visible worksheet that calls the ShapeLink macro, which I assume makes the "navigation" page active.

So, what do you need help with?

Hi Guys,

I'm currently working on a macro that creates a shape on each visible sheet. And then I want to assign a Macro to those shapes.
This is what i have so far

Code:
Sub AddNavigationPage()
Dim ws1 As Worksheet, ws As Worksheet
With ThisWorkbook
    .Sheets.Add(Before:=.Sheets(1)).Name = "Navigation"
End With
Set ws1 = ActiveSheet
For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws1.Cells(i + 1, 1) = ws.Name
            'change the multiplier in next line to space the rectangles to suit
            ws1.Shapes.AddShape(1, 100, 25 + i * 50, 100, 40).Select
            With Selection
              .Formula = "=A" & i + 1
              .OnAction = "ActivateSheet"
              End With
            i = i + 1
        End If
    Next ws
    
        For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Shapes.AddShape(1, 100, 25 + i * 50, 100, 40).Select
                With Selection
                .Formula = "=A" & 2
                .OnAction = "ShapeLink"
                End With
            i = i + 1
        End If
    Next ws
    
    Columns("A").EntireColumn.Hidden = True
    End Sub
Sub ActivateSheet()
Dim shp As Shape
    Set shp = ActiveSheet.Shapes(Application.Caller)
    s = shp.TextFrame.Characters.Text
    Sheets(s).Select
End Sub

It currently does what it needs to in terms of creating a shape and naming all the shapes onto one page at the start.
I just need it to create a shape on every other sheet, and make a link back to the "navigation" page.

Think of it like a Home button, that takes me back to the page with all the other buttons.

help will be much appreciated.
 
Upvote 0
Hi Tusharm,

Its not creating a shape on every visible sheet.

What i'm basically try to achieve is a link that takes me back to the navigation page, where all the other shapes have been created. so for instance if i have 30 sheets in a workbook and I go to the navigation page that takes me to the 28th sheet. I would like for a shape that has been created to link me back to the navigation page.

Hope this makes sense.
 
Last edited:
Upvote 0
The code below (from your original post) creates a shape on each visible worksheet that uses the ShapeLink sub, which may or may not exist!
Code:
        For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Shapes.AddShape(1, 100, 25 + i * 50, 100, 40).Select
                With Selection
                .Formula = "=A" & 2
                .OnAction = "ShapeLink"
                End With
            i = i + 1
        End If
    Next ws
It locates the shape fairly much "down" the sheet. Scroll down...and down...and down...and you'll see the shape.

Consider the following revised and *untested* code:
Code:
        For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws.Shapes.AddShape(1, 100, 25 + [COLOR="#FF0000"]0[/COLOR] * 50, 100, 40).Select
                With Selection
                .Formula = "=A" & 2
                .OnAction = "[COLOR="#FF0000"]ActivateSheet[/COLOR]"
                End With
            i = i + 1
        End If
    Next ws
Hi Tusharm,

Its not creating a shape on every visible sheet.

What i'm basically try to achieve is a link that takes me back to the navigation page, where all the other shapes have been created. so for instance if i have 30 sheets in a workbook and I go to the navigation page that takes me to the 28th sheet. I would like for a shape that has been created to link me back to the navigation page.

Hope this makes sense.
 
Upvote 0
hahaha, i can't believe i made that mistake!

Thanks alot Tusharm!

The only thing not working is that its not naming the shape correctly - i.e calling A2 from the navigation sheet.

Do you know a work around that?

I think i will have to update:

.Formula = "=A" & 2

to

.Formula = "!Navigation.sheet..."
 
Upvote 0
I'm thinking it may even be easier to create a macro that just copies the "navigation" sheet linked shape onto the top of every visible sheet.
 
Upvote 0
If you want to have a link on every sheet in your workbook that will take you back to your Master sheet.
Why not have a link in say Range("A1") of every sheet which you could click on to take you back to the Master sheet.

Why do you need a shape with a Macro in the shape?

If that would work I could write a script which would only need to be run once which would install the link on each sheet.

I would need to know the master sheets name and in what cell on each sheet can I install the link in.
 
Last edited:
Upvote 0
If your willing to take my suggestion unless I really do not know what your attempting to do try this:

Create yourself a new workbook for this test

Create a Sheet named "Master"

Create yourself several worksheets and give them names.

Then run this script:
You will only need to run this script once unless you add new sheets later.
Then you will have to run the script again to include those new sheets.

You will see in Sheet named "Master" in column "A" you will have a list of all your sheet names
with a link to that sheet. Click on the sheet name and you will be taken to that sheet.

And then look in all your other sheets and in Range("A1") you will have a link you can click on that will take you back to your "Master" sheet.


Code:
Sub AddHyperLinks()
Dim C As Range
Dim i As Long
    For i = 2 To Sheets.Count
    Sheets("Master").Cells(i, 1).Value = Sheets(i).Name
    Sheets(i).Cells(1, 1).Value = Sheets(1).Name
    Sheets(i).Cells(1, 1).Hyperlinks.Add Anchor:=Sheets(i).Cells(1, 1), Address:="", SubAddress:="'" & Sheets(i).Cells(1, 1).Value & "'!A1"
    Next

With Sheets("Master")
    For Each C In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
        .Hyperlinks.Add Anchor:=C, Address:="", SubAddress:="'" & C.Value & "'!A1"
    Next C
End With
End Sub
 
Last edited:
Upvote 0
Hey mate,

The reason im using shapes is because its just something we've done always and for some reason prefer assigning a macro to a shape over hyperlinks.

I think the reason we use a shape is because it is linked to the list generated from all the visible sheets. So if a sheet name changes all we need to do is change that list generated.

I hope this helps.

Also you macro works really well, i would much rather prefer that method than the current way.

Thanks
 
Upvote 0
Thanks for your comments. I know how things work at a lot of places and changing ways of doing things. My script would also work when you change sheet names. You would just have to run the script again. Take care and I hope someone else can help you with your needs.
Hey mate,

The reason im using shapes is because its just something we've done always and for some reason prefer assigning a macro to a shape over hyperlinks.

I think the reason we use a shape is because it is linked to the list generated from all the visible sheets. So if a sheet name changes all we need to do is change that list generated.

I hope this helps.

Also you macro works really well, i would much rather prefer that method than the current way.

Thanks
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top