Problem with Chart MouseUp Event

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
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!
 

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
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:
Code:
Dim MyChart As New Class1
Private Sub Worksheet_Activate()
    Set MyChart.Ch = ActiveSheet.ChartObjects(1).Chart
End Sub
In the Class1 Module Ch_MouseDown:
Code:
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:
Code:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
What range of data are you plotting. I get an error on this line:

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

largeselection

Active Member
Joined
Aug 4, 2008
Messages
353
Basically it is a scatterplot, but the ranges are created by means of this code:

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.
 

Forum statistics

Threads
1,082,099
Messages
5,363,129
Members
400,720
Latest member
Pettel

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top