Forum Discussion

Martin Crean's avatar
Martin Crean
Tin Contributor
Apr 25, 2018

Read Data from Second Sheet & Display Resulting Chart on First Sheet

Hello,

 

What I would like to do is to click on a part number on this 1st sheet, have it find the part number wherever it may be on the second sheet, read the all of the data to the right of the part number and then display the chart I've chosen. 

 

The attached sample file is annotated. I've simplified it as much as possible for clarity. 

 

Thank You :) 

 

Marty

excel

1 Reply

  • Matt Mickle's avatar
    Matt Mickle
    Bronze Contributor
    Apr 25, 2018

    Please try adding this code to the Parts Code Module:

     

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
    'Part to find
    Part = Target.Value
    'Find row on History Sheet
    Set PartObj = Sheets("Data (History)").Range("A1:A500").Find(Part)
    PartRow = PartObj.Row
    'Find last occupied cell in Part Row
    LastCol = Sheets("Data (History)").Cells(PartRow, Columns.Count).End(xlToLeft).Column
    'Activate History Sheet
    Sheets("Data (History)").Activate
    
    'Activate Chart
    ActiveSheet.ChartObjects("Chart 2").Activate
    'Set Source Ranges....
    Set Rng1 = Sheets("Data (History)").Range(Sheets("Data (History)").Cells(PartRow, "B"), Sheets("Data (History)").Cells(PartRow, LastCol))
    Set Rng2 = Sheets("Data (History)").Range(Sheets("Data (History)").Cells(2, "B"), Sheets("Data (History)").Cells(2, LastCol))
    'Change Source Data
    ActiveChart.SetSourceData Source:=Union(Rng1, Rng2) 'Combine two ranges
    'Add Title
    ActiveChart.HasTitle = True
    ActiveChart.ChartTitle.Text = Sheets("Data (History)").Range("A" & PartRow).Value
    Sheets("Data (History)").Range("A2").Select

End Sub

     

    1. Alt + F11

    2. Click on the Parts Icon in the Project Explorer

    3. Paste this code to the "blank" window on the right

     

    After doing this return to the Parts worksheet and test by double clicking a part