I have been tasked to create an excel worksheet that displays the progress of Performance Indicators in the form of a dynamic report. The structure of the report will be Grouped Lists of Performance Indicator Descriptions under their respective Headings and for a chart, based on that description to be displayed detailing the relevant month-by-month performance.

The main build has been done, however, it's the dynamic part of calling charts based on clicking on a cell that is stumping me.

What I want the end-user to do is click on one of the listed descriptions and for the relevant chart based on that description to be displayed on the right-hand side. Is there a way to do this via macro or VBA (preferred)? The charts and tables will be stored on a separate hidden worksheet in the same document.

Any help or the relevant code would be much appreciated

Thanks Folks.

CodePudding user response：

You can run a macro when a certain cell is selected by using the Worksheet.SelectionChange event.

I would start by putting the event handler into the sheet in question and test what the selected range is compared to the range you care about.

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
    If Target.row = row_of_kpi_desc And Target.Column = col_of_kpi_desc Then
        Call Other_Macro
    End If
End Sub

Other_Macro could copy the chart from this other hidden sheet and paste it to a specific location. There are ample examples of how to do this. Best of luck.

CodePudding user response：

You can use hyperlinks to trigger some specific action by using the Worksheet_FollowHyperlink event.

In the worksheet code module for the sheet where the user will be clicking cells:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    
    Debug.Print Target.TextToDisplay   'Text of the clicked cell
    Debug.Print Target.Range.Address   'the actual cell which was clicked

    'Process either of the above to figure out what action to take...

End Sub

And in a regular module:

'Sets up your links: select one or more cells and run this to turn the cell 
'  contents into hyperlinks which will trigger the event handler.
Sub MakeLinks()
    Dim c As Range
    For Each c In Selection.Cells
        c.Hyperlinks.Delete 'remove any existing link
        c.Worksheet.Hyperlinks.Add Anchor:=c, Address:="#LinkTarget()", _
                                   TextToDisplay:=c.Text
        'you can re-style the cells if you don't want the blue/underlined font
    Next c
End Sub


'This serves as a "dummy" destination for the hyperlinks
'  Just returns the clicked-on cell, so the selection doesn't jump around
Function LinkTarget() As Range
    Set LinkTarget = Selection
End Function

Once this is set up, clicking any of the links will trigger the event handler in the sheet module, and you can decide what action to take based on the text of the clicked link.

This is a useful alternative to having a bunch of buttons in a table-like structure, since you don't have to worry about tying buttons to specific rows (eg. when sorting)

Notes

• If you need to respond to clicks on multiple sheets then there's also the Workbook_SheetFollowHyperlink event (in the ThisWorkbook code module)
• This doesn't work with links created using the HYPERLINK() worksheet formula, since those type of links don't trigger the event handler.
• If your cell text is short then clicking the cell may "miss" the link - in that case you can pad the text with spaces on either side so it fits the cell better