CODEWITHSUNDEEP
vbaexcel
Alexey
Alexey

Reputation: 13

How can i call a Sub using hyperlinks

I am newbie to VBA; I have a question:

How can I call sub to delete a cell in a sheet by using a Hyperlinks from another sheet.

A structure of the code is greatly appreciated.

Upvotes: 0

Views: 1010

Answers (2)

Tim Williams
Tim Williams

Reputation: 166331

Event handler in worksheet which contains the hyperlink:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    If Target.TextToDisplay = "Clear Cell" Then
        ClearThatCell
    End If

End Sub

Note there's also a Workbook-level event: use that if you'd like to be able to trap any hyperlink click in the workbook. 

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, _
                                      ByVal Target As Hyperlink)

End Sub

Called code:

Sub ClearThatCell()
    ThisWorkbook.Sheets("Sheet2").Range("A1").ClearContents
End Sub

Upvotes: 2

Aleksey F.
Aleksey F.

Reputation: 761

' Public m_data_wks As Worksheet
Sub init_links
  Dim v_r As Range, n_rows as Integer
  Set v_r = m_data_wks.Cells(1, 1)
  n_rows = 3 'is an example of filling up cells with hyperlinks
  For I = 1 To n_rows
    v_r.Value = I
    'The key: adding hyperlink to the v_r cell with a special subaddress for alternative usage.
    'The hyperlink looks like the ordinary but points to itself.
    m_data_wks.Hyperlinks.Add Anchor:=v_r, Address:="", SubAddress:=v_r.Address(External:=False, RowAbsolute:=False, columnAbsolute:=False)
    Set v_r = v_r.Offset(1)
  Next I
end sub

'Private WithEvents App As Application
Private Sub App_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
  Dim v_dst As Worksheet, v_index_s As String, v_index_i As Integer
  'get_context sets v_dst the proper worksheet
  'get_context Sh.Parent, v_dst
  If v_dst Is Nothing Then Exit Sub
On Error GoTo Ext
  'Using the value of the cell for choosing which to delete
  v_index_s = CStr(Sh.Range(Target.SubAddress).Value)
  If v_index_s = "#" Then
    v_index_i = 0
  Else
    v_index_i = CLng(v_index_s)
  End If
  'Here the v_index_i points to the row instead of a cell for deleting
  v_dst.Rows(v_index_i).Delete
  Exit Sub
Ext:
  If Err.Number <> 0 Then
    MsgBox "Error occured while deleting by hyperlink: " & Err.Number & ", " & Err.Description, vbExclamation, "Non critical error"
    Err.Clear
  End If
End Sub

Upvotes: 0

Related Questions