Write a button to run macro on *ANY* chart area clicked.

[Lawrence at Carderock]

How do I identify an object in a macro when I click on it? I want to operate
on the object and must have some recognizable identity to use in the macro
because I want the macro to operate on any object (really a chart) that I
click on. I know how to identify an arbitrary cell by clicking on it and how
to use the identity to run in a visual basic macro. For example:
co = ActiveCell.Offset(0, -1).Range("A1").Column
ro = ActiveCell.Offset(0, -1).Range("A1").Row
give me the cell identity of the cell that I click on -- (ro, co) being the
cell that was clicked on. Is there some way to identify a "Chart Area" in
such a manner? --LJM

 

[minimaster]

I have fairly versatile routine to identify the current type of
selected object for adjusting its color beyond the normal excel
methods for color adjustments. May be this code will give you some
ideas how to identify various selected objects with VBA code

Sub AdjustColor()
Dim colStart As Long
Dim colID As Long
Dim newColor As Long
Dim myChart As String
Dim selecName As String
Dim sID As Long
Dim pID As Integer
Dim c As ChartObject
Dim RGBval As RGB
Dim shpR As ShapeRange

On Error Resume Next
If ActiveWorkbook Is Nothing Then
MsgBox "Open workbook and try again!"
ElseIf Selection Is Nothing Then
pID = MsgBox("Make a selection and try again!", vbInformation,
"Nothing selected!")
Else
Debug.Print TypeName(Selection)
Debug.Print Selection.Name
On Error GoTo 0
GetSelectionColor colStart, colID

Select Case TypeName(Selection)
Case "Range"
newColor = PickNewColor(CDbl(colStart), colID)
Selection.Interior.ColorIndex = colID
Case "Series"
sID = Selection.PlotOrder
newColor = PickNewColor(CDbl(colStart), colID, , True)
ActiveChart.SeriesCollection(sID).Select
Selection.Interior.ColorIndex = colID
Case "Point"
sID = Selection.Parent.PlotOrder
pID = pointID()
newColor = PickNewColor(CDbl(colStart), colID, True, True)
ActiveChart.SeriesCollection(sID).Points(pID).Select
Selection.Interior.Color = newColor
Case "PlotArea"
newColor = PickNewColor(CDbl(colStart), colID, , True)
ActiveChart.PlotArea.Select
Selection.Interior.ColorIndex = colID
Case "ChartArea"
newColor = PickNewColor(CDbl(colStart), colID, , True)
ActiveChart.ChartArea.Select
Selection.Interior.ColorIndex = colID
Case "Legend"
newColor = PickNewColor(CDbl(colStart), colID, True, True)
ActiveChart.Legend.Select
Selection.Interior.ColorIndex = ClosestColor(newColor)
Case "LegendEntry", "DataTable", "Axis"
MsgBox "Error: This object can't be colored!"
Exit Sub

Case "Rectangle", "Oval", "TextBox", "Drawing"
On Error GoTo err_hdl
Set shpR = Selection.ShapeRange
On Error GoTo 0
ActiveWorkbook.Windows(1).Activate
If ActiveSheet.Type = xlWorksheet Then
' shpR.Parent.TopLeftCell.Select '
does not work anymore in XL2007
shpR.Item(1).TopLeftCell.Select ' this works in
XL2007
End If
Debug.Print "in ", colID, colStart
newColor = PickNewColor(CDbl(colStart), colID, True)
Debug.Print "out ", colID, colStart, newColor

shpR.Select
With shpR.Fill
.ForeColor.RGB = newColor
End With

Case Else
MsgBox "Color picking for " & TypeName(Selection) & " via
this add-in is not implemented."
' Stop
End Select
RGBval = buildRGB(colStart)
Debug.Print "ActiveWorkbook.Colors(" & str(colID) & ") = RGB("
& RGBval.R & "," & RGBval.G & "," & RGBval.B & ")"
perhapsupdateRGBtext
End If
Exit Sub
err_hdl:

MsgBox "Error: Please use standard Excel methods to modify the
color of this object"

End Sub