Add Hyperlink to a cell in a Table using VBA - Excel

Question

I have an Excel with two sheets named "Complaints" and "Add Row".

I am using the Add Row sheet to add a row (after the last row with values) to a table named ComplaintsTable in Complaints sheet and I am using a macro paired with a command button to do this.

My code looks like this:

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

Somehow when I clicked the command button to add values it doesn't add anything! Where am I going wrong?

Step through code with F8 and watch the locals window, see if values are as expected. Or add debug.assert statements to check values are as expected or even use the watch window and add watches. To name a few ways to debug. — QHarr
– QHarr, Commented Dec 14, 2017 at 23:50
It probably doesn't like you qualifying newrow with tbl. e.g. tbl.newRow.Range(3) in your hyperlinks though you should get an error i would have thought. — Carol
– Carol, Commented Dec 14, 2017 at 23:55
it works fine here! Do you have 1 row only on the table? The problem is your button, right click on it and see if you have assigned the right name for the macro — Ibo
– Ibo, Commented Dec 14, 2017 at 23:56
@lbo yeah, I copied this to a new Excel file and it works except for the hyperlink part. So, how to add hyperlinks? — nandyalu
– nandyalu, Commented Dec 15, 2017 at 0:18
@Ibo, OP appears to be using an ActiveX command button rather than a Form Control button. You cannot assign a macro to an ActiveX command button by right clicking it. You add code to the button's Click, GotFocus, LostFocus or any other event in the Sheet module where the button sits. — SilentRevolution
– SilentRevolution, Commented Dec 15, 2017 at 0:42

SilentRevolution · Accepted Answer · 2017-12-18 12:21:05Z

Here is your refactored, cleaned up code with screenshots. As mentioned by both @Ibo and myself, The problem most likely lies in the fact that you've declared and set newRow as a range but then used it as a property of your table which is impossible.

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

Screenshots of the solution.

SilentRevolution · Accepted Answer · 2017-12-15 01:36:11Z

0

If you click the button and nothing, not even an error of any sort, happens there may be several issues.

To start of with, as mentioned by @Carol, the newRow is not supposed to be qualified by tbl as newRow is not a property or method of tbl

Possibility 1:
You've added a Form Control button to your sheet which you are unable to assign a Private Sub CommandButton1_Click() because, well, it is private and can only be used within the code module it is placed in, it cannot be referenced outside of it.

Possibility 2:
You've added an ActiveX CommandButton, wrote the Private Sub CommandButton1_Click() but then changed the name of the button. In that case, change the CommandButton1 to whatever you've named your button.

Possibility 3:
You've encountered an error, hit debug and the code is paused. As long as the code is paused, no new event will fire and thus your button will appear to do nothing. This is recognized by a line of your code highlighted in yellow. You need to fix the line which caused the error and resume your code by hitting F5 or hitting the stop icon usually located somewhere near the top of your VBA window.

edited Dec 15, 2017 at 1:36

answered Dec 15, 2017 at 1:10

SilentRevolution

1,5132 gold badges16 silver badges31 bronze badges

1 Comment

nandyalu Over a year ago

No, No and No. That is not the problem. The problem is with the if loop to add the hyperlink, it seems like the hyperlink cannot be used with table.

Ibo · Accepted Answer · 2017-12-15 04:31:20Z

0

Your text to display must be a zero-length string and that is it is failing to create the hyperlink.

define the text to display like this before to make sure this line is the problem:

myStr=Worksheets("Add Row").Range("F2").Value

Try to define variables and range objects before adding the hyperlink, instead of using the .value etc put them in a string variable and make sure all of them have a valid value. If you try this, it should work, otherwise follow the above instruction and you will find where the problem is:

Replace this block and if it worked, change the other block in the same way:

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:=IIf(mystr <> "", mystr, "Click Here")
End If

answered Dec 15, 2017 at 4:31

Ibo

4,3197 gold badges51 silver badges75 bronze badges

2 Comments

nandyalu Over a year ago

I tried to do this and that gave me "Run-time error '438' - Object doesn't support this property or method" pointing to the hyperlink command.

Ibo Over a year ago

one of the parameters are not defined and that is why you get that error, as I said you should define them beforehand and go through your code with F8 and see if you get the string variables defined as you should and also check that the range object that is used in the hyperlink definition block is no nothing. If you could not handle it, you should upload your workbook and then I can have a look and tell you where you have a problem

nandyalu · Accepted Answer · 2017-12-24 21:20:11Z

I have changed the code as follows and it is working perfectly fine without any errors.

Private Sub AddRow_Click()
Dim LastRow As Long, ws As Worksheet, ws1 As Worksheet
Dim newRow As ListRow ', tbl As ListObjects
Dim cmpNo As String, pceNo As String

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

With newRow
    .Range(1) = ws1.Range("C1").Value 'Complaint Yes/No
    .Range(11) = ws1.Range("C6").Value 'PCE Yes/No
    .Range(3) = ws1.Range("C4").Value 'Subject
    .Range(20) = ws1.Range("C5").Value 'Entered Date
End With

'To add Hyperlink
If (ws1.Range("C1").Value = "Yes") Then
    Call ActiveSheet.Hyperlinks.Add(newRow.Range(2), ws1.Range("F3").Value, "", "Open in EtQ", ws1.Range("F2").Value)
End If

If (ws1.Range("C6").Value = "Yes") Then
    Call ActiveSheet.Hyperlinks.Add(newRow.Range(12), ws1.Range("F8").Value, "", "Open in EtQ", ws1.Range("F7").Value)
    'To add hyperlink and PCE Number
End If
End Sub

The problem with the code is that "newRow.Range" does not work with the " hyperlinks.add". I fugered out this a few days ago but I didn't get a chance to post this.

I appreciate all your help!

Collectives™ on Stack Overflow

Add Hyperlink to a cell in a Table using VBA - Excel

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

1 Comment

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related