How to update chart when data is changing?

I have two columns were is information that I use in chart and it is changing according to what time range I choose. The main problem is that the columns length can be different, it can be 5 rows and when I choose another period of time it can be 7 rows or less then 5. The data in chart is refreshing, but the number of columns not, e.g. I have data like this:

 Tom 20
 Susan 30
 John 15

So the chart would have three columns and their color is different according to the value in the first column, but when I choose another period of time data changes to:

 Peter 40
 Patrick 70
 Joe 36
 Megan 57
 Susan 74

Now the chart will contain three columns with only the first three names: Peter, Patrick, Joe and the color of columns will be the same as in the first chart.

I hope it is easy to understand my problem, the main thing is that the visualization of the chart doesn't change as it's supposed to. I add a part of my code:

Sub Macro1()

    Dim MyRangex As Range
    Dim LastRow As Long
    Dim ChartRange1 As Range

    LastRow = Worksheets("Calculate").Cells(Rows.Count, "E").End(xlUp).row

    Set MyRangex = Worksheets("Calculate").Range("E2:E" & LastRow)
    Set ChartRange1 = Sheets("Calculate").Range("G2:G" & LastRow)

    ActiveChart.SeriesCollection(1).Select
    ActiveChart.SeriesCollection(1).XValues = MyRangex
    ActiveChart.SeriesCollection(1).Values = ChartRange1

    For i = 1 To Worksheets("Calculate").Cells(9, 10).value
        ActiveChart.SeriesCollection(1).Points(i).Select
        Select Case Worksheets("Calculate").Cells(i + 1, 5).value
            Case Is = "Tom"
                With Selection.Format.Fill
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(255, 0, 0)
                    .Transparency = 0
                    .Solid
                End With
            Case Is = "Susan"
                With Selection.Format.Fill
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(0, 176, 240)
                    .Transparency = 0
                    .Solid
                End With
            Case Is = "Joe"
                With Selection.Format.Fill
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(255, 255, 0)
                    .Transparency = 0
                    .Solid
                End With
            Case Is = "John"
                With Selection.Format.Fill
                    .Visible = msoTrue
                    .ForeColor.RGB = RGB(191, 191, 191)
                    .Transparency = 0
                    .Solid
                End With
        End Select
    Next i
End Sub

Answers


You should employ a Worksheet_Change event. It must reside in the worksheet module.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Me.Range("C2"), Target) Is Nothing Then
        MsgBox "data in C2 was changed"
    End If
End Sub

The MsgBox in this example appears only when data in the cell C2 is changed. Depending on how you change your new period, this part should be changed.


No matter if you update your period manually or automatically, I am sure the code below will work for you. (Unless there are formulas under these names.)

Requirements for usage: 1. Place this code inside your worksheet module (not a regular module which appears after you record a macro). 2. Rename your chart to "MyChartName" OR replace this name in the code with your actual chart name.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim MyRangex As Range
    Dim LastRow As Long
    Dim ChartRange1 As Range
    Dim i As Long
    Dim mySeries As Series

    If Not Intersect(Me.Range("E2:E100"), Target) Is Nothing Then

        LastRow = Worksheets("Calculate").Cells(Rows.Count, "E").End(xlUp).Row

        Set MyRangex = Worksheets("Calculate").Range("E2:E" & LastRow)
        Set ChartRange1 = Sheets("Calculate").Range("G2:G" & LastRow)
        Set mySeries = ActiveSheet.ChartObjects("MyChartName").Chart.SeriesCollection(1)

        mySeries.XValues = MyRangex
        mySeries.Values = ChartRange1

        For i = 1 To Worksheets("Calculate").Cells(9, 10).Value
            With mySeries.Points(i).Format.Fill
                Select Case Worksheets("Calculate").Cells(i + 1, 5).Value
                    Case Is = "Tom"
                        .Visible = msoTrue
                        .ForeColor.RGB = RGB(255, 0, 0)
                        .Transparency = 0
                        .Solid
                    Case Is = "Susan"
                        .Visible = msoTrue
                        .ForeColor.RGB = RGB(0, 176, 240)
                        .Transparency = 0
                        .Solid
                    Case Is = "Joe"
                        .Visible = msoTrue
                        .ForeColor.RGB = RGB(255, 255, 0)
                        .Transparency = 0
                        .Solid
                    Case Is = "John"
                        .Visible = msoTrue
                        .ForeColor.RGB = RGB(191, 191, 191)
                        .Transparency = 0
                        .Solid
                End Select
            End With
        Next
    End If
End Sub

Need Your Help

Regex PHP: match only numbers except the number that follows a specific character

php regex

After many temptatives I need some help. I have the following typical string: