Adding hyperlink to dynamically created label in VBA Excel

Question

I have multiple labels created dynamically on Userform. I want to add a hyperlink to the created labels, is there a way I could add hyperlink to these labels. Here is the code of how I created the labels dynamically.

Private Sub cmdViewReports_Click()

    Dim row_num As Long
    Dim fso As Object
    Dim src_path As String
    Dim dest_path As String
    Dim sub_folder As String
    Dim theLabel1 As msforms.Label
    Dim inc As Integer
    Dim my_files As Object
    Dim my_folder As Object
    Dim i As Integer
    Dim ctrl As Control

    'Check if the record is selected in listbox
    If Selected_List = 0 Then   

        MsgBox "No record is selected.", vbOKOnly + vbInformation, "Upload Results"

        Exit Sub

    End If

    'Folder Name to be created as per the 3rd column value in the list 
    sub_folder = Me.lstDb.List(Me.lstDb.ListIndex, 3)

    sub_folder = Replace(sub_folder, "/", "_")

    dest_path = "C:\abc\xyz\Desktop\FV\" & sub_folder & "\"

    Set fso = CreateObject("scripting.filesystemobject")

    If Not fso.FolderExists(dest_path) Then

        MsgBox "No reports are loaded"

        Exit Sub

    End If

    Set my_folder = fso.GetFolder(dest_path)
    Set my_files = my_folder.Files

    i = 1

    For Each oFiles In my_files
        Set theLabel1 = Me.Frame1.Controls.Add("Forms.Label.1", "File_name" & i, True)
                    With theLabel1
                        .Caption = oFiles.Name
                        .Left = 1038
                        .Width = 60
                        .Height = 12
                        .Top = 324 + inc
                        .TextAlign = 1
                        .BackColor = &HC0FFFF
                        .BackStyle = 0
                        .BorderStyle = 1
                        .BorderStyle = 0
                        '.Locked = True
                        .ForeColor = &H8000000D
                        .Font.Size = 9
                        .Font.Underline = True
                        .Visible = True
                    End With

                inc = inc + 12
                i = i + 1

    Next   
End Sub

here's how the part of the form looks like

To give a brief of my use case: I have some files/reports (pdf,word etc..) that I need to attach to a record. User can attach their reports to the records and also view reports if they are attached. So with the above code I am able to generate the labels with the files inside the folder; now when the file names are displayed on the form, I want a functionality where is I click the report(label) I want that report to open.

Thanks in Advance !!!

see [This thread](https://stackoverflow.com/questions/48382957/how-to-add-events-to-dynamically-created-controls-buttons-listboxes-in-Excel) : it uses a Class, but I think it's the only way to go if you want to stick with dynamically created controls. Have you thought of using "static" controls and hiding/unhideing them? — HTH, Apr 02 '20 at 15:10
I recently answered a similar [question](https://stackoverflow.com/a/60766557/5162073) covering this situation. It should be helpful. — Brian M Stafford, Apr 02 '20 at 15:11
@BrianMStafford To be honest, its a little difficult for me to digest the code. If you could help me build the code it would be mean a lot :) — Atul Patil, Apr 02 '20 at 15:45
Ok so here's what I have done create a class module clsMyEvents. Added these line ` Option Explicit Public WithEvents File_name As MSForms.Label Private Sub File_name_Click(ByVal link As String) ActiveWorkbook.FollowHyperlink Address:=link, NewWindow:=True End Sub 'Just after the End With in the code(Main question) i have this Dim lblEvent As clsMyEvents Set lblEvent = New clsMyEvents Set lblEvent.File_name = theLabel1 MyEvents.Add lblEvent Next End Sub ` I want to pass the **link** to the clsMyEvents, how can that be done? — Atul Patil, Apr 02 '20 at 17:04

Brian M Stafford · Accepted Answer · 2020-04-02T18:58:44.053

You can use most of the code in this answer with only slight modification. You will need to modify the MyControl class to use Labels instead of CommandButtons. You will also need to modify the event to pass the file name.

Once these modifications are complete, your code is pretty much the same, too. Here is your original code, simplified and modified to illustrate the concept:

UserForm

Option Explicit

Private WithEvents MyNotifier As Notifier
Private MyControls As Collection

Private Sub UserForm_Initialize()
   Set MyNotifier = GetNotifier()
   Set MyControls = New Collection
End Sub

Private Sub CommandButton1_Click()
   Dim i As Integer
   Dim inc As Integer
   Dim theLabel1 As MSForms.Label
   Dim mc As MyControl

   inc = 0

   For i = 1 To 2
      Set theLabel1 = Me.Frame1.Controls.Add("Forms.Label.1", "File_name" & i, True)

      With theLabel1
          .Caption = "filename" & i
          .Left = 100
          .Width = 60
          .Height = 12
          .Top = 20 + inc
          .TextAlign = 1
          .BackColor = &HC0FFFF
          .BackStyle = 0
          .BorderStyle = 1
          .BorderStyle = 0
          '.Locked = True
          .ForeColor = &H8000000D
          .Font.Size = 9
          .Font.Underline = True
          .Visible = True
      End With

      Set mc = New MyControl
      mc.Add theLabel1
      MyControls.Add mc

      inc = inc + 12
   Next
End Sub

Private Sub MyNotifier_Click(ByVal Filename As String)
   MsgBox Filename
End Sub

And here is the modified support files for quick reference:

Module

Option Explicit

Private m_Notifier As Notifier

Public Function GetNotifier() As Notifier
   If m_Notifier Is Nothing Then Set m_Notifier = New Notifier

   Set GetNotifier = m_Notifier
End Function

Notifier Class

Option Explicit

Public Event Click(ByVal Filename As String)

Public Function Click(ByVal Filename As String)
   RaiseEvent Click(Filename)
End Function

MyControl Class

Option Explicit

Private MyNotifier As Notifier
Private WithEvents MyLabel As MSForms.Label

Public Sub Add(ByVal c As MSForms.Label)
   Set MyNotifier = GetNotifier()
   Set MyLabel = c
End Sub

Private Sub MyLabel_Click()
   MyNotifier.Click MyLabel.Caption
End Sub

Ok so here's what I have done create a class module clsMyEvents. Added these line `Option Explicit Public WithEvents File_name As MSForms.Label Private Sub File_name_Click(ByVal link As String) ActiveWorkbook.FollowHyperlink Address:=link, NewWindow:=True End Sub` Just after the End With in the code(Main question) i have this ` Dim lblEvent As clsMyEvents Set lblEvent = New clsMyEvents Set lblEvent.File_name = theLabel1 MyEvents.Add lblEvent Next End Sub` I want to pass the **link** to the clsMyEvents, how can that be done? — Atul Patil, Apr 02 '20 at 17:08
You are a genius. Worked very well but only thing here is when I click the file I see the file name in msgbox, what i need is if i click the file it should open the file at that location `e.g "C:\abc\xyz\Desktop\FV\" & sub_folder & "\" & filename` . Inorder to do that I need to make use of `ActiveWorkbook.FollowHyperlink address:=**link**`. — Atul Patil, Apr 02 '20 at 19:16
Replace the MsgBox with whatever logic you need, in this case FollowHyperLink, adding the Filename to the Location. — Brian M Stafford, Apr 02 '20 at 19:27

score 0 · Answer 2 · answered Apr 02 '20 at 14:47

0

I suggest you to use the following code for each Label:

Private Sub Label1_Click()
ActiveWorkbook.FollowHyperlink Label1.Caption
End Sub

---> The Function you need is FollowHyperlink from the ActiveWorkbook.

You need to make sure that:

Label1 is the Label
The Caption of each Label is a valid URL Hyperlink
For better format, you need to make sure that the Label is well formatted as a Hyperlink

Once the user click on the Label, it should lead directly to the Default browser with the given Link.

Hope it will help you!

[Edition: Just formated the answer better]

answered Apr 02 '20 at 14:47

Tsiry Rakotonirina

731
4
13

i am aware of this function but this is not the way I need to create the hyper link. As I have created the label dynamically i am not sure where the click event would be . – Atul Patil Apr 02 '20 at 14:53
You still can set the `Label_Click` like this, but make sure that the Hyperlink the users assign to the Caption shall be a Valid Hyperlink. – Tsiry Rakotonirina Apr 02 '20 at 14:55
@TsiriniainaRakotonirina Since the number of controls is unknown until runtime, how would you get this to work? – Brian M Stafford Apr 02 '20 at 15:06
Looking at your main question, you can use `Dir(Labelxxx.Caption)` in a function and whenever the Result is different than `vbNullString`, then the **File exists**. – Tsiry Rakotonirina Apr 02 '20 at 15:14
So the `Dir` function shall test every `Labelxxx.Caption` anytime its value is assigned. Then you can use the `FollowHyperlink ` function on the Click of every Label. – Tsiry Rakotonirina Apr 02 '20 at 15:16
@TsiriniainaRakotonirina, the point is that those dinamically added controls do not react as they are coded till now, so they can't give the opportunity to be triggered by any event and start any action. Try it yourself!. But with the use of classes (see comments below the question) you can do it – HTH Apr 02 '20 at 15:26
May be you can try to look at Brian's solution here? https://stackoverflow.com/questions/60764106/create-a-series-of-dynamically-created-button-by-clicking-each-of-them-in-excel/60766557#60766557 – Tsiry Rakotonirina Apr 02 '20 at 15:28

Adding hyperlink to dynamically created label in VBA Excel

2 Answers2

Linked