Create macro that assigns value in a cell to Shape

[bobshah2010]

Hi Guys!

I'm having some trouble assigning a name to a shape via a macro.

Right now my macro is creating a list of visible sheets, and then creating an equal amount of shapes onto the worksheet.

I would like my macro to assign the shapes created with the value that is in the list created earlier (of all visible sheets). Ideally this shape should be linked i.e. "=$A$1" etc, not just named.

The next step that i have in mind is to assign these shapes with a macro - so that once you click on the shape it will re-direct you to the respective sheet name you clicked on.

This is the macro that i have used to assign shapes manually before:

Sub SheetLink()Application.GoToReference:= Worksheets (ActiveSheet.DrawingObjects(Application.Caller).Text).Range("A1")
End Sub

This is what i have so far:

Sub SheetNames()    Dim ws As Worksheet, ws1 As Worksheet
    Set ws1 = ActiveSheet
    Dim sq As Shape
    Dim rngRange As Range
    Dim Shp As Shape
    i = 1
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Set rngRange = Sheets(1).Range("A1")
    ws1.Columns(1).Insert
    For Each ws In ThisWorkbook.Worksheets
        If ws.Visible = xlSheetVisible Then
            ws1.Cells(i, 1) = ws.Name
            Set sq = ws1.Shapes.AddShape(1, 50, 50, 100, 100)
            
            'Selection.Formula = ws1.Cells(i, 1)
            i = i + 1
        End If
    Next ws
'    For Each Shp In ActiveSheet.Shapes
'        Shp.Select
'        Selection.Formula = ws1.Columns(1)
'    Next
End Sub

Please ignore some of the commented areas. I've been trying different things, but none seem to be working.

To provide some context, i've previously been creating shapes and linking them to sheet names manually before. (i.e would copy and paste each sheet name into a column on a separate sheet. Then creating a few shapes and assigning the above macro to the shape.

I'm looking at automating this process, essentially creating a navigation page.

 

[JoeMo]

Apologies Joe, I tried again and it worked!

Thank you so much, you were really helpful!

You are welcome - thanks for the reply.