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]

I really appreciate your help Dave

 

[dmt32]

I really appreciate your help Dave

Looks like you have placed the code in a Sheet code page. Code MUST be placed in a STANDARD module

Delete the Code & the from the VBA Editor Insert > Module
Now place the code in the module.
With the Summary Sheet Active, run the AssignShapes code.

Dave

 

[Delvestio]

Looks like you have placed the code in a Sheet code page. Code MUST be placed in a STANDARD module

Delete the Code & the from the VBA Editor Insert > Module
Now place the code in the module.
With the Summary Sheet Active, run the AssignShapes code.

Dave

Thanks Dave, I followed your instructions and this threw of a mismatch error: I ran the debug - screen shots attached.

 

[dmt32]

It may be that a name in your range is not valid

can you comment out the lines shown in the code below & then try running it again

           'If Evaluate("ISREF('" & sh & "'!A1)") Then
                Worksheets(sh).Visible = IIf(sh = SheetName, xlSheetVisible, xlSheetVeryHidden)
           ' End If

 

[dmt32]

further thoughts I am guessing you may have another shape in column C that is not assigned to a sheet name (empty cell)

try this update to the ShowSheet code & see if resolves the issue

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 Len(sh) > 0 Then
                If Evaluate("ISREF('" & sh & "'!A1)") Then
                    Worksheets(sh).Visible = IIf(sh = SheetName, xlSheetVisible, xlSheetVeryHidden)
                End If
            End If
        End If
    Next shp
    Application.ScreenUpdating = True
   
End Sub

Dave

 

[Delvestio]

further thoughts I am guessing you may have another shape in column C that is not assigned to a sheet name (empty cell)

try this update to the ShowSheet code & see if resolves the issue

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 Len(sh) > 0 Then
                If Evaluate("ISREF('" & sh & "'!A1)") Then
                    Worksheets(sh).Visible = IIf(sh = SheetName, xlSheetVisible, xlSheetVeryHidden)
                End If
            End If
        End If
    Next shp
    Application.ScreenUpdating = True
  
End Sub

Dave

Ok, so this has resolved the issue and the buttons now work without throwing up an error. However, the result is not what is required.

E.G., When I click the arrow within the Buildings Column C cell - this works perfectly and shows the Buildings tab (previously hidden). However, when I then click the arrow in the Contents cell it replaces the Buildings tab with the contents tab. I need it to show any and all tabs that has been clicked.

 

[dmt32]

Ok, so this has resolved the issue and the buttons now work without throwing up an error. However, the result is not what is required.

E.G., When I click the arrow within the Buildings Column C cell - this works perfectly and shows the Buildings tab (previously hidden). However, when I then click the arrow in the Contents cell it replaces the Buildings tab with the contents tab. I need it to show any and all tabs that has been clicked.

You need to ensure that each Shape sits within the appropriate cell (must not overlap another cell) & then with the summary sheet active, run the AssignShapes code again.

Dave

 

[Delvestio]

You need to ensure that each Shape sits within the appropriate cell (must not overlap another cell) & then with the summary sheet active, run the AssignShapes code again.

Dave

Looks fine to me...

 

[dmt32]

Looks fine to me...

Did you re-run the AssignShapes code?

If still having problems, place copy of your workbook (with no data) on a file sharing site like dropbox & provide a link to it here.

 

[Delvestio]

Did you re-run the AssignShapes code?

If still having problems, place copy of your workbook (with no data) on a file sharing site like dropbox & provide a link to it here.

Dropbox

www.dropbox.com