PDA

View Full Version : [SOLVED] Worksheet_BeforeDoubleClick



fbofill
02-10-2020, 11:54 PM
Hi all!
This is my first post in this forum. First of all thank you for solving me loads of codes issues.
I've been looking for a solution to event BeforeDoubleClick

The issue is have done a macro that creates a chart when a certain cell is double clicked and depending on the value of the cells above (YES/NO)the macro shows the serie of data. I want to make a boolean variable that detects when the chart is active and if it's active delete it and make a new one.
This is whats i ve been trying to do to solve the problem but haven't worked.

The code

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim m_Grafico1_Active As Boolean
With Sheets("AUX")
lrow = .Range("A" & .Rows.Count).End(xlUp).Row

End With

If Not Intersect(Target, Range("C13:C16,C20:C26,C30:C31")) Is Nothing Then
If Target = "SI" Then
Target = "NO"
Target.Interior.Color = RGB(231, 231, 231)
Target.Font.Color = RGB(0, 0, 0)
Else
Target = "SI"
Target.Interior.Color = RGB(254, 80, 0)
End If
If m_Grafico1_Active = True Then
Worksheets("CONFIGURACIÓ").ChartObjects("Grafico_1").Delete
m_Grafico1_Active = False
End If
im grafico As ChartObject
Dim wks As Worksheet
Dim wksdata As Worksheet

Set wks = ActiveWorkbook.Sheets(1)
Set grafico = wks.ChartObjects.Add(Left:=400, Width:=1000, Top:=100, Height:=500)
grafico.Name = "Grafico_1"
grafico.Chart.ChartType = xlXYScatterLines
grafico.Chart.SetSourceData Source:=Worksheets("AUX").Range("A14:M" & lrow)
'Borrar series iniciales
Dim cht As Chart
Set cht = ActiveWorkbook.Sheets(1).ChartObjects("Grafico_1").Chart
Dim series As series
For Each series In cht.SeriesCollection
series.Delete
Next series
cht.HasTitle = True
cht.ChartTitle.Text = "Capçal"
With cht.ChartTitle.Font
.Size = 16
.Bold = True
.Color = RGB(0, 0, 0)
End With
'Series

If Range("C14") = "SI" Then
grafico.Chart.SeriesCollection.NewSeries.Select
With Selection
.Name = "M capçal"
.XValues = Worksheets("AUX").Range("X14:X" & lrow)
.Values = Worksheets("AUX").Range("M14:M" & lrow)
End With
End If
If Range("C15") = "SI" Then
grafico.Chart.SeriesCollection.NewSeries.Select
'Series
With Selection
.Name = "M en eix"
.XValues = Worksheets("AUX").Range("X14:X" & lrow)
.Values = Worksheets("AUX").Range("N14:N" & lrow)
End With
End If
If Range("C16") = "SI" Then
grafico.Chart.SeriesCollection.NewSeries.Select
With Selection
.Name = "RPM capçal"
.XValues = Worksheets("AUX").Range("X14:X" & lrow)
.Values = Worksheets("AUX").Range("T14:T" & lrow)
.AxisGroup = 2
End With
End If
m_Grafico1_Active = True
Range("C13") = "NO"
Range("C13").Interior.Color = RGB(231, 231, 231)
End If
Cancel = True
End Sub

fbofill
02-11-2020, 01:59 AM
The problem was that the BOOL variable shouldn't be Dim and must be Static instead.
[SOLVED]