?
Solved

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

Posted on 2011-10-14
10
Medium Priority
?
652 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:RobStl
  • 7
  • 3
10 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36969061
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
 
LVL 24

Expert Comment

by:StephenJR
ID: 36969081
You can of course assign a macro to a shape by right-clicking on it.
0
 

Author Comment

by:RobStl
ID: 36969110
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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 24

Expert Comment

by:StephenJR
ID: 36969143
You can add this line in your code above (where x is name of hyperlink macro).

s.OnAction = "x"
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36969149
OK, didn't read your question properly so might need a bit more than that, but let's see...
0
 
LVL 24

Expert Comment

by:StephenJR
ID: 36969184
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
 

Author Comment

by:RobStl
ID: 36969204
Okay, I'll look into this soon. I'll get back to you once I have tried it out.
0
 
LVL 24

Accepted Solution

by:
StephenJR earned 2000 total points
ID: 36969221
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
 

Author Comment

by:RobStl
ID: 36969270
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
 
LVL 24

Expert Comment

by:StephenJR
ID: 36969304
My pleasure.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question