Problem with Chart MouseUp Event

[largeselection]

Hello,

For some reason I can't get the Ch_MouseUp event to fire. I'm using a modified version of some code Andrew Poulsom provided to another user to display labels when the mouse is clicked down and then have them not visible when the mouse button is released.

However, I have it working so that the Ch_MouseDown event works fine and will create the label that I want, but it creates and stays regardless of whether I hold the mouse button or just click. So the MouseUp event does not hide the label.

Any ideas why it wouldn't work? I'm using a chart that is embedded in a sheet so perhaps that is the issue? Just confusing because the Ch_MouseDown event does work.

I'm also trying to think of an elegant way to hide the label easily because there are a lot of datapoints, so I would want to be able to click one and then release to have it disappear or click-again to disappear or something like that.

Thanks for your help!

 

[Andrew Poulsom]

Can you post your code please?

 

[largeselection]

The MAN himself! Thanks for taking a look! This is what I'm working with (I know it's not pretty...)

In the Sheet Module:

Dim MyChart As New Class1
Private Sub Worksheet_Activate()
    Set MyChart.Ch = ActiveSheet.ChartObjects(1).Chart
End Sub

In the Class1 Module Ch_MouseDown:

Public WithEvents Ch As Chart
Dim IDNum As Long
Dim a As Long
Dim b As Long
 
Private Sub Ch_MouseDown(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Dim Txt As String
    Dim SFormula As String
    Dim FirstComma As Integer
    Dim SecondComma As Integer
    Dim CloseParen As Integer
    Dim counter As Long
    Dim rcell As Range
    
    
    Txt = ""
    Ch.GetChartElement x, y, IDNum, a, b

    If IDNum = xlSeries Then
    SFormula = ActiveChart.SeriesCollection(a).Formula
    FirstComma = InStr(1, SFormula, ",")
    CloseParen = InStr(1, SFormula, ")")
        If Mid(SFormula, FirstComma + 1, 1) = "(" Then
        ref = Mid(SFormula, FirstComma + 2, CloseParen - (FirstComma + 2))
        Else
        SecondComma = InStr(FirstComma + 1, SFormula, ",")
        ref = Mid(SFormula, FirstComma + 1, SecondComma - (FirstComma + 1))
        End If
        
        For Each rcell In Range(ref)
            counter = counter + 1
            If counter = b Then
                RefCell = rcell.Offset(, -2).Value
                RefName = rcell.Offset(, -1).Value
                RefData = rcell.Offset(, 2).Value
                Exit For
            End If
        Next rcell
        With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            Txt = Txt & RefCell
            Txt = Txt & " - " & RefName
            Txt = Txt & " :: " & RefData
            
            With .DataLabel
                .Text = Txt
                .Position = xlLabelPositionAbove
                .Font.Size = 8
                .Border.Weight = xlHairline
                .Border.LineStyle = xlAutomatic
                .Interior.ColorIndex = 19
            End With
        End With
    End If
End Sub

In the Class1 Module Ch_MouseUp:

Private Sub Ch_MouseUp(ByVal Button As Long, ByVal Shift As Long, ByVal x As Long, ByVal y As Long)
    Ch.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
            .HasDataLabel = False
        End With
    End If
End Sub

 

[Andrew Poulsom]

What range of data are you plotting. I get an error on this line:

RefCell = rcell.Offset(, -2).Value

 

[largeselection]

Basically it is a scatterplot, but the ranges are created by means of this code:

Dim xrng0 As Range
Dim xrng1 As Range...etc...
Dim xrng10 As Range
Dim yrng0 As Range
...etc...
Dim yrng10 As Range 
Set xrng0 = Nothing
Set xrng1 = Nothing
...etc...
Set yrng9 = Nothing
Set yrng10 = Nothing
 
Dim LastRow As Long
Dim i As Long
Dim CaseID As String
Dim ThisSheet As Worksheet
Set ThisSheet = ActiveSheet
LastRow = Cells(Rows.Count, "G").End(xlUp).Row
For i = 28 To LastRow
CaseID = Cells(i, "G").Value
Select Case CaseID
Case Is = "0"
    If xrng0 Is Nothing Then
        Set xrng0 = Cells(i, "D")
    Else
        Set xrng0 = Union(xrng0, Cells(i, "D"))
    End If
    If yrng0 Is Nothing Then
        Set yrng0 = Cells(i, "E")
    Else
        Set yrng0 = Union(yrng0, Cells(i, "E"))
    End If
    
Case Is = "1"
    If xrng1 Is Nothing Then
        Set xrng1 = Cells(i, "D")
    Else
        Set xrng1 = Union(xrng1, Cells(i, "D"))
    End If
    If yrng1 Is Nothing Then
        Set yrng1 = Cells(i, "E")
    Else
        Set yrng1 = Union(yrng1, Cells(i, "E"))
    End If
    
...Case is = 2, 3, 4, 5, 6, 7, 8, 9, 10
 
End Select
Next i
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
If xrng0 Is Nothing Then
ActiveChart.SeriesCollection(1).XValues = 0
ActiveChart.SeriesCollection(1).Values = 0
Else
ActiveChart.SeriesCollection(1).XValues = xrng0
ActiveChart.SeriesCollection(1).Values = yrng0
End If
If xrng1 Is Nothing Then
ActiveChart.SeriesCollection(2).XValues = 0
ActiveChart.SeriesCollection(2).Values = 0
Else
ActiveChart.SeriesCollection(2).XValues = xrng1
ActiveChart.SeriesCollection(2).Values = yrng1
End If
...etc... for If xrng2, xrng3, ...
 
Sheets(1).Select
ThisSheet.Select

So in the code I posted earlier when I click on a point I pass the formula through to extract the range of XValues of the related series to that point. Then count through the points in the range = range(ref) = XValues until I get to the point I selected's index. Then I use that as the address and pull some values that are to the left and right of that address as parts of the label (since it is from a data table below). The reason for the counts is because the ranges are not necessarily contiguous.