Show worksheets independently using macro assigned buttons

[Delvestio]

Hi all, i'm brand new at this and would like to politely ask you wonderful people if you could spare the time to help me with a problem:

I have one WorkBook.
This WorkBook contains 14 WorkSheets and Summary Sheet.
The Summary Sheet is a constant and always shown.
The WorkSheets are all hidden by default.
Within the Summary Sheet there are cells which contain the names of the WorkSheets, as follows:

Buildings
Contents
Jewellery
Jewellery (Specified)
Art & Antiques
Alternative Accommodation
Preventative Measures
Electricity Calcs

I need to have a button assigned to each cell that when clicked Shows the WorkSheet.

Help me.

 

[Delvestio]

P.S. doesnt have to be a button, could easily be an image or shape (like and arrow) with the macro assigned.

 

[dmt32]

P.S. doesnt have to be a button, could easily be an image or shape (like and arrow) with the macro assigned.

Hi welcome to forum
A shape would be a good choice.
It would be helpful if you are you able to share copy of your summary sheet using MrExcel Addin

Dave

 

[Delvestio]

Hi welcome to forum
A shape would be a good choice.
It would be helpful if you are you able to share copy of your summary sheet using MrExcel Addin

Dave

Hi Dave, I tried that and followed the steps but work admin police wont allow it. Here's a screen grab:

 

[Delvestio]

Hi Dave, I tried that and followed the steps but work admin police wont allow it. Here's a screen grab:

I was planning to use the little blue arrows (images) as the buttons

 

[dmt32]

I was planning to use the little blue arrows (images) as the buttons

Should not be a problem - do though ensure

- that each shape occupies the same Cell as the Sheet name listed in Column C
- that all the sheet names listed MATCH the actual tab name

let me know when all done

Dave

 

[Delvestio]

Should not be a problem - do though ensure

- that each shape occupies the same Cell as the Sheet name listed in Column C
- that all the sheet names listed MATCH the actual tab name

let me know when all done

Dave

Ok, all done - the blue arrows all sits within the cells under Column C and the names match.

 

[dmt32]

Ok, all done - the blue arrows all sits within the cells under Column C and the names match.

OK place following codes in a STANDARD module

Sub ShowSheet(ByVal SheetName As String)
    Dim sh          As String
    Dim shp         As Shape
    
    Application.ScreenUpdating = False
    For Each shp In ActiveSheet.Shapes
        If shp.TopLeftCell.Column = 3 Then
            sh = shp.TopLeftCell.Value
            If Evaluate("ISREF('" & sh & "'!A1)") Then
                Worksheets(sh).Visible = IIf(sh = SheetName, xlSheetVisible, xlSheetVeryHidden)
            End If
        End If
    Next shp
    Application.ScreenUpdating = True
    
End Sub

Sub AssignShapes()
 Dim shp        As Shape
 Dim SheetName  As String
 
 For Each shp In ActiveSheet.Shapes
    SheetName = shp.TopLeftCell.Value
    If shp.TopLeftCell.Column = 3 Then _
    shp.OnAction = "'ShowSheet """ & SheetName & """ '"
 Next shp

End Sub

Run the AssignShapes code just once - this will assign each shape in column C to the common procedure ShowSheet.
When done, try clicking a shape and hopefully, the sheet name listed next to it should become visible.

Note - if you update the sheet name list in column C at anytime, you will need to run the AssignShapes code again.

Dave

 

[Delvestio]

OK place following codes in a STANDARD module

Sub ShowSheet(ByVal SheetName As String)
    Dim sh          As String
    Dim shp         As Shape
   
    Application.ScreenUpdating = False
    For Each shp In ActiveSheet.Shapes
        If shp.TopLeftCell.Column = 3 Then
            sh = shp.TopLeftCell.Value
            If Evaluate("ISREF('" & sh & "'!A1)") Then
                Worksheets(sh).Visible = IIf(sh = SheetName, xlSheetVisible, xlSheetVeryHidden)
            End If
        End If
    Next shp
    Application.ScreenUpdating = True
   
End Sub

Sub AssignShapes()
 Dim shp        As Shape
 Dim SheetName  As String
 
 For Each shp In ActiveSheet.Shapes
    SheetName = shp.TopLeftCell.Value
    If shp.TopLeftCell.Column = 3 Then _
    shp.OnAction = "'ShowSheet """ & SheetName & """ '"
 Next shp

End Sub

Run the AssignShapes code just once - this will assign each shape in column C to the common procedure ShowSheet.
When done, try clicking a shape and hopefully, the sheet name listed next to it should become visible.

Note - if you update the sheet name list in column C at anytime, you will need to run the AssignShapes code again.

Dave

So im getting this error:

 

[Delvestio]

So im getting this error:

Code screenshot and macro security screenshot