Hi Sheet

[josros60]

Hi,

How can I modify the code below to create the when I click back to CAD WIRES sheet hide it sheet I was in .

Code:

Sub Contents_Hyperlinks()
'PURPOSE: Add hyperlinked buttons back to Table of Contents worksheet tab


Dim sht As Worksheet
Dim shp As Shape
Dim ContentName As String
Dim ButtonID As String

'Inputs:
  ContentName = "USD WIRES" 'Table of Contents Worksheet Name
  ButtonID = "_ContentButton" 'ID to Track Buttons for deletion
  
'Loop Through Each Worksheet in Workbook
  For Each sht In ActiveWorkbook.Worksheets
  
    If sht.Name <> ContentName Then
      
      'Delete Old Button (if necessary when refreshing)
        For Each shp In sht.Shapes
          If Right(shp.Name, Len(ButtonID)) = ButtonID Then
            shp.Delete
            Exit For
          End If
        Next shp
        
      'Create & Position Shape
        Set shp = sht.Shapes.AddShape(msoShapeRoundedRectangle, _
          1, 1, 60, 20)

      'Format Shape
        shp.Fill.ForeColor.RGB = RGB(91, 155, 213) 'Blue
        shp.Line.Visible = msoFalse
        shp.TextFrame2.TextRange.Font.Size = 10
        shp.TextFrame2.TextRange.Text = ContentName
        shp.TextFrame2.TextRange.Font.Bold = True
        shp.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(255, 255, 255) 'White
      
      'Track Shape Name with ID Tag
        shp.Name = shp.Name & ButtonID
      
      'Assign Hyperlink to Shape
        sht.Hyperlinks.Add shp, "", _
          SubAddress:="'" & ContentName & "'!I1"
  
    End If
    
  Next sht

End Sub

Thank you,

 

[Anthony47]

Use the SheetDeactivate event...
Open the vba module "ThisWorkbook", and paste in it the following code:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If ActiveSheet.Name = "CAD WIRES" Then
    Sh.Visible = xlSheetHidden
End If
End Sub

This way when CAD WIRES get activated the past sheet will be hidden

Bye

 

[josros60]

Thank you so much, worked perfectly.