CODEWITHSUNDEEP
vbaexcel
Jonathan Gariepy
Jonathan Gariepy

Reputation: 53

How to assign a selected chart name to a cell (not the other way around)?

I'm still a beginner with VBA and I'm learning a ton from stackoverflow and from general googling.

I'm hitting a wall on this very general task : I'm trying to show a text giving a very general explanation of a chart when it is selected / hovered-over.

The way I was thinking of approaching this was to create a tab with all my chart names (which I already have for other tasks) and create a little text for each of them. A cell (the VBA part) would contain the selected chart name that I could use to do a simple vlookup to fetch the explanation.

I tried to look on google how to do this and I'm usually pretty successful with forums and such, but there are sooooo many information on how to name a chart name based on a cell that I can't seem to find information on how to name a cell based on a chart name.

Edit : was cut off while typing by my newborn waking up, my bad completely forgot to come back and add my attempted code !!!

Sub Test_Chart_Name()
    Dim T As String
    T = ActiveChart.ChartTitle.Text
    Range("AM41").Value = T
End Sub

So far it works when I run it, I do believe I should be able to make it run automatically whenever I select a new chart but right now the wrong behavior is that it display the chart title instead of the name I assigned to it (ie it paste 'Pay per month in dollars' instead of 'Monthly_pay'.

Upvotes: 0

Views: 156

Answers (2)

Jon Peltier
Jon Peltier

Reputation: 6063

Here's how I approached the request. I wrote a macro that looks up the description of the chart, and displays it in a message box. For each chart you want to run it with, right click the chart, click Assign Macro from the pop-up menu, and select the macro. When you click on the chart, the macro runs. You can also run the macro anytime from Developer tab > Macros or the shortcut Alt+F8.

I set up a lookup range on the active sheet (it could be anywhere) with chart names in the first column and descriptions in the second.

Sub PopUpChartDescription()
  Dim rTable As Range, rCell As Range
  Dim sName As String, sDescription As String, sCaller As String

  On Error Resume Next
  sCaller = Application.Caller
  If Len(sCaller) > 0 Then  ' macro called by clicking on a chart
    ' activate the chart or it is deactivated after the macro runs
    ActiveSheet.ChartObjects(sCaller).Activate
    DoEvents
    DoEvents
  End If
  On Error GoTo 0

  If Not ActiveChart Is Nothing Then  ' so ActiveChart is something, eh?
    sName = ActiveChart.Parent.Name

    Set rTable = ActiveSheet.Range("DisplayTable")  ' my lookup range
    Set rCell = rTable.Columns(1).Find(What:=sName)  ' find the chart name

    If Not rCell Is Nothing Then  ' so rCell containing chart name was found
      sDescription = rCell.Offset(, 1).Text
      ' show the description
      MsgBox sDescription, vbInformation, "Chart Description"
    End If
  End If

End Sub

Upvotes: 1

Lina
Lina

Reputation: 291

You could try the code below: 

Sub Test_Chart_Name()
        Dim T As String
        T = ActiveChart.Parent.Name
        Range("AM41").Value = T
    End Sub

Hope that helps!

Upvotes: 1

Related Questions