How would I make textboxes / autoshapes unclickable?

[phil152003]

I have a scatter chart (object in a worksheet). I have split the graph area into quadrants using autoshape lines. In each quadrant of the chart, there is a textbox describing what points in that quadrant represent.

Now, I have implemented some code (that someone else from this forum has helped me with) that allows me to click on a data point in the chart and a popup label appears for the duration of the mousepress. However, if the datapoint coincides with one of the autoshape lines or a textbox, then when I go to click the datapoint, it selects the line/textbox instead of the point.

I want to know if there is a way using VBA to "lock" these textboxes/lines into the graph, making them unclickable to allow no interation with them.

I'm not particularly experienced with VBA, so if you could tell me exactly what code I need, and where exactly I have to put it then that would be greatly appriciated.

Thanks!

 

[RoryA]

I'd have to guess that's a function of whatever code you are running, since it doesn't do that for me.
I'd also have to say that it sounds to me like your chart is far too complicated, though of course there may be good reason for it.

 

[phil152003]

Its actually a really useful chart (when it works), and makes a lot of sense. Its a design that my boss has come up with, and I'm ALMOST there implementing it. The reason I wasnt it perfect is because it will be sent to a lot of important people.

If I posted the full code, would there be a chance you could identify what is causing the flickering (baring in mind it didn't flicker before I uploaded a picture as background, and had a standard Excel fill)?

Also, I've seen the solution to similar problems on different forum threads using

Application.ScreenUpdating = False
Application.ScreenUpdating = True

But I'm not entuirely sure how I'd implement them into my code. I'll post it anyway, and if you can help then that's brilliant!

Class Module:

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
    Txt = ""
    Ch.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            Txt = "Series " & .Parent.Name
            Txt = Txt & " - " & Worksheets("Chart Calculation - RV & Hide").Range("AC8:AL676").Cells(b, 3 * a - 2).Value
            With .DataLabel
                .Text = Txt
                .Position = xlLabelPositionAbove
                .Font.Size = 20
                .Border.Weight = xlHairline
                .Border.LineStyle = xlAutomatic
                .Interior.ColorIndex = 19
            End With
        End With
    End If
End Sub
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

In the sheet with the chart:

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

 

[RoryA]

I can't say if it will help (there isn't really a noticeable flicker in my small test) but try altering the class code to:

Private Declare Function LockWindowUpdate Lib "user32" (ByVal hWnd As Long) As Long
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
    Debug.Print "mosuedown"
   LockWindowUpdate Application.hWnd
    Txt = ""
    Ch.GetChartElement x, y, IDNum, a, b
    If IDNum = xlSeries Then
        With ActiveChart.SeriesCollection(a).Points(b)
             .HasDataLabel = True
            Txt = "Series " & .Parent.Name
            Txt = Txt & " - " & Worksheets("Chart Calculation - RV & Hide").Range("AC8:AL676").Cells(b, 3 * a - 2).Value
            With .DataLabel
                .Position = xlLabelPositionAbove
                .Font.Size = 20
                .Border.Weight = xlHairline
                .Border.LineStyle = xlAutomatic
                .Interior.ColorIndex = 19
                .Text = Txt
            End With
        End With
    End If
LockWindowUpdate 0
End Sub
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

 

[phil152003]

That's much better! There is still a flicker, but only one or two and doesn't seem to be particularly distracting. Obviously I would prefer no flicker at all (like when the background is a standard Excel fill) but I think this will do. I just need to check now that the flick is still minimal on other computers other than mine!

Thanks very much for your help.