link to excel chartS from various cellS

July 29, 2011 at 11:59:17
Specs: Windows XP
I have 10 charts sitting in their own sheets/tabs in my workbook.
Each chart is labelled "Sign 1 Sketch", "Sign 2 Sketch", ... "Sign 10 Sketch"

On the sheet called 'Input', each cell in the range B31:K31 has the following formula:

=IF(B34="OK","Sign "&B$2&" Sketch","")

That was from column B and either displays:

(BTW B2:K2 has the numbers 1 to 10, and B34 to K 34 is the results of an Error in will either have "OK" or "ERROR" in it.)
Sign 1 Sketch

Column C and displays either

Sign 2 Sketch

and so forth to columK where the options are

Sign 10 Sketch

When the name of the sign is displayed in a cell and that same cell is selected (one of B31 toK31), I want the chart tab/sheet with the matching name to be displayed.

It is my understanding that it cannot be hyperlinked and a piece of VBA code is required. I did some searching and found the following :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not Intersect(Target, Range("B31")) Is Nothing Then
    On Error Resume Next
    If Err.Number <> 0 Then
        MsgBox "No such chart exists.", vbCritical, "Chart Not Found"
    End If
    On Error GoTo 0

End If 

How do I modify this code or what is the replacement code required in order to achieve what is described above?

If further clarification is required please let me know.

See More: link to excel chartS from various cellS

Report •

July 29, 2011 at 15:08:56
If all you are asking for is that the chart whose name is selected within B31:K31 is activated, then the only thing that needs to be changed is:

Range("B31") --> Range("B31:K31")

The Intersect method will now check to see that the selected cell is within the specified range.

Another change you might consider making is adding this line before the Intersect line:

If Target.Cells.Count > 1 Then Exit Sub

That way the user can select more than one cell within B31:K31 and not get an error. If the user selects more than 1 cell, he can't expect more than one chart to be displayed, so the code exits.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question