Wednesday, April 24, 2013

VBA - Create Hyperlink Using VBA Coding on click of button.

 This Post will help you in creating hyperlink dynamically using VBA coding.

Requirement: My Client wants to create hyperlinks dynamically with a click of button with the source value present in column two "Link Source" and the name of link is present in Column 1 with heading "Link Name" and the hyperlink must be created in third column with heading "Final Hyper Link"

To Run the VBA code I have created a button called "Create Hyperlink" Clicking on it will create the hyperlinks in Final Hyper Link Column.

'Function Name: CreateHyperlink
'Author Name: Anshu Khandelwal
'Input : From Excel Work Book as link name and link source.
'This function can be run after calling this function on Button or Directly running it

Public Function CreateHyperlink()
Dim link_name As String
Dim link_value As String
Dim i As Integer

'Using Count to work on Dynamic Records.
 Count = Application.WorksheetFunction.CountA(Range("A:A"))

For i = 2 To Count
    link_name = Cells(i, 1).Value   'Picking up link_name value from Column 1
    link_source = Cells(i, 2).Value 'Picking up link_source value from Column
    Cells(i, 3).Select   'Select a Cell where you want Hyperlink to display
 'Here i am adding hyperlinks with ActiveSheet reference and Passing SubAddress in the form '
 of link_source and Texttodisplay using link_name variable which contains values from excel workbook.
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=link_source, _
Next i

End Function

Note: Anyone can directly use this code just copy and paste in your module and change value in your excel as per your need.

No comments:

Post a Comment