Excel VBA - Create Hyperlink on a Shape Object that Links & Filters Another Sheet

Hey,

I am attempting to create a hyperlink on a shape object's textframe using VBA in Excel 2007.  I'm not sure if this is even possible, but I figured I'd ask for some guidance.  I would like the hyperlink to automatically link to another spreadsheet (Sheets("Data")) within the same workbook, then filter that spreadsheet by the given Project Number.  See code for more details:

 
Sub CreateHyperlinks()
    Dim s As Shape
    Dim projectNumber As String
    
    projectNumber = "P38947938"
    Set s = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 50, 50, 50, 25)
    s.TextFrame.Characters.Text = projectNumber
    's.Hyperlink.????
    'TO DO:
    'Create hyperlink (using the shape's textframe, possibly) to link to Sheets("data"),
    'Then filter Sheets("data") by Project Number.
    'ie: Cells("A1") is the Project Number column in Sheets("data");
    '''Filter A1 by the given project number passed from the shape's textframe
    
End Sub

Open in new window


Is this even possible? I'm pretty sure that you can do this with text within a cell (but have not done that yet either).  Any help would be greatly appreciated!

Regards,
Rob
RobStlAsked:
Who is Participating?
 
StephenJRCommented:
This will actually do some filtering. You might like to post a workbook if it doesn't work as I'm not sure how your data are laid out.
Sub CreateHyperlinks()
    Dim s As Shape
    Dim projectNumber As String
    
    projectNumber = "P38947938"
    Set s = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 50, 50, 50, 25)
    s.TextFrame.Characters.Text = projectNumber
    s.OnAction = "x"
End Sub

Sub x()

Application.ScreenUpdating = False

Sheets("Data").Activate

With ActiveSheet
    .AutoFilterMode = False
    .Range("A1").AutoFilter Field:=1, Criteria1:=Sheets("Sheet1").Shapes(Application.Caller).TextFrame.Characters.Text
End With

Application.ScreenUpdating = True

End Sub

Open in new window

0
 
StephenJRCommented:
Is there a specific reason why you want this method, as opposed to e.g. clicking on a button or double-clicking a cell?
0
 
StephenJRCommented:
You can of course assign a macro to a shape by right-clicking on it.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
RobStlAuthor Commented:
Yes there is. I have multiple projects that are arranged in a Gantt chart type of view, using these shapes.  I am creating a dynamic dashboard that generates each of these shapes (and more) based off of the data available in Sheets("Data").  

I don't think assigning a macro to each shape would work for me though since these shapes are dynamically created at run-time, meaning I also need these hyperlinks to be created at run-time.
0
 
StephenJRCommented:
You can add this line in your code above (where x is name of hyperlink macro).

s.OnAction = "x"
0
 
StephenJRCommented:
OK, didn't read your question properly so might need a bit more than that, but let's see...
0
 
StephenJRCommented:
So, here you can utilise the properties of the shape calling the macro:
Sub CreateHyperlinks()
    Dim s As Shape
    Dim projectNumber As String
    
    projectNumber = "P38947938"
    Set s = ActiveSheet.Shapes.AddShape(msoShapeRectangle, 50, 50, 50, 25)
    s.TextFrame.Characters.Text = projectNumber
    s.OnAction = "x"
End Sub

Sub x()

MsgBox ActiveSheet.Shapes(Application.Caller).TextFrame.Characters.Text

End Sub

Open in new window

0
 
RobStlAuthor Commented:
Okay, I'll look into this soon. I'll get back to you once I have tried it out.
0
 
RobStlAuthor Commented:
That works perfectly :)   Each shape filters by the given project number.  I have not used the OnAction method before, but it works perfectly for what I need.  I actually like it much better than setting the text as hyperlink text.  Thanks so much for your help!

~Rob
0
 
StephenJRCommented:
My pleasure.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.