Obtaining the chart datalabel string value with VBA

[coreyalderin]

How can I obtain the string value of a data label, with VBA?

Here is what I have done but it returns an empty string, even though there is a value in the data label. I want the value to be placed into a variable called "Text".

Text = Selection.Characters.Text

The code that I have for selecting the data label is working correctly, but the variable Text is always "".

Thank you

 

[pgc01]

Hi
Welcome to the board

It works for me.

Please post
- where is the chart, what's its name, is it a chart sheet or a chart embedded in a worksheet
- how was the label created, what's its name
- the rest of the code, including the dimensioning of the variables.

 

[coreyalderin]

I was trying to avoid a lengthy post but this might help.

What I am trying to do:

• User selects a label in a chart that is embedded in a worksheet. For example, in a bar graph the label indicating tha value of the graph.
• A pop-up window appears asking the user to enter text that they want included on the label
• That text is then included with the value that is already there, as a superscript.

But the "Text" variable always returns "" (not Empty). So the end result then adds what the user enters, but deletes the originol label value.

This did work before I upgraded to the new version of Excel.

Note: The "number" variable at the bottom is created in a seperate Sub. That Sub is only used to obtain the number that Excel puts on all charts created in a worksheet.

I hope this is what you need.

Thanks again

Code

Private Sub EmbChart_Select(ByVal ElementID As Long, _
ByVal Arg1 As Long, ByVal Arg2 As Long)


If Arg2 > 0 Then
'Obtain superscript text (user inputed)
sScript = InputBox("Type in the superscript exactly how you want it", _
"Superscript", "Enter superscript here")

If sScript <> "" Then

Length1 = Len(sScript)


'Enter the superscript
ActiveChart.SeriesCollection(Arg1).Points(Arg2).DataLabel.Select

Text = Selection.Characters.Text

Length2 = Len(Selection.Characters.Text)

Selection.Characters.Text = Text & sScript

Selection.AutoScaleFont = False
Selection.AutoScaleFont = False
With Selection.Characters(Start:=Length2 + 1, Length:=Length1).Font
.Superscript = True
End With
ActiveWindow.Visible = False
ActiveSheet.ChartObjects(Number).Activate
End If
End If

 

[coreyalderin]

OK, so I got the problem solved and now there is a new one.

I have been working on this for hours, and of course, as soon as I post something I figured it out.

I replaced:

Text = Selection.Characters.Text

with

Text = ActiveChart.SeriesCollection(Arg1).Points(Arg2).DataLabel.Text


BUT, now I have a new problem. VBA is not letting me superscript only a certain portion of the label. It is makeing me superscript all or none, with VBA. Again, this part also worked previously, before upgrading to the new Excel.

Thanks

 

[pgc01]

Hi

I did a small test to add the superscripted text.

This example works for me. Please test it and then adapt to your case:

- create a chart in a chart sheet

- in the chart sheet module paste:

Private Sub Chart_Select(ByVal ElementID As Long, ByVal Arg1 As Long, ByVal Arg2 As Long)
Dim dtLbl As DataLabel
Dim sScript As String
Dim lLen As Long
 
If ElementID = xlDataLabel And Arg2 > 0 Then
 
    'Obtain superscript text (user inputed)
    sScript = InputBox("Type in the superscript exactly how you want it", _
        "Superscript", "Enter superscript here")
 
    If sScript <> "" Then
        Set dtLbl = Me.SeriesCollection(Arg1).Points(Arg2).DataLabel
 
        With dtLbl
            lLen = Len(.text)
 
            .AutoScaleFont = False
            .AutoScaleFont = False
 
            With .Characters(Start:=lLen + 1)
                .text = sScript
                .Font.Superscript = True
            End With
        End With
    End If
End If
End Sub

- test


P. S. In your code you did not declare the variables you are using. That is bad practice.
The best thing to do is, in the VB Editor, to go to Tools->Options->Editor and check "Require Variable Declarations". This will insert "Option Explicit" in every new module you create and forces you to declare all variables.

 

[coreyalderin]

What does the "me" stand for in this line:

Set dtLbl = Me.SeriesCollection(Arg1).Points(Arg2).DataLabel



Thanks

 

[pgc01]

Hi

Did you test the code with my setup and is it working?

 

[coreyalderin]

Yes, I have tried it and it does not work. What does the "Me" stand for in this line:

Set dtLbl = Me.SeriesCollection(Arg1).Points(Arg2).DataLabel

 

[pgc01]

The "Me" keyword refers to the object that is the current instance of the class that the code is implementing. In this case, since you are using it in a chart module, "Me" refers to the chart. If you'd use it in a form module it would refer to the form.

Make sure you have the setup I wrote in my post.

Have you created a chart in a chart sheet and posted the code into the chart module?

What is your excel version (I've tested in excel 2000 but I can test it in excel 2010 tomorrow)?

 

[coreyalderin]

I am using Excel 2010. From what I have seen from other posters, it is not possible to format part of the string.

In Excel 2003, I used record to help me create the code needed to format only a part of the string. But now, when I record, it formats teh whole string, even though I have only selected a portion of it.

Thanks