Dynamisch bereik bij grafiek

Status
Niet open voor verdere reacties.
D

Duijzer

Gebruiker
Lid geworden
2 mrt 2009
Berichten
81
Ik zit met een probleem,
In mijn excel bestand heb ik een grafiek waarin bepaalde waarde worden weergegeven.
Eén van mijn macro's maakt een nieuwe kolom aan en vult deze met nieuwe waarden.
Deze nieuwe kolom moet tijdens deze macro direct extra worden toegevoegd aan de grafiek.

Als voorbeeld zal ik een bestandje toevoegen waarin ik al het e.e.a. heb geprobeerd. De basis van het probleem moet zo duidelijk worden.
Wanneer ik mijn macro één keer uitvoer gaat het goed. De kolom wordt aangemaakt, en ingevuld (dat lukt sowieso wel).
Daarna kijkt de grafiek in Q2 voor de titel van de toe te voegen reeks, en vervolgens kijkt de grafiek naar Q3:Q7 voor de toe te voegen waarden.

Nu komt mijn probleem ik wil namelijk dat deze macro de nieuwe reeks blijft toevoegen aan de grafiek, echter worden de nieuwe reeksen niet juist ingevuld.

Wie kan mij helpen zodat de grafiek wel automatisch de juiste reeks aanmaakt (met juist waarden).

Alvast bedankt!

Voorbeeld bestand:
Bekijk bijlage Voorbeeld1.xlsm

Code: 
Sub Macro2()
'
' Macro2 Macro
'

'
    Columns("Q:Q").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    
    Range("Q2").FormulaR1C1 = "Waarde 3"
    Range("Q3").FormulaR1C1 = "50"
    Range("Q4").FormulaR1C1 = "50"
    Range("Q5").FormulaR1C1 = "60"
    Range("Q6").FormulaR1C1 = "40"
    Range("Q7").FormulaR1C1 = "30"
    
    ActiveSheet.ChartObjects("Grafiek 1").Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(3).Name = "=Blad1!$Q$2"
    ActiveChart.SeriesCollection(3).Values = "=Blad1!$Q$3:$Q$7"
End Sub
 
Code: 
Sub Macro2()

    aantal = Range("q2").End(xlToRight).Column - 15
    Columns("Q:Q").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove

    Range("Q2").Resize(6, 1).Value = WorksheetFunction.Transpose(Array("waarde " & aantal, 50, 50, 60, 40, 30))
 
    ActiveSheet.ChartObjects("Grafiek 1").Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(aantal).Name = "waarde " & aantal
    ActiveChart.SeriesCollection(aantal).Values = "=Blad1!$Q$3:$Q$7"
End Sub

Niels
 
Het werkt perfect in mijn voorbeeld bestand! Dankjewel Niels!

Echter probeer ik nu het principe toe te passen bij mijn basis bestand, en ik loop alweer tegen problemen aan.
Het liefst zou ik het natuurlijk zelf oplossen zodat ik het helemaal snap, dus wellicht kan ik je om wat uitleg vragen;

Code: 
Sub Macro2()  <-- Dit is de naam van de Macro


    aantal = Range("q2").End(xlToRight).Column - 15
    Columns("Q:Q").Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove  <-- Deze twee regels maken een nieuwe kolom aan links van kolom Q

    Range("Q2").Resize(6, 1).Value = WorksheetFunction.Transpose(Array("waarde " & aantal, 50, 50, 60, 40, 30))  <-- Deze regel vult de kolom in met betreffende waarden
 
    ActiveSheet.ChartObjects("Grafiek 1").Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(aantal).Name = "waarde " & aantal
    ActiveChart.SeriesCollection(aantal).Values = "=Blad1!$Q$3:$Q$7"  <-- Deze vier regels vertellen tegen de grafiek dat er een nieuwe reeks aan toe moet worden gevoegd, waarde de reeksnaam "waarde " & aantal (= dus waarde +1) is, en de waarden in de cellen Q3:Q7 staan.

Aangezien mijn eindbestand nog geen vertrouwelijke informatie bevat kan ik deze ook bijvoegen, zodat het duidelijk wordt, wat er mis gaat:
Voer eerst macro 1 uit voor het uitvoeren van de basis macro, deze werkt in principe naar behoren. (en deze macro voegt dus ook die kolom toe, net als Niels bij zijn antwoord heeft getypt, deze stap ga ik dus ook verwijderen uit de nieuwe macro)
Voer daarna macro 2 uit, deze moet ervoor zorgen dat de zojuist aangemaakte kolom, met ingevulde waarde, wordt toegevoegd aan de grafiek.

Basis bestand:
Bekijk bijlage CAPACITEITSOVERZICHT - kopie.xlsm
 
Code: 
Sub Macro2()


aantal = ActiveSheet.Range("i4").End(xlDown).Row - 3 'bepaald het aantal projecten
If aantal > 10000 Then aantal = 1
 
    ActiveSheet.ChartObjects("Grafiek 2").Activate
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(aantal).Name = Range("i4").Value
    ActiveChart.SeriesCollection(aantal).Values = "='Urentabel 2013'!$C$3:$C$53"
  
End Sub
 
Ik heb nu een volgend probleem, ik probeer namelijk een 'toggle' functie te creëren zodat de nieuwe aangemaakte kolom wel of niet worden weergegeven in de grafiek. In mijn vorig bestand heb ik een macro gemaakt die een bepaalde vastgestelde kolom toont/verbergt. Alleen omdat ik nu met 'variabele' kolommen een grafiek maak, heb ik geen idee hoe ik dit vast kan leggen in mijn macro.

In m'n nieuwe voorbeeld bestand heb ik de toggle macro toegevoegd. Enig idee hoe ik een dergelijke checkbox (d.m.v. een macro) kan toevoegen waarbij de juiste kolom word verbergd (of gewoon de reeks in de grafiek aan/uitgezet kan worden).:

Code: 
 Sub CheckBox180_Click()
    With ActiveSheet.CheckBoxes(Application.Caller)
        If .Value = 1 Then
            Sheets("Urentabel 2013").Columns("C").EntireColumn.Hidden = False
        Else
            Sheets("Urentabel 2013").Columns("C").EntireColumn.Hidden = True
        End If
    End With
End Sub
 

Bijlagen

Laatst bewerkt:
Wouw... Helemaal super!
Grappig & slim met dat Wingdings 2 lettertype, daar had ik zelf niet aan gedacht!

Heel erg bedankt!
 
Ik dacht dat ik dit ook wel zelf kon, maar misschien kun jij me nogmaals helpen;

Ik heb nu een macro die onder behoud van bepaalde geselecteerde waarde in een cel, kolommen en rijen verwijderd met dat betreffende projectnummer (het bestand hierboven maakt namelijk met de eerste macro de nieuwe projecten aan).
Dit is me gelukt, alleen wanneer ik de kolommen verwijder verwijst de grafiek naar een #REF, waardoor deze reeks zichtbaar blijft met de waarde 1 (vult excel denk ik standaard in).

Ik kom er niet achter hoe ik deze chart nu ook die betreffende reeks kan laten verwijderen. Mijn macro is tot nu toe:

Code: 
Sub ProjectVerwijderen()
    
    Sheets("Urentabel 2013").Select
    Application.ScreenUpdating = False
    icol = Cells(2, Columns.Count).End(xlToLeft).Column
        For x = icol To 1 Step -1
        If Cells(2, x).Value = Range("A1") Then Cells(2, x).EntireColumn.Delete
        Next
    Application.ScreenUpdating = True
    Sheets("Urentabel 2014").Select
    Application.ScreenUpdating = False
    icol = Cells(2, Columns.Count).End(xlToLeft).Column
        For x = icol To 1 Step -1
        If Cells(2, x).Value = Range("A1") Then Cells(2, x).EntireColumn.Delete
        Next
    Application.ScreenUpdating = True
    Sheets("Capaciteitsoverzicht 2013").Select
    
        Dim i As Long
    With ActiveWorkbook.Sheets(1)
    For i = 100 To 1 Step -1
    If .Cells(i, "I") = Range("O3") Then
    .Cells(i, "I").EntireRow.Delete
    End If
Next i

End With
End Sub

Ik zat te denken om op het einde iets in de richting van dit toe te voegen, alleen dit krijg ik dus niet werkend:

Code: 
ActiveSheet.ChartObjects(1).Activate 
    Do While activechart.SeriesCollection.Name = Range("A1") 
    activechart.SeriesCollection(activechart.SeriesCollection.Name).delete

Maar laten we eerlijk wezen, ik snap vrij weinig van die grafieken in VBA code... Zou je me nog een keer uit de brand kunnen helpen?

Bvd!
 
Het te verwijderen projectnummer invullen op het eerste blad cel O3


Code: 
Sub ProjectVerwijderen()

    Dim StrProject As String
    Application.ScreenUpdating = False
    StrProject = Sheets(1).Range("O3").Value
    
    If StrProject = "" Then Exit Sub
    For Each sh In ThisWorkbook.Sheets(Array("Urentabel 2014", "Urentabel 2013"))
        icol = sh.Cells(2, Columns.Count).End(xlToLeft).Column
            For x = icol To 1 Step -1
            If sh.Cells(2, x).Value = StrProject Then sh.Cells(2, x).EntireColumn.Delete
        Next
    Next
    With ActiveWorkbook.Sheets(1)
        For i = 100 To 1 Step -1
            If .Cells(i, "I") = StrProject Then
                .ChartObjects("Grafiek 2").Activate
                ActiveChart.SeriesCollection(StrProject).Delete
                .Cells(i, "I").EntireRow.Delete
            End If
        Next
    End With
    Application.ScreenUpdating = True

End Sub


Niels
 
Laatst bewerkt:
En zelfs mijn loop eruitgehaald waardoor ik de macro geen lege rijen meer verwijderd.. Echt geweldig! Hij werkt ook nog eens veel sneller...

Heel erg bedankt!
 
Laatst bewerkt:
Goedemorgen,

In de grafiek moeten twee kolommen vast worden opgenomen en worden weergegeven als lijn, in tegenstelling tot de rest van de resultaten (die moeten weergegeven worden als gestapelde kolom). Dit zijn de target en budget kolommen.
Ik krijg dit redelijk voor elkaar, alleen zodra ik deze twee kolommen toevoeg, wijzigen de verder aangemaakte projecten in "Reeks 4" & "Reeks 5" etc. waardoor deze resultaten niet correct worden weergegeven.

Heb je toevallig nog een oplossing voor een seperate macro waarbij twee vast ingegeven kolommen worden toegevoegd als lijn aan de grafiek, zonder dat dit de overige reeksen beïnvloed?
Als voorbeeld heb ik een bestand ingevoegd waar is weergegeven wat ik bedoel:

Bekijk bijlage CAPACITEITSOVERZICHT - kopie 4.xlsm
 
Sorry ik weet niet hoe je een lijn toevoegd aan de grafiek.
Als je dat uitlegt dan kan ik er misschien wel een macro van maken.
Miscchien lukt je dat zelf ook wel door je handelingen op te nemen en de opgenomen macro dan aan te passen.
Anders lees ik het wel.

Niels
 
Hoi Niels,

Misschien was het handig voor mij om te vertellen dat die vaste kolommen de 'target' en 'budget' kolommen zijn. De code voor het 'aanpassen naar lijn' in VBA is:

Code: 
ActiveChart.SeriesCollection(5).ChartType = xlLine
ActiveChart.SeriesCollection(4).ChartType = xlLine

Afhankelijk van welke serie het is natuurlijk, dit is een voorbeeld.
Ik weet alleen niet hoe je die reekswaarden van deze kolommen dus vast insteld. Want ik krijg het wel voor elkaar om deze waarde toe te voegen, alleen wanneer ik ze als reeks 1 en 2 invoeg, dan zullen alle reeksen die erna nog komen, niet goed worden toegevoegd (deze worden elke keer als ik de macro voor de target en budget uitvoer, weer gereset worden, dus dan krijg ik Reeks 1 "Target", Reeks 2 "Budget", Reeks 3 "Reeks 3" (blanco), Reeks 4 "Reeks 4" (blanco))

Ik denk dat de oplossing erin zit dat de budget en target elke keer als 'laatste' reeks wordt toegevoegd. Alleen ik weet niet hoe ik die variabele toegevoegd krijg in VBA.
 
Status
Niet open voor verdere reacties.
                    Steun ons                    

Nieuwste berichten

Terug
Bovenaan Onderaan