empty chart after macro

A lot of this code looks familiar. It looks like you used one of my old programs as a template .

You are doing things the hard way by replicating your code for each person. You can pass parameters to subroutines. I don’t know if this will solve your problem but it will make coding and debugging it easier.

I suggest that you modify the change event on Controlpannel to read:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B9")) Is Nothing Then
    Select Case Range("B9")
        Case "Chris": Bijwerken "CC"
        Case "Els": Bijwerken "EV"
        Case "René": Bijwerken "RH"
        Case "Yves": Bijwerken "YG"
    End Select
End If
End Sub

Then make a “Generic” Biweken subroutine

Sub Bijwerken(Initials As String)

    DataKopieren Initials
    KopieerWaarden_Grafiek Initials
    
End Sub

And likewise a generic DatKopieren

Sub DataKopieren(Initials as String)
Dim MyPath As String
Dim MyFile As String
Dim xlThis As Workbook
Dim xlData As Workbook
Dim shThis As Worksheet
Dim shData As Worksheet
Dim LRow As Long, LCol As Long

    Set xlThis = ThisWorkbook
    Set shThis = xlThis.Sheets("Data")
    MyPath = Range("Def_Dir")
    MyFile = Range("Data_File")
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' Clear the old data
    ClearTable "Data", "Table_Data"
    
    ' Open data workbook
    Set xlData = Workbooks.Open(MyPath & "\" & MyFile)
    
    ' Set the sheet and get the last row and column
    Set shData = xlData.Sheets(1)
    LRow = shData.Range("A" & Rows.Count).End(xlUp).row
    LCol = shData.Cells("1", Columns.Count).End(xlToLeft).Column
    
    ' Set the filter
    shData.Range(shData.Cells(1, 1), shData.Cells(LRow, LCol)).AutoFilter Field:=3, Criteria1:= Initials
    
    shData.Range(shData.Cells(1, 1), shData.Cells(LRow, LCol)).SpecialCells(xlCellTypeVisible).Copy
        shThis.Range("A1").PasteSpecial xlPasteValues
        
    ' Close the workbook
    Workbooks(MyFile).Close savechanges:=False
    
    Sheets("Controlepaneel").Select
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    Range("A1").Select
    
    SubtotaalMaken

End Sub

Next a generic KopieerWaarden_Grafiek

Sub KopieerWaarden_Grafiek(Initials As String)
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    Worksheets("Grafiek").Activate
    Cells.Select
    Selection.Delete Shift:=xlUp
    Range("A1").Select
    
    ThisWorkbook.Worksheets("Subtotaal").Cells.Copy
    ThisWorkbook.Worksheets("Grafiek").Select
    Selection.PasteSpecial Paste:=xlPasteValues, _
        Operation:=xlNone, SkipBlanks:=True, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats
    
    ActiveSheet.Outline.ShowLevels rowlevels:=1
    
    Laatste3KolommenVerwijderen
    
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    
    Range("A1").Select
    
    GrafiekMaken Initials
    
    MsgBox "Klaar!" & vbCr & vbCr & "De gegevens werden verwerkt.", vbInformation, "Verkoopsresultaten"
    
    
End Sub

And finally a generic GrafiekMaken – note that I deleted the title. I’ll address that later. In this case, you don’t need to pass the parameter since the only thing you need it for is to set the title and we will do that elsewhere.

Sub GrafiekMaken()

    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    With Range("A1")
        .Parent.ListObjects.Add(xlSrcRange, Range(.End(xlDown), .End(xlToRight)), , xlYes).Name = "Tabel1"
    End With

    Range("a1").Select
    Selection.CurrentRegion.Select
    MyRange = Selection.Address
    mysheetname = ActiveSheet.Name
    ActiveSheet.ChartObjects.Add(100, 60, 500, 250).Select
    Application.CutCopyMode = False
    ActiveChart.ChartWizard _
       Source:=Sheets(mysheetname).Range(MyRange), _
       Gallery:=xlColumnClustered, Format:=10, PlotBy:=xlRows, _
       CategoryLabels:=1, SeriesLabels:=1, HasLegend:=0, _
       CategoryTitle:="", _
       ValueTitle:="", ExtraTitle:=""
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    Range("a1").Select
    
End Sub

To set the title, go to the Controlepanneel and set a cell (for example B11) ="Omzet 2016 - " & B9. Then go to the chart and click in the title. Then go to the formula bar, type = and then select this cell.

There is probably a better way of doing this using a “template” chart and named dynamic ranges rather than building the chart with VBA.

If you decide to try that, here are a couple of articles that will help.
http://www.utteraccess.com/wiki/inde...Dynamic_Ranges
http://www.utteraccess.com/wiki/inde...namic_Charting

I decided to take your suggestion and use MS-Query. The issue is to keep it dynamic. My apologies for introducing English (or more appropriately American) into your spreadsheet. I don’t speak Dutch … yet, but I am learning by working with you and your fellow countrymen .

The SQL has to change every month to get the new months. So I address this issue on the Sales SQL page.

I have a little table in Columns D:F where, based on the date entered in the Controlepanneel, I make a string to get the months that I expect to find in the data.

Cell I2 contains the fragment of SQL code that will change every month.

Columns A & B on the Sales SQL page are my “standard” way of dealing with dynamic SQL Code. I use whatever formulas I need to create the SQL code. Normally I am substituting parameters, but in this case, I am adding a dynamic list. Cell B1 concatenates the translated code into a SQL String.

The module SubSQL goes along with this. What this module does is it replaces the Command string for the query with what is in Cell B1 (which I named SQL_Sales_Data).

You can modify the FROM clause to reflect the directory where your data spreadsheet exists. In fact you can probably use a formula referencing Cell B6 on the Controlepaneel.

I also tacked on a subroutine called MakeFunction.

So when you click on the Refresh Data button. It creates the SQL, substitutes it into the connection, refreshes the data and then runs the MakeFunction subroutine.

The MakeFunction subroutine reads the months returned by the data query and creates the headers and the formulas on the Sales Chart page.

On the Sales Chart page, I made dynamic named ranges for Sales_Month (on row 1) and Sales_Data (on row 2). I “attached” these to the chart. The title for the chart comes from the Controlepannel Cell B11.

Using the dynamic names eliminated the need to use VBA to manage the charts. See the two references I provided in the previous post to see how to make named dynamic ranges and how to use them in charts.

One other tweak I added was a lookup table on the parameters tab. This table looks up the initials and the person’s full name. This keeps you out of the code when personnel change; all you have to do is change the table. I also made the data validation on the Controlepaneel work off this table.

I think you should be able to throw out a lot of your code.

If you have any questions, just ask.

One thing I did notice as I was playing with the spreadsheet. When I changed a name in Cell B9 on the Controlepaneel, it fired off your code. I got an error message because the code was looking for data in a directory I don’t have. I wasn’t testing that part of the code so I exited the debugger.

Except for Rene. When I changed to Rene from another name, the trigger probably worked, but I did not get an error message. Something is not being called. So, there is some sort of disconnect and that may be the root of your problem.

Oh yeah. The connection string. I forgot about that. Let me work on that one. It's a relatively simple fix but I'll want to test it.

I think I have it now. It reads both Def_dir and Data_File and sets thing up in both the connection string (via vba) and the command string (via a formula on the SQL page). To reset the command string, first you break it into pieces using the split command and then you change the pieces you want, and then you put it back together.

Well, there are 4 months, I suspect that they are the culprits. I could not get that error. Please attach both the program and the source data again. I want to see exactly what you've filled in and what is contained in the data. I think I will have to do an ASCII analysis on the strings. The issue is that the dates have a slash in them and have to be handled with a back single quote.

I opened up the file you just sent and checked the connection string. It is still pointing to my temp directory. You can check the strings by going to Data -> Connections, select the string name and click on properties. Click on the definition tag and the two strings are shown there. You can copy and paste them to notepad or Word for further analysis.

I tried refreshing data with the strings you provided and it failed as expected. I got a message saying that the path was not valid.

If you were to try it with my temp directory path still in there, you should be getting a message saying that the path is invalid. You should not be seeing the "too few parameters" error.

I switched the string back to my temp directory and it ran ok. I cannot duplicate the error.

It is possible that when the subroutine fails, it does not switch the strings. This might explain why my strings were still in the connection string and command sting as shown on the Data ribbon. Even if that is the case, you are getting the wrong error message for the conditions.

What could be happening, but I can't make it happen on my end, is that the Sales SQL isn't doing the translations properly because they are not recalculating. The part of the code that changes the connection string uses the named ranges. However the part of the code that changes the command string uses the Sales SQL page.

So with your latest version, please take a look at the Sales SQL page. First of all, cell B1 should have the connection string in it (don't worry that you can't see all of it). Then look at cell A7 - this should have the correct months. Then look at cell A8 - this should have the correct path and file name to where the database is.

If Cell B1 has the #Value error in it, select it and click in the formula bar and press ENTER. This should clear the error. If either of the other two cells is incorrect, press F9.

Let me know if any of this works. It should be all automatic, but I might be able to put some checks in the code nonetheless.

When you went back to three months, did you change the date on the control panel? The date controls the months that go into the query. This is managed by the table in columns D:F on the Sales SQL and cells I1 & I2.

I'm fairly certain the connection string is working. It's the command string that would produce that error. Let's try one more thing: after you get the error can you go to Data -> Connections and get the connection string and command string and post them. I probably isn't the command string, but you might as well throw it in while you are there.

Also copy and paste the translated SQL.

I see the problem: b.`jan/yy`, b.`feb/yy`, b.`mrt/yy`, b.`apr/yy` should be b.`jan/16`, b.`feb/16`, b.`mrt/16`, b.`apr/16`

On the Sales SQL Page. Check columns D:F and cells I1 & I2 to see why this isn't happening.

The formula in cell F2 should read =IF(MONTH(MyDate)>'Sales SQL'!D2,"b." & CHAR(96) & E2 & "/" & TEXT(MyDate,"yy") & CHAR(96) & ",","")

Check to see what MyDate is defined as. It should point to Cell B1 on the Control Panel.

Ah, got to love the language barrier! I've learned something! It's been a pleasure. I've enjoyed working with you and good luck with the rest of the project.