Link runs macro...second click runs another macro?

[jmpatrick]

Searched for this and didn't see a solution that used a link instead of a command button.

I have two very basic macros: one that shows gridlines, headings and the formula bar, and a second one that hides them.

I want use a sinle link in cell H1 that alternates between the two macros. The sheet would open with everything hidden (macro called HideWork) and the text link in the cell reading Show Work. If I click the link (running the macro called ShowWork) the macro runs and the text link in H1 changes to Hide Work.

Here's the macros I have so far:

Sub ShowWork()
  ActiveWindow.DisplayGridlines = True
  ActiveWindow.DisplayHeadings = True
  Application.DisplayFormulaBar = True
End Sub

Sub HideWork()
  ActiveWindow.DisplayGridlines = False
  ActiveWindow.DisplayHeadings = False
  Application.DisplayFormulaBar = False
End Sub

 

[Akuini]

Here's an example:
Create a hyperlink, assign it to cell I2.
Replace MsgBox with a call to your sub.

Dim flag As Boolean
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Target.Address(0, 0) = "I2" Then
        If flag Then
            MsgBox "ZZ"
        Else
            MsgBox "qqqqqqqqq"
        End If
        flag = Not flag
    End If

End Sub

another method:
double-clicking cell H1 instead of using hyperlink

 

[Crystalyzer]

Create a hyperlink in cell H1 that references itself and place the code below in the VBE of the Sheet where the hyperlink exists.

NOTE: UPDATED Code to exit sub as it was continuing to call the second macro.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "$H$1" And Selection.Hyperlinks(1).TextToDisplay = "Show Work" Then
        Call HideWork
        Selection.Hyperlinks(1).TextToDisplay = "Hide Work"
        Exit Sub
    End If
    If Target.Range.Address = "$H$1" And Selection.Hyperlinks(1).TextToDisplay = "Hide Work" Then
        Call ShowWork
        Selection.Hyperlinks(1).TextToDisplay = "Show Work"
        Exit Sub
    End If
End Sub

 

[jmpatrick]

Create a hyperlink in cell H1 that references itself and place the code below in the VBE of the Sheet where the hyperlink exists.

View attachment 45533

NOTE: UPDATED Code to exit sub as it was continuing to call the second macro.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "$H$1" And Selection.Hyperlinks(1).TextToDisplay = "Show Work" Then
        Call HideWork
        Selection.Hyperlinks(1).TextToDisplay = "Hide Work"
        Exit Sub
    End If
    If Target.Range.Address = "$H$1" And Selection.Hyperlinks(1).TextToDisplay = "Hide Work" Then
        Call ShowWork
        Selection.Hyperlinks(1).TextToDisplay = "Show Work"
        Exit Sub
    End If
End Sub

Perfect! Thank you.

 

[Crystalyzer]

Additionally you will want to put this code into the Thisworkbook section of the VBE so that when the workbook opens, it will "Hide Work" and show the hyperlink as "Show Work"

Private Sub Workbook_Open()
    If Range("H1").Hyperlinks(1).TextToDisplay = "Hide Work" Then
        Call HideWork
        Range("H1").Hyperlinks(1).TextToDisplay = "Show Work"
    End If
End Sub

 

[Crystalyzer]

Happy to help, and thanks for the feedback!

 

[Crystalyzer]

I just realized, I reversed the macro calls above. the code is updated below.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "$H$1" And Selection.Hyperlinks(1).TextToDisplay = "Show Work" Then
        'Call HideWork
         Call ShowWork
        Selection.Hyperlinks(1).TextToDisplay = "Hide Work"
        Exit Sub
    End If
    If Target.Range.Address = "$H$1" And Selection.Hyperlinks(1).TextToDisplay = "Hide Work" Then
        'Call ShowWork
        Call HideWork 
        Selection.Hyperlinks(1).TextToDisplay = "Show Work"
        Exit Sub
    End If
End Sub

 

[jmpatrick]

I just realized, I reversed the macro calls above. the code is updated below.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = "$H$1" And Selection.Hyperlinks(1).TextToDisplay = "Show Work" Then
        'Call HideWork
         Call ShowWork
        Selection.Hyperlinks(1).TextToDisplay = "Hide Work"
        Exit Sub
    End If
    If Target.Range.Address = "$H$1" And Selection.Hyperlinks(1).TextToDisplay = "Hide Work" Then
        'Call ShowWork
        Call HideWork
        Selection.Hyperlinks(1).TextToDisplay = "Show Work"
        Exit Sub
    End If
End Sub

I figured that out!