Public DisbleEventsFailed As String
Sub Create_Charts()

'Disabled the Events
Application.EnableEvents = False

On Error Resume Next

'Defined the variable for Data worksheet
Dim DSh As Worksheet
Set DSh = ActiveWorkbook.Sheets("Data")

'Delete the existing Chart Object
ActiveSheet.ChartObjects("Sales").Delete

'Wait for One second
Application.Wait (Now + TimeValue("00:00:01"))

'Create The Names for the Data
DSh.Range("A2:A7").Name = "Fruits"
DSh.Range("B2:B7").Name = "One"
DSh.Range("C2:C7").Name = "Two"
DSh.Range("D2:D7").Name = "Three"
DSh.Range("E2:E7").Name = "Four"
DSh.Range("F2:F7").Name = "Five"
DSh.Range("G2:G7").Name = "Six"
DSh.Range("B1:G1").Name = "Years"

'Application jumping to the Listbox events when we deleted the values from the listbox
'when program hits Listbox1.clear
DisbleEventsFailed = "Yes"


'Format the Listbox
With Sheet1.ListBox1
.Clear
For C = 2 To 7
.AddItem Sheet3.Cells(1, C).Value
Next

.ForeColor = RGB(255, 255, 255)
.BackColor = RGB(150, 0, 0)
.Font.Name = "Estrangelo Edessa"
.Font.Size = 15
.Font.Bold = True
End With

'Create chart Object
Dim ch As ChartObject
With Range("H2:P18")
Set ch = Sheet1.ChartObjects.Add( _
Height:=.Height, _
Width:=.Width, _
Top:=.Top, _
Left:=.Left)

'Create Chart
With ch.Chart
ch.Name = "Sales"
ch.Chart.ChartType = xlColumnClustered
.SeriesCollection.NewSeries

Dim S As Series
Set S = ch.Chart.SeriesCollection(1)
S.XValues = Range("Fruits")
S.Values = Range("One")
Dim L As Legend
Set L = ch.Chart.Legend
'L.Position = xlLegendPositionTop
L.Delete
S.Name = "Sales Data"
S.Interior.ColorIndex = 9
ch.Chart.SetElement (msoElementPrimaryValueGridLinesNone)
ch.Chart.SetElement ((msoElementPrimaryCategoryGridLinesNone))
S.HasDataLabels = True

'Find the Max value from the values of Chart
MaxValue = Application.WorksheetFunction.Large(Range("one"), 1)

'Loop through all Points and assign Yellow color to the max value point
For V = 1 To S.Points.Count
If S.Values(V) = MaxValue Then
S.Points(V).Interior.ColorIndex = 6
Exit For
End If
Next
End With

End With

DisbleEventsFailed = ""
Application.EnableEvents = True
End Sub

Private Sub ListBox1_Change()

'If the Program the event while running the Macro in Module
If DisbleEventsFailed = "Yes" Then
GoTo ExitProgram
End If

'Find the Year Number based on Listbox selection
YearNumber = Sheet1.ListBox1.Text

'Find the Range Name based on Year Number
If YearNumber = Val(2015) Then
RangeName = "One"
ElseIf YearNumber = Val(2016) Then
RangeName = "Two"
ElseIf YearNumber = Val(2017) Then
RangeName = "Three"
ElseIf YearNumber = Val(2018) Then
RangeName = "Four"
ElseIf YearNumber = Val(2019) Then
RangeName = "Five"
ElseIf YearNumber = Val(2020) Then
RangeName = "Six"
End If
'Sheets("Data").Select

'Find the Max value
MaxValue = Application.WorksheetFunction.Large(Sheets("Data").Range(RangeName), 1)

'Define the series and change the color of the point(s)
With Sheet1.ChartObjects("Sales")
Dim S As Series
Set S = .Chart.SeriesCollection(1)
S.Values = Sheets("Data").Range(RangeName)
S.Interior.ColorIndex = 9
For V = 1 To S.Points.Count
If S.Values(V) = MaxValue Then
S.Points(V).Interior.ColorIndex = 6
Exit For
End If
Next
End With

S.HasDataLabels = True
Sheets("Buttons").Activate

ExitProgram:
DisbleEventsFailed = ""
End Sub