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.

 

[My Aswer Is This]

I may be able to help you with the shape script.

Please answer these questions:

1.What do your want to happen when you click on the shape?
2. Where do you want the shape placed on each sheet?
Give me a range like "E10" or "B16" and the shape would then be placed in that exact location on each sheet. You can modify it later if you want.
Were just doing a test here.


I would rather create a whole new script to do this for you then to attempt to modify your script

 

[bobshah2010]

I may be able to help you with the shape script.

Please answer these questions:

1.What do your want to happen when you click on the shape?
2. Where do you want the shape placed on each sheet?
Give me a range like "E10" or "B16" and the shape would then be placed in that exact location on each sheet. You can modify it later if you want.
Were just doing a test here.


I would rather create a whole new script to do this for you then to attempt to modify your script

Thanks mate.

1. I would like to have a page with all the shapes from column B2-E2 that represent all the sheets that are currently visible- Call this the navigation sheet. I would also prefer if there already exists a "navigation" sheet with the same name, that it creates another version - call it "navigation(1)" or something.
These shapes, once clicked will take me to the respective sheet. I would then like a shape on the top of this sheet (and every other sheet between C2-E2) which will take me back to the navigation sheet.

2. So for the navigation sheet, have all the shapes go down from B2-E2. And for the link back to the navigation sheet (top of every other sheet) have it from C2-E2.

Really appreciate this!

Just think of this process like having a home button on a browser, and bookmarks that take you to different webpages. but if you want to come back to the start you have a home button at the top.

Thanks a lot.

 

[My Aswer Is This]

You said "shapes from column B2-E2"

Actually you should say Range("B2:E2")
There is no Column B2 or E2

Assuming you have one shape in each cell then you only have 4 shapes

I'm not sure how you would be able to only have shapes for visible sheets
So every time you hide a sheet then a shape has to be deleted. And if the sheet is unhidden then a new shape has to be created.

This is probable beyond my knowledgebase

 

[tusharm]

You do realize this is more or less "out of the box," don't you? While the UI is slightly different than the one you are accustomed to, it's a lot more versatile, adjusts to changes in sheet status, and easier to maintain (well, zero maintenance, actually!).

Each sheet has a tab that one can click to go to that sheet. If there are "too many" tabs, one can also right click on the arrows to the left of the worksheet tabs. This brings up a pop up dialog box listing all visible sheets. Double click any and voila, you are done. Now, you can get rid off the "navigation" worksheet.

An alternative that I developed some years back also did away with the need for using sheet elements for navigation. "TM Workbook Navigator" provides navigation through the Ribbon (or Commandbar for pre-2007). It's superior to the default navigation paradigm since it supports *all* open workbooks at one time. The downside, of course, is that it's an add-in that one has to install -- and pay for. . Nonetheless, the need for the utility has diminished since Microsoft itself improved sheet navigation and I haven't updated it in a couple of years.

Thanks mate.

1. I would like to have a page with all the shapes from column B2-E2 that represent all the sheets that are currently visible- Call this the navigation sheet. I would also prefer if there already exists a "navigation" sheet with the same name, that it creates another version - call it "navigation(1)" or something.
These shapes, once clicked will take me to the respective sheet. I would then like a shape on the top of this sheet (and every other sheet between C2-E2) which will take me back to the navigation sheet.

2. So for the navigation sheet, have all the shapes go down from B2-E2. And for the link back to the navigation sheet (top of every other sheet) have it from C2-E2.

Really appreciate this!

Just think of this process like having a home button on a browser, and bookmarks that take you to different webpages. but if you want to come back to the start you have a home button at the top.

Thanks a lot.