Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Hilight Chart's Source Data?
iI am wondering if there is a way to use code to find cells that a chart is
linked to. For instance, if I right-click on a chart, and click on Source Data, I can then see the cells that the chart is linked to in the €˜Data Range or €˜Series tabs. Can I use a macro to automatically color the cells that feed that chart. I would envision clicking on a chart and running the code. Essentially: ActiveChart.ChartArea.Select Hilight the cells that are selected... So, I would just highlight the cells in this range. That way, I can easily see the charts source data. Is that possible? Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Hilight Chart's Source Data?
This is crude, I'm ashamed to post it, but you might like it !
Sub test() Dim sf As String Dim arr, v Dim rng As Range, rAll As Range Dim cht As Chart Dim sr As Series Set cht = ActiveChart If cht Is Nothing Then MsgBox "Select a chart" Exit Sub End If On Error Resume Next For Each sr In cht.SeriesCollection sf = sr.Formula sf = Mid$(sf, 9, Len(sf) - 9) ' strip =SERIES( ) arr = Split(sf, ",") For Each v In arr Set rng = Range(v) If Not rng Is Nothing Then If rAll Is Nothing Then Set rAll = rng Else Set rAll = Union(rAll, rng) End If Set rng = Nothing End If Next Next If Not rAll Is Nothing Then Application.Goto rAll End If End Sub Regards, Peter T "ryguy7272" wrote in message ... iI am wondering if there is a way to use code to find cells that a chart is linked to. For instance, if I right-click on a chart, and click on Source Data, I can then see the cells that the chart is linked to in the 'Data Range' or 'Series' tabs. Can I use a macro to automatically color the cells that feed that chart. I would envision clicking on a chart and running the code. Essentially: ActiveChart.ChartArea.Select Hilight the cells that are selected... So, I would just highlight the cells in this range. That way, I can easily see the chart's source data. Is that possible? Thanks, Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Hilight Chart's Source Data?
Here is a simple example that worked for me on a scatter chart with four
series, all separate regions on the sheet. It probably needs work to do what you want, including bullet proofing and extension to other sheets or workbooks, which I didn't check. If you can link it to selecting the chart somehow, then you can also create an "undo" method that links to de-selection of the chart and returns the cells back to their original formatting. This would require you to save the original formatting of the cells somehow, a problem I'll let you figure out! HTH, Eric ' ' This macro puts a bright red border around each range ' of data used in a chart ' Sub Highlight_Chart_Cells() Dim i As Long, j As Long Dim thisChart As Chart Dim Range1 As Range, Range2 As Range, Range3 As Range ' Set thisChart = ActiveChart For i = 1 To thisChart.SeriesCollection.Count Call Split_Series_Ranges(thisChart.SeriesCollection(i). Formula, Range1, Range2, Range3) ' ' If a range exists, highlight it with a bright red, thick border ' If (Not Range1 Is Nothing) Then For j = 1 To Range1.Areas.Count ' Highlight each area separately... With Range1.Areas(j) .BorderAround xlSolid, xlThick, 3 End With Next j End If ' If (Not Range2 Is Nothing) Then For j = 1 To Range2.Areas.Count ' Highlight each area separately... With Range2.Areas(j) .BorderAround xlSolid, xlThick, 3 End With Next j End If ' If (Not Range3 Is Nothing) Then For j = 1 To Range3.Areas.Count ' Highlight each area separately... With Range3.Areas(j) .BorderAround xlSolid, xlThick, 3 End With Next j End If ' Next i ' End Sub ' ' This subroutine takes as input the formula for one series on a ' chart and returns as output the three ranges (if they all exist) ' that make up the series: the title, the x-values and the y-values. ' Sub Split_Series_Ranges(inFormula As String, Range1 As Range, Range2 As Range, Range3 As Range) Dim i As Integer, j As Integer Dim tStr As String ' i = InStr(1, inFormula, "(") j = InStr(1, inFormula, ",") If (j - i 1) Then ' Title range exists Set Range1 = Range(Mid(inFormula, i + 1, j - i - 1)) Else Set Range1 = Nothing End If ' i = j j = InStr(i + 1, inFormula, ",") If (j - i 1) Then ' Category range exists Set Range2 = Range(Mid(inFormula, i + 1, j - i - 1)) Else Set Range2 = Nothing End If ' i = j j = InStr(i + 1, inFormula, ",") If (j - i 1) Then ' Value range exists Set Range3 = Range(Mid(inFormula, i + 1, j - i - 1)) Else Set Range3 = Nothing End If ' End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Hilight Chart's Source Data?
Chip Pearson's discussion of event handling in VBA discusses how to deal with
charts. He shows how to catch the activation event. Just look for the word oddball! http://www.cpearson.com/excel/Events.aspx |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to Hilight Chart's Source Data?
Wow! Super cool! Thanks for sharing, Peter and egun!!
Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "egun" wrote: Here is a simple example that worked for me on a scatter chart with four series, all separate regions on the sheet. It probably needs work to do what you want, including bullet proofing and extension to other sheets or workbooks, which I didn't check. If you can link it to selecting the chart somehow, then you can also create an "undo" method that links to de-selection of the chart and returns the cells back to their original formatting. This would require you to save the original formatting of the cells somehow, a problem I'll let you figure out! HTH, Eric ' ' This macro puts a bright red border around each range ' of data used in a chart ' Sub Highlight_Chart_Cells() Dim i As Long, j As Long Dim thisChart As Chart Dim Range1 As Range, Range2 As Range, Range3 As Range ' Set thisChart = ActiveChart For i = 1 To thisChart.SeriesCollection.Count Call Split_Series_Ranges(thisChart.SeriesCollection(i). Formula, Range1, Range2, Range3) ' ' If a range exists, highlight it with a bright red, thick border ' If (Not Range1 Is Nothing) Then For j = 1 To Range1.Areas.Count ' Highlight each area separately... With Range1.Areas(j) .BorderAround xlSolid, xlThick, 3 End With Next j End If ' If (Not Range2 Is Nothing) Then For j = 1 To Range2.Areas.Count ' Highlight each area separately... With Range2.Areas(j) .BorderAround xlSolid, xlThick, 3 End With Next j End If ' If (Not Range3 Is Nothing) Then For j = 1 To Range3.Areas.Count ' Highlight each area separately... With Range3.Areas(j) .BorderAround xlSolid, xlThick, 3 End With Next j End If ' Next i ' End Sub ' ' This subroutine takes as input the formula for one series on a ' chart and returns as output the three ranges (if they all exist) ' that make up the series: the title, the x-values and the y-values. ' Sub Split_Series_Ranges(inFormula As String, Range1 As Range, Range2 As Range, Range3 As Range) Dim i As Integer, j As Integer Dim tStr As String ' i = InStr(1, inFormula, "(") j = InStr(1, inFormula, ",") If (j - i 1) Then ' Title range exists Set Range1 = Range(Mid(inFormula, i + 1, j - i - 1)) Else Set Range1 = Nothing End If ' i = j j = InStr(i + 1, inFormula, ",") If (j - i 1) Then ' Category range exists Set Range2 = Range(Mid(inFormula, i + 1, j - i - 1)) Else Set Range2 = Nothing End If ' i = j j = InStr(i + 1, inFormula, ",") If (j - i 1) Then ' Value range exists Set Range3 = Range(Mid(inFormula, i + 1, j - i - 1)) Else Set Range3 = Nothing End If ' End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Including Totals in Chart's data table | Charts and Charting in Excel | |||
return chart's source data location/range | Excel Programming | |||
Programmatically determine Chart's data source | Excel Programming | |||
Alignment within a chart's data table | New Users to Excel | |||
How do I return the values of a chart's data range? | Excel Programming |