CODEWITHSUNDEEP
vbaexcel
KamilKrzes
KamilKrzes

Reputation: 15

excel vba add hyperlink to shape in group

I have absolutely no idea why i cannot add hyperlink to shape but only if its trigged by in-cell function. When I run it manually from immediate window, everything is working well. Also I don't have any troubles with doing it manually.

I have also no idea how write good error handling in vba

This is sample code.

function testCase(xlLabelName as String)
Dim sht As Worksheet
    For Each sht In ActiveWorkbook.Sheets
        If shapeExist(sht, xlLabelName) Then
            sht.Hyperlinks.Add Anchor:=sht.Shapes(xlLabelName), Address:="", SubAddress:="a1" 
            testCase = "Bingo"
        End If
    Next sht
End Function

From immediate window I get Bingo answer and in cell I get #arg! error and this is only line which is not executed. I tried to ungroup shapes first using 

Dim gshp As Shape
Dim shrng As ShapeRange
Dim tmp As String
Set gshp = sht.Shapes(getParentShape(xlLabelName))
tmp = gshp.Name
Set shrng = gshp.Ungroup
sht.Hyperlinks.Add Anchor:=sht.Shapes(xlLabelName), Address:="", SubAddress:="a1"
shrng.Regroup
shrng.name = tmp

But it also failed. And still immediate window gives me correct answer (bingo) All other operations are done as I needed (painting), only this one fails. If I remove this problematic line I got correct response in cell. The shape I'm trying to deal with is msoFreeform
The function to check if shape exist is just loop through all shapes in sheet and compare names (Lame but for now its working)

Thanks in advance.

Upvotes: 1

Views: 831

Answers (1)

David G
David G

Reputation: 2347

If I were you I would assign a macro to my shape so that it would run the following sub when clicked:

sub hyperlinkShape
    ThisWorkbook.FollowHyperlink ("C:\temp\test.xlsx")
End sub

If you're looking for a hyperlink within the workbook, such as changing sheets, you can write 

sub hyperlinkShape
    Sheets("Desiredsheet").Select
End sub

This way the hyperlink would not be attached to the shape, but the sub. Clicking the shape can launch the sub.

For error handling, I suggest you read the answers from this thread I posted a few months back: VBA Excel simple Error Handling

Upvotes: 1

Related Questions