在VBA Excel中将超链接添加到动态创建的标签 [英] Adding hyperlink to dynamically created label in VBA Excel

问题描述

我在Userform上动态创建了多个标签.我想将超链接添加到创建的标签，有没有一种方法可以将超链接添加到这些标签.这是我如何动态创建标签的代码.

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

这是表单部分的外观

要简要介绍一下我的用例:我需要将一些文件/报告(pdf，word等)附加到记录中.用户可以将其报告附加到记录中，也可以查看报告(如果已附加).因此，使用上面的代码，我可以使用文件夹内的文件生成标签；现在，当文件名显示在表单上时，我想要一个功能，该功能是单击要打开该报告的报告(标签).

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.

先谢谢了！

推荐答案

您可以在此 answer 只需稍作修改.您将需要修改MyControl类以使用Labels而不是CommandButtons.您还需要修改事件以传递文件名.

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:

用户窗体

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:

模块

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

通知者类

Option Explicit

Public Event Click(ByVal Filename As String)

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

MyControl类

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

这篇关于在VBA Excel中将超链接添加到动态创建的标签的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！