Change Chart Series using Excel VBA

Hi all.

I have the attached Excel sheet, when the end user clicks the button it populates columns A through D. It all depends on what parameters the end user enters in cells: B1, D1 and B2, D2.

Now, I have a chart in the "Slab-Dried-in Chart" worksheet. Currently I have to manually change the series for the everything every time I click the button. Is there a way to automatically tell the chart to update the series everytime the button is clicked?

In the attachment there are only 3 rows of data, but it can vary, there could be 50 rows or 10 rows etc. I don't want to have to select the new data everytime to update the chart. Can I do this using VBA?

Thank you in advance!
MySQLAPP-11.xlsx
Sim1980Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Hi,

Is there a way to automatically tell the chart to update the series everytime the button is clicked?

Yes.

However, the workbook you have attached does not include the Visual Basic for Applications [VBA] code for the click event for the button; "'MySQLAPP-11.xlsx'!Button1_Click".

At the end of that routine you could add VBA code similar to this:

  
  Worksheets("Slab-Dried-in Chart").Activate

  ActiveSheet.ChartObjects("Chart 2").Select
  
  ActiveChart.SeriesCollection(1).Select
  
  ActiveChart.SeriesCollection(1).Points(2).Select
  ActiveChart.SeriesCollection(1).Values = "='Summary Sheet'!" & Range(Worksheets("Summary Sheet").[B9], Worksheets("Summary Sheet").Cells(Cells.Rows.Count, "B").End(xlUp)).Address
  
  ActiveChart.Axes(xlCategory).Select
  ActiveChart.SeriesCollection(1).XValues = "='Summary Sheet'!" & Range(Worksheets("Summary Sheet").[A9], Worksheets("Summary Sheet").Cells(Cells.Rows.Count, "A").End(xlUp)).Address
  ActiveChart.SeriesCollection(2).Values = "='Summary Sheet'!" & Range(Worksheets("Summary Sheet").[C9], Worksheets("Summary Sheet").Cells(Cells.Rows.Count, "C").End(xlUp)).Address
  ActiveChart.SeriesCollection(3).Values = "='Summary Sheet'!" & Range(Worksheets("Summary Sheet").[D9], Worksheets("Summary Sheet").Cells(Cells.Rows.Count, "D").End(xlUp)).Address
 

Open in new window


I have attached a workbook containing this code with three additional rows in the [Summary Sheet] worksheet.

Please download/open this attachment, review the [Slab-Dried-in Chart] worksheet, then return to the [Summary Sheet] worksheet & click the button.

BFN,

fp.
Q-28266437.xlsm
0
Sim1980Author Commented:
Thank you for the quick reply.

In my initial post I didn't attach the macro-enabled workbook. I have attached now, with your suggested code (I removed my database username, password, database and server IP for security). And I get the following error when I press the button:

Run-time error '1004': Invalid Parameter

It highlights this line of code:
  ActiveChart.SeriesCollection(1).Points(2).Select

Open in new window


The data in columns A through D are initially blank and do not get populated until the end user enters the parameters.
Testing-MySQLAPP-WEB.xlsm
0
[ fanpages ]IT Services ConsultantCommented:
Was the code I posted above successful in execution in your environment (i.e. a run-time error was not shown), before you moved the code to the other workbook?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Sim1980Author Commented:
No it never worked. When I pasted your code to my workbook and then clicked the button, I got the error. I don't know if this makes a difference but as I said before, the fields are initially blank (i.e. B1, D1, B2, D2 and starting with A9, B9, C9 and D9).
0
[ fanpages ]IT Services ConsultantCommented:
Sorry, you are missing my point.

When I pasted your code to my workbook...

I was asking if the code worked in the attachment I posted above, not following the transfer of the code from that workbook to your own.

...

I have taken your newly-provided workbook, & changed the code to read as follows:

Option Explicit
Public Sub Button1_Click()

'==========================================================================================
'==========================================================================================
'==========================================================================================
'Dim StartDate As Date
'Dim EndDate As Date
'Dim NumMonths As Long

 '   With ActiveSheet
    
  '      StartDate = DateSerial(.Range("D1").Value, .Range("B1").Value, 1)
   '     EndDate = DateSerial(.Range("D2").Value, .Range("B2").Value, 1)
    '    NumMonths = DateDiff("M", StartDate, EndDate)
        
     '   With .Range("M1").Resize(NumMonths + 1)
        
      '      .Formula = "=DATE($D$1,$B$1+ROW()-1,1)"
       '     .NumberFormat = "m"
        '    .Offset(0, 1).Formula = "=DATE($D$1,$B$1+ROW()-1,1)"
         '   .Offset(0, 1).NumberFormat = "yyyy"
        'End With
    'End With
Dim dtLoopDate As Date
Dim dtEndDate As Date
Dim lngWriteRow As Long

dtLoopDate = DateSerial(Range("D1"), Range("B1"), 1)
dtEndDate = DateSerial(Range("D2"), Range("B2") + 1, 1)
lngWriteRow = 5

Do Until dtLoopDate = dtEndDate
    Range("R" & lngWriteRow) = Month(dtLoopDate)
    Range("S" & lngWriteRow) = Year(dtLoopDate)
    dtLoopDate = DateSerial(Year(dtLoopDate), Month(dtLoopDate) + 1, 1)
    lngWriteRow = lngWriteRow + 1
Loop

'==========================================================================================
'==========================================================================================
'Populate the slab count starting in cell B9
'==========================================================================================
'==========================================================================================

Dim cnPubs1 As ADODB.Connection
Set cnPubs1 = New ADODB.Connection

Dim strConn1 As String

strConn1 = "DRIVER={MySQL ODBC 5.1 Driver};SERVER=###;DATABASE=###;USER=###;PASSWORD=###;Option=3"

cnPubs1.Open strConn1

Dim rsPubs1 As ADODB.Recordset
Set rsPubs1 = New ADODB.Recordset

With rsPubs1

    .ActiveConnection = cnPubs1
    Dim I3
    Dim rng1 As Range
    Set rng1 = Range("R5")
    Dim rng1a As Range
    Set rng1a = Range("S5")
    Dim strSQL1 As String
        
    Dim rng2 As Range
    Set rng2 = Range("A9")
   
    While rng1.Value <> "" And rng1a.Value <> ""
    strSQL1 = "SELECT COUNT(*) From FieldSuperDataEntry WHERE Type = 1 AND MONTH(CreateDate) = '" & rng1.Value & "' AND YEAR(CreateDate) = '" & rng1a.Value & "'"
    .Open strSQL1
    
        If Not rsPubs1.EOF Then
            rsPubs1.MoveFirst
            For I3 = 0 To rsPubs1.Fields.Count - 1
          
                rng2.Offset(, I3 + 1).Value = rsPubs1.Fields(I3).Value
                                      
            Next I3
        
        End If

        rsPubs1.Close
        Set rng1 = rng1.Offset(1)
        Set rng1a = rng1a.Offset(1)
        Set rng2 = rng2.Offset(1)
    Wend
      
End With

cnPubs1.Close

Set rsPubs1 = Nothing
Set cnPubs1 = Nothing

'==================================================================================================
'==================================================================================================
'Populate the dried in count starting in cell C9===================================================
'==================================================================================================
'==================================================================================================

Dim cnPubs2 As ADODB.Connection
Set cnPubs2 = New ADODB.Connection

cnPubs2.Open strConn1

Dim rsPubs2 As ADODB.Recordset
Set rsPubs2 = New ADODB.Recordset

With rsPubs2

    .ActiveConnection = cnPubs2
    Dim I4
    Dim rng3 As Range
    Set rng3 = Range("R5")
    Dim rng3a As Range
    Set rng3a = Range("S5")
    Dim strSQL2 As String
    
    
    Dim rng4 As Range
    Set rng4 = Range("A9")

    While rng3.Value <> "" And rng3a.Value <> ""
    strSQL2 = "SELECT COUNT(*) From FieldSuperDataEntry WHERE Type = 2 AND MONTH(CreateDate) = '" & rng3.Value & "' AND YEAR(CreateDate) = '" & rng3a.Value & "'"
    .Open strSQL2
    
        If Not rsPubs2.EOF Then
            rsPubs2.MoveFirst
            For I4 = 0 To rsPubs2.Fields.Count - 1
          
                rng4.Offset(, I4 + 2).Value = rsPubs2.Fields(I4).Value
                                      
            Next I4
        
        End If
        

        rsPubs2.Close
        Set rng3 = rng3.Offset(1)
        Set rng3a = rng3a.Offset(1)
        Set rng4 = rng4.Offset(1)
    Wend
      
End With

cnPubs2.Close

Set rsPubs2 = Nothing
Set cnPubs2 = Nothing

'========================================================================================================================
'Change the month and year to month/1/year to be able to use the TEXT(T5,"mmm") to convert the month number to month name
'========================================================================================================================

Dim rng5 As Long

With Worksheets("Summary Sheet")

    rng5 = .Range("R" & Rows.Count).End(xlUp).Row
 
     .Range("T5:T" & rng5).Formula = "=CONCATENATE(R5,""/1/"",S5)"
   
End With

'=======================================================================================
'Use the TEXT(T5,"mmm") to convert the month number to month name and place in column A
'=======================================================================================

Dim rng6 As Long

With Worksheets("Summary Sheet")

    rng6 = .Range("B" & Rows.Count).End(xlUp).Row

   .Range("A9:A" & rng6).Formula = "=CONCATENATE(TEXT(T5,""mmmm""), "" "",S5)"

End With

'==================================================================================================
'Add borders
'==================================================================================================

With Range("A8", Range("A" & Rows.Count).End(xlUp)).Resize(, 4).Borders
    
    .LineStyle = xlContinuous

End With

'==================================================================================================
'Auto fit the columns
'==================================================================================================

Dim rng7 As Long
With Worksheets("Summary Sheet")
    rng7 = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("A9:A" & rng7).Columns.AutoFit
End With

'==================================================================================================
'Add columns B and C to calcuate the combined slab and dried in count
'==================================================================================================

Dim rng8 As Long

With Worksheets("Summary Sheet")

    rng8 = .Range("A" & Rows.Count).End(xlUp).Row
    .Range("B9:B" & rng8).NumberFormat = "0"
    .Range("C9:C" & rng8).NumberFormat = "0"
   .Range("D9:D" & rng8).Formula = "=B9+C9"
   .Range("D9:D" & rng8).NumberFormat = "0"

End With

'==================================================================================================
'==================================================================================================
'==================================================================================================

  Worksheets("Slab-Dried-in Chart").Activate

  If ActiveSheet.ChartObjects.Count > 0& Then
     ActiveSheet.ChartObjects(1&).Select
  
     If ActiveChart.SeriesCollection.Count > 0& Then
        ActiveChart.SeriesCollection(1&).Select
  
        ActiveChart.SeriesCollection(1&).Points(2&).Select
        ActiveChart.SeriesCollection(1&).Values = "='Summary Sheet'!" & Range(Worksheets("Summary Sheet").[B9], Worksheets("Summary Sheet").Cells(Cells.Rows.Count, "B").End(xlUp)).Address
  
        ActiveChart.Axes(xlCategory).Select
        ActiveChart.SeriesCollection(1&).XValues = "='Summary Sheet'!" & Range(Worksheets("Summary Sheet").[A9], Worksheets("Summary Sheet").Cells(Cells.Rows.Count, "A").End(xlUp)).Address
        ActiveChart.SeriesCollection(2&).Values = "='Summary Sheet'!" & Range(Worksheets("Summary Sheet").[C9], Worksheets("Summary Sheet").Cells(Cells.Rows.Count, "C").End(xlUp)).Address
        ActiveChart.SeriesCollection(3&).Values = "='Summary Sheet'!" & Range(Worksheets("Summary Sheet").[D9], Worksheets("Summary Sheet").Cells(Cells.Rows.Count, "D").End(xlUp)).Address
     End If ' If ActiveChart.SeriesCollection.Count > 0& Then
  End If ' If ActiveSheet.ChartObjects.Count > 0& Then
  
End Sub

Open in new window


A revised workbook is attached for convenience.
Q-28266437b.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Sim1980Author Commented:
Sorry about that, yes, the file you initially uploaded did work. I pasted your updated code in my workbook and now it works.

Thanks!
0
[ fanpages ]IT Services ConsultantCommented:
You're very welcome.

Good luck with the rest of your project.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.