CODEWITHSUNDEEP
vbaexcel
Gary's Student
Gary's Student

Reputation: 96763

Counting Hyperlinks

I am having a tiny problem with Inserted hyperlinks. If I start with a blank sheet and insert a hyperlink and then copy it to two other cells using two individual Copy/Pastes:

Sub hyper1()
    Cells.Clear
    With ActiveSheet
        .Hyperlinks.Add Anchor:=Range("A1"), Address:="http://www.cnn.com/", TextToDisplay:="News"
        Range("A1").Copy Range("A2")
        Range("A1").Copy Range("A3")
        MsgBox .Hyperlinks.Count
    End With
End Sub

Three cells get filled and Excel correctly reports the number of hyperlinks as 3.

However if I fill the second two cells with a single Copy/Paste:

Sub hyper2()
    Cells.Clear
    With ActiveSheet
        .Hyperlinks.Add Anchor:=Range("A1"), Address:="http://www.cnn.com/", TextToDisplay:="News"
        Range("A1").Copy Range("A2:A3")
        MsgBox ActiveSheet.Hyperlinks.Count
    End With
End Sub

The same three cells get filled, but Excel reports the number of hyperlinks as 2!!

Which is broken, Excel 2007 or my computer ??

Upvotes: 2

Views: 1446

Answers (2)

user7168530
user7168530

Reputation: 

Sub hyper3()
    Cells.Clear
    With ActiveSheet
        .Hyperlinks.Add Anchor:=Range("A1"), Address:="http://www.cnn.com/", TextToDisplay:="News"
        Range("A1").Copy Range("A2,A3")
        MsgBox ActiveSheet.Hyperlinks.Count
    End With
End Sub

The comma separation splits them into two ranges instead of one

Upvotes: 4

user7168530
user7168530

Reputation:

I suspect its because in the second macro, the hyperlink is copied as a single range object as opposed to two individual ranges (like the first one).

Excel will treat this as a Range object (that represents the range the specified hyperlink is attached to). It will therefore count this as one hyperlink instead of two.

You can see this if you click either of cells A2 or A3 after the macro has run (they will both change colour at the same time indicating that the hyperlink has been selected).

Upvotes: 4

Related Questions