Als u een dashboard wilt maken met een diagram waarvan de gegevens worden gewijzigd volgens de geselecteerde opties, kunt u de gebeurtenissen in VBA gebruiken. Ja, het kan. We hebben geen vervolgkeuzelijst, slicer of combobox nodig. We zullen cellen klikbaar maken en gegevens wijzigen om een grafiek te maken van de geselecteerde cel.
Volg de onderstaande stappen om dynamische grafieken in Excel te maken die veranderen volgens de celselectie.
Stap 1: bereid de gegevens in een blad voor als bron voor de grafiek.
Hier heb ik enkele voorbeeldgegevens uit verschillende regio's in een blad. Ik noemde het brongegevens.
Stap 2: Haal de gegevens van één regio tegelijk op een ander blad.
- Voeg nu een nieuw blad in. Noem het passend. Ik noemde het "Dashboard".
- Kopieer alle maanden in één kolom. Schrijf de naam van een regio naast de maand.
- Nu willen we gegevens van de regio in cel D1 ophalen. We willen dat de gegevens veranderen als de regio verandert in D1. Daarvoor kunnen we de Two Way Lookup gebruiken.
Aangezien mijn brongegevens in A2:D8 op Source Datasheet staan. Ik gebruik onderstaande formule.
| =VERT.ZOEKEN(C2,'Brongegevens'!$A$2:$D$8,BIJ ELKAAR PASSEN($D$1,'Brongegevens'!$A$1:$D$1,0)) |
Hier gebruiken we dynamische kolomindexering voor VERT.ZOEKEN. Je kunt er hier over lezen.
- Voeg een diagram in met behulp van deze gegevens op het dashboardblad. Ik gebruik een eenvoudig lijndiagram. Verberg de bron van de grafiek als u deze niet wilt weergeven.
Als u nu de regionaam in D1 verandert, verandert de grafiek dienovereenkomstig. De volgende stap is om de regionaam in D1 te wijzigen terwijl u een optie selecteert in de opgegeven cel.
Stap 3: Wijzig de regio terwijl u een regionaam in het opgegeven bereik selecteert.
- Schrijf alle regionamen in een bereik, ik schrijf ze in bereik A2:A4.
- Klik met de rechtermuisknop op de naam van het Dashboard-blad en klik op de optie "Code weergeven" om rechtstreeks naar de werkbladmodule in VBE te gaan, zodat we de werkbladgebeurtenis kunnen gebruiken.
- Schrijf nu onderstaande code in VB Editor.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Not Intersect(Target, Range("A2:A4")) Is Nothing Then Range("A2:A4").Interior.ColorIndex = xlColorIndexNone Dim regio As Variant region = Target.value Bij fout Ga naar fout: Selecteer Case regio Case Is = "Central" Range ("D1").value = regio Case Is = "East" Range ("D1").value = regio Case Is = "West" Range ("D1" ").value = regio Case Else MsgBox "Ongeldige optie" Einde Selecteer Target.Interior.ColorIndex = 8 Einde Indien fout: Einde Sub
En het is gedaan. Wanneer u nu een cel in het bereik A2:A4 selecteert, wordt de waarde ervan toegewezen aan D1 en veranderen de gegevens van de grafiek dienovereenkomstig.
Ik heb hieronder uitgelegd hoe deze code werkt. U kunt het begrijpen en wijzigingen aanbrengen volgens uw vereisten. Ik heb links naar Help-onderwerpen gegeven die ik hier in dit voorbeeld heb gebruikt. Bekijk ze dus zeker eens.
Hoe werkt de code?
Hier heb ik de Event van Excel gebruikt. Ik heb een werkbladgebeurtenis "SelectionChange" gebruikt om de gebeurtenissen te activeren.
Als niet snijden (doel, bereik ("A2: A4")) is dan niets
Deze regel stelt de focus in op het bereik A2:A4, zodat de gebeurtenis SelectionChange alleen wordt geactiveerd als de selectie zich in het bereik A2:A4 bevindt. De code tussen If en End wordt alleen uitgevoerd als de selectie zich in het bereik A2:A4 bevindt. U kunt het nu naar wens instellen om uw grafiek dynamisch te maken.
Bereik ("A2:A4").Interior.ColorIndex = xlColorIndexNone
Deze regel stelt de kleur van bereik A2:A4 in op niets.
region = Target.value On Error GoTo err:
In de bovenstaande twee regels krijgen we de waarde van de geselecteerde cellen in het variabele gebied en negeren we eventuele fouten. gebruik de regel "On Error GoTo err:" niet totdat u zeker weet dat u een eventuele fout wilt negeren. Ik heb het gebruikt om een fout te voorkomen wanneer ik meerdere cellen selecteer.
Selecteer Case regio Case Is = "Centraal" Range("D1").value = regio Case Is = "Oost" Range("D1").value = regio Case Is = "West" Range("D1").value = regio Case Anders MsgBox "Ongeldige optie" Einde Selecteren
In de bovenstaande regels gebruiken we Excel Select Case Statement om de waarde van bereik D1 in te stellen.
Target.Interior.ColorIndex = 8 End If err: End Sub
Voor de End If-instructie veranderen we de kleur van de geselecteerde optie zodat deze wordt gemarkeerd. Dan eindigt het If-statement en err: tag begint. De instructie On Error springt naar deze tag als er een fout optreedt tijdens de select-instructie.
Download het werkbestand hieronder.
Ingesloten grafiekgebeurtenissen met VBA in Microsoft Excel| De ingesloten grafiekgebeurtenissen kunnen uw grafiek interactiever, dynamischer en nuttiger maken dan normale grafieken. Om de gebeurtenissen op grafieken mogelijk te maken, hebben we …
De gebeurtenissen in Excel VBA |Er zijn zeven soorten gebeurtenissen in Excel. Elk evenement behandelt in verschillende reikwijdte. Application Event behandelt op werkmapniveau. Werkmap op bladenniveau. Werkblad Gebeurtenis op bereikniveau.
De werkbladgebeurtenissen in Excel VBA|De werkbladgebeurtenis is erg handig als u wilt dat uw macro's worden uitgevoerd wanneer een opgegeven gebeurtenis op het werkblad plaatsvindt.
Werkmapgebeurtenissen met VBA in Microsoft Excel | De werkmapgebeurtenissen werken op de hele werkmap. Omdat alle bladen deel uitmaken van de werkmap, werken deze gebeurtenissen ook op hen.
Voorkom dat een automacro/eventmacro wordt uitgevoerd met VBA in Microsoft Excel| Gebruik de shift-toets om het uitvoeren van de macro auto_open te voorkomen.
Objectgebeurtenissen in kaart brengen met VBA in Microsoft Excel| De grafieken zijn complexe objecten en er zijn verschillende componenten die u eraan hebt gekoppeld. Om de Chart Events te maken gebruiken we de Class module.
Populaire artikels:
50 Excel-snelkoppelingen om uw productiviteit te verhogen | Word sneller in uw taak. Met deze 50 sneltoetsen werkt u nog sneller in Excel.
De VERT.ZOEKEN-functie in Excel | Dit is een van de meest gebruikte en populaire functies van Excel die wordt gebruikt om waarde op te zoeken uit verschillende bereiken en bladen.
AANTAL.ALS in Excel 2016 | Tel waarden met voorwaarden met behulp van deze geweldige functie. U hoeft uw gegevens niet te filteren om een specifieke waarde te tellen. Countif-functie is essentieel om uw dashboard voor te bereiden.
Hoe de SUMIF-functie in Excel te gebruiken? | Dit is een andere essentiële functie van het dashboard. Dit helpt u bij het optellen van waarden voor specifieke voorwaarden.
