Running macro via hyperlink

[FJMD1003]

Hi guys,

I am trying to get a set of hyperlinks on a worksheet to run the macro 'Last_Row' I have written (below):

[FONT=Calibri][/FONT] 
[FONT=Calibri]Sub Last_Row()
Sheets("Sheet 1").Select
ActiveSheet.Protect Password:="Password", DrawingObjects:=True, _
        contents:=True, Scenarios:=True, _
        userinterfaceonly:=True
    ActiveSheet.EnableAutoFilter = True
If ActiveSheet.AutoFilterMode Then
  If ActiveSheet.FilterMode = True Then
       ActiveSheet.ShowAllData
  End If
End If
    Dim FinalRow As Long
    FinalRow = Range("D" & Rows.Count).End(xlUp).Row
    ActiveSheet.Rows(FinalRow).Select
    Selection.Offset(1, 0).Select
End Sub[/FONT][FONT=Calibri][/FONT]

I have been using the following to try and make it work without success:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Run ("Last_Row")
End Sub

The hyperlink is based on an IF formula (i.e. only appears if a certain cell has been checked "Yes". The same hyperlink features across multiple cells in one column ( Column V).

Can anyone help? Getting desperate

Cheers,

Fred

 

[klarowe]

is the hyperlink for more than just running the macro (ie actually a hyperlink to a website/etc)? If so, why not just use a button with its visibility matched to the "yes" checkbox?

 

[FJMD1003]

Hi Klarowe,

It's for nothing more than running the Macro, so your suggestion sounds good - how do you modify the visibility settings on a button?

CHeers,

Fred

 

[klarowe]

One more thing, is it just on a single sheet or will it appear on multiple sheets?
if just a single sheet then:

On the sheet where you want to put the button, insert a command button (not a form button). Double click the button and insert this code:

Run ("Last_Row")

Finally, whereever you want the checkbox, insert the checkbox with this code:

Sheet1.CommandButton1.Visible = Sheet1.CheckBox1.Value

Make sure to change the macro name, main code, "sheet1", commandbox#, and checkbox# to match your workbook.

There may be a better way of doing this, but it should get you what you need.

Editted since I completely forgot you had posted your original code.. lol.

 

[klarowe]

If you need multiple buttons and checkboxes, then the only way I know to do it would be to insert multiple buttons and checkboxes with the same codes. I'm sure someone can chime in if there is a better way... I'm still a noob when it comes to this stuff... lol

 

[FJMD1003]

Thanks very much for the input Klarowe, but I think this will still work best with the hyperlink option as it's tidier and simpler to follow for the specific audience - has anyone else got any input?

Any ideas very gratefully received....

Fred

 

[klarowe]

Again, I'm sure there is a better way of specifying the target range selection, but here is one way to write it so it gives you a "link"
Type this in the worksheet page that you want the checkbox/link to be and change the sheet name and cell as needed.

Private Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
        With Sheet1.Range("C4")
            .Value = "Link"
            .Font.ColorIndex = 5
            .Font.Bold = True
        End With
    Else
        Sheet1.Range("C4").Value = ""
    End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    On Error GoTo Er
    If Intersect(Target, Range("C4")) = "Link" Then
        Run ("Last_Row")
    Else: Exit Sub
    End If
Er: Exit Sub
End Sub

 

[klarowe]

Missed the edit window, but here is a little better code:

Private Sub CheckBox1_Click()
    If CheckBox1.Value = True Then
        With Sheet1.Range("C4")
            .Value = "Link"
            .Font.ColorIndex = 5
            .Font.Bold = True
            .Font.Underline = xlUnderlineStyleSingle
        End With
    Else
        Sheet1.Range("C4").Value = ""
    End If
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Target.Address = "$C$4" Then
        If Target.Value = "Link" Then Run ("Last_Row")
    Else: 'do nothing
    End If
End Sub

Editted to fix code.