vba - 使用 VBA 向表格中的单元格添加超链接 - Excel

Question

我有一张 Excel 表格，上面有两张名为“投诉”和“添加行”的表格。

我正在使用 Add Row 工作表向名为 ComplaintsTable 的表中添加一行(在最后一行有值之后)。在投诉表中，我正在使用与命令按钮配对的宏来执行此操作。

我的代码如下所示:

Private Sub CommandButton1_Click()
Dim LastRow As Long, ws As Worksheet, ws1 As Worksheet, newRow As ListRow

Set ws = Sheets("Complaints")
Set ws1 = Sheets("Add Row")
Set tbl = ws.ListObjects("ComplaintsTable")
Set newRow = tbl.ListRows.Add

With newRow
    .Range(2) = ws1.Range("C1").Value 'Complaint Yes/No
    .Range(12) = ws1.Range("C6").Value 'PCE Yes/No
End With

newRow.Range(4) = ws1.Range("C4").Value 'Subject
newRow.Range(21) = ws1.Range("C5").Value 'Entered Date

'To add Hyperlink
If (ws1.Range("C1").Value = "Yes") Then
    ws.Hyperlinks.Add Anchor:=tbl.newRow.Range(3), _
    Address:=ws1.Range("F3").Value, _
    ScreenTip:="Open Complaint in EtQ", _
    TextToDisplay:=Worksheets("Add Row").Range("F2").Value
End If

If (ws1.Range("C6").Value = "Yes") Then
    'To add hyperlink and PCE Number
    ws.Hyperlinks.Add Anchor:=tbl.newRow.Range(13), _
    Address:=ws1.Range("F8").Value, _
    ScreenTip:="Open PCE in EtQ", _
    TextToDisplay:=ws1.Range("F7").Value
End If
End Sub

不知何故，当我单击命令按钮添加值时，它不会添加任何东西!我哪里错了？

Answer 1

这是您重构、清理后的带有屏幕截图的代码。正如@Ibo 和我自己所提到的，问题很可能在于您已经声明并设置了newRow。作为一个范围，但随后将其用作表格的属性，这是不可能的。

Option Explicit

Private Sub CommandButton1_Click()
    Dim wsComplaints As Worksheet, wsAddRow As Worksheet
    Dim tblComplaints As ListObject
    Dim lngRows As Long

    With ThisWorkbook
        Set wsComplaints = .Worksheets("Complaints")
        Set wsAddRow = .Worksheets("Add Row")
    End With

    Set tblComplaints = wsComplaints.ListObjects("ComplaintsTable")

    tblComplaints.ListRows.Add

    lngRows = tblComplaints.ListRows.Count

    With tblComplaints
        .DataBodyRange(lngRows, 2) = wsAddRow.Cells(1, 3)
        .DataBodyRange(lngRows, 4) = wsAddRow.Cells(4, 3)
        .DataBodyRange(lngRows, 12) = wsAddRow.Cells(6, 3)
        .DataBodyRange(lngRows, 21) = wsAddRow.Cells(5, 3)
    End With

    If wsAddRow.Cells(1, 3) = "Yes" Then
        tblComplaints.DataBodyRange(lngRows, 3).Hyperlinks.Add _
        Anchor:=tblComplaints.DataBodyRange(lngRows, 3), _
        Address:=CStr(wsAddRow.Cells(3, 6)), _
        ScreenTip:="Open complaint in EtQ", _
        TextToDisplay:=CStr(wsAddRow.Cells(2, 6))
    End If

    If wsAddRow.Cells(6, 3) = "Yes" Then
        tblComplaints.DataBodyRange.Hyperlinks.Add _
        Anchor:=tblComplaints.DataBodyRange(lngRows, 13), _
        Address:=CStr(wsAddRow.Cells(8, 6)), _
        ScreenTip:="Open PCE in EtQ", _
        TextToDisplay:=CStr(wsAddRow.Cells(7, 6))
    End If
End Sub

解决方案截图。