Macro that creates a shape on each sheet

[bobshah2010]

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

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.

 

[tusharm]

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

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.

 

[bobshah2010]

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.

 

[tusharm]

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!

        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:

        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.

 

[bobshah2010]

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..."

 

[bobshah2010]

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.

 

[My Aswer Is This]

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.

 

[My Aswer Is This]

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.

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

 

[bobshah2010]

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

 

[My Aswer Is This]

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