wimgielis.com

Breid Excel tabellen automatisch uit

• 22/10/2016

	Voorbeeldbestanden bij dit artikel:
	Tabellen uitbreiden

Inleiding

Tabelfunctionaliteit in Excel is zeer handig en neemt een groot aantal (Excel) zorgen weg die je wel zou hebben mochten tabellen niet bestaan. Bijvoorbeeld, formules automatisch doortrekken naar alle rijen in de tabel, leesbare formules schrijven, enz. Ik heb er hier eerder over geblogd. Maar wat ik zelf niet zo prettig vind is hoe je op een makkelijke manier nieuwe rijen aan een tabel kan toevoegen.

• Ofwel moet je naar de laatste rij en kolom in de tabel gaan en Tab drukken
• Ofwel gebruik je de muis, rechtermuisklik en "Voeg rijen toe"
• Ofwel selecteer je een cel in de rij net onder de tabel, in een kolom niet berekend is met een formule, en begin je te typen. Dan zal de tabel uitbreiden.

Noot: als je de rij met totalen inschakelt voor de tabel, dan vervalt optie 3.

Dus, kunnen we dit op een intelligentere manier doen ? Ja maar door aangepaste VBA-codes te gebruken. Ik heb dat voor mezelf gedaan omdat het zo handig is, en ik ben zo onbaatzuchtig om de code hier voor mijn regelmatige websitebezoekers weg te geven. Opmerkingen of verbetering zijn steeds welkom per email.

Mijn oplossing

Ik gebruik de Worksheet_SelectionChange() gebeurtenis. Dat wordt telkens afgevuurd wanneer een nieuwe selectie gemaakt wordt door de gebruiker: met de muis, keyboard, VBA-code, ... Bij deze gebeurtenis ga ik kijken welke cel of cellen geselecteerd werden. Indien de selectie begint net 1 rij onder een bestaande tabel (zonder een rij tussen te laten) dan zal dit VBA-code uitvoeren die de tabel met 1 rij uitbreidt.

Voorbeeld: gewoon cel A16 selecteren (zie hierboven) zonder maar iets te typen voegt een nieuwe rij toe aan de tabel. Maar ik laat ook toe om cellen B16, C16, D16 of E16, of combinaties, te selecteren. Het eindresultaat is het ter beschikking hebben van 1 nieuwe rij.

Kunnen we ook automatisch 5 lijnen toevoegen ? Absoluut! Selecteer cellen A16:A20 en de code zal 5 lijnen toevoegen :-)

Eén van de nadelen van de methode met Tab (zie eerder) is dat je altijd naar een nieuwe lijn gaat maar terecht komt in de eerste kolom van de tabel. Dat kan goed zijn maar als de tabel links 3 kolommen bevat met berekende cellen, dan is dat niet echt nuttig en zelfs tijdverlies. Je zou liever willen springen naar bijvoorbeeld de 4de kolom in dat geval, om onmiddellijk data input te doen. Mijn code laat toe om op 3 verschillende manieren om te gaan met de keuze van de juiste kolom:

1. Optie 1: altijd naar de eerste kolom van de tabel gaan
2. Optie 2: in dezelfde kolom blijven als waar de gebruiker klikte
3. Optie 3: naar de eerste lege kolom gaan (als die niet lukt dan nemen we optie 1 - kolom 1 van de tabel)

Die mogelijkheden zal je zien terugkomen in de onderstaande codes.

Een Excel werkblad kan vanzelfsprekend meerdere tabellen bevatten. M.a.w. de code moet nagaan onder welke tabel geklikt werd zodat die tabel uitgebreid kan worden. Een lus doorheen de tabellen presenteer ik jullie in wat volgt.

Aangepaste code

wordt:

Kolommen A en E in het bestand worden berekend met de ASELECT functie zodat je de resultaten/getallen in deze kolommen zal zien veranderen bij het herberekenen of openen van het bestand :-)

Hier is de code voor de Worksheet_SelectionChange gebeurtenis:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    VoegRijenToeAanTabel Target, 3

End Sub

Daarna heb ik ook deze code die komt in een normale Module in VBA:

Sub VoegRijenToeAanTabel(r As Range, Optional iChoiceOfColumn As Integer = 3)
    Dim lo                    As ListObject
    Dim sTableName            As String
    Dim oNewRow               As ListRow
    Dim rng                   As Range
    Dim i                     As Long

    ''voor deze macro kan de selectie niet in een tabel zelf zijn
    ''anders zou gewoon in de tabel klikken steeds leiden tot extra nieuwe rijen
    On Error Resume Next
    sTableName = r.Cells(1).ListObject.Name
    On Error GoTo 0

    If Len(sTableName) > 0 Then
        Exit Sub
    End If

    ''zoek een tabel die de selectie matcht
    For Each lo In r.Parent.ListObjects

        On Error Resume Next
        sTableName = r.Cells(1).Offset(-1).ListObject.Name
        On Error GoTo 0

        If Len(sTableName) > 0 Then

            ''we hebben een tabel gevonden, laten we nu rijen toevoegen
            For i = 1 To r.Rows.Count
                Set oNewRow = lo.ListRows.Add(AlwaysInsert:=True)
                If i = 1 Then Set rng = oNewRow.Range
            Next

            ''selecteer een cel in de eerste nieuwe rij

            'we kunnnen:
            '1. altijd naar de eerste kolom van de tabel gaan
            '2. in dezelfde kolom blijven als waar de gebruiker klikte
            '3. naar de eerste lege kolom gaan (als die niet lukt dan nemen we optie 1 - kolom 1 van de tabel)
            Application.EnableEvents = False

            Select Case iChoiceOfColumn

            Case 1: 'naar de eerste kolom van de tabel gaan

                Application.Intersect(rng, lo.DataBodyRange.Columns(1)).Select

            Case 2: 'in dezelfde kolom blijven als waar de gebruiker klikte

                Application.Intersect(rng, r.Parent.Columns(r.Column)).Select

            Case 3: 'naar de eerste lege kolom gaan

                On Error Resume Next
                rng.SpecialCells(xlCellTypeBlanks).Cells(1).Select
                If Err.Number Then
                    Err.Clear
                    Application.Intersect(rng, lo.DataBodyRange.Columns(1)).Select
                End If
                On Error GoTo 0

            End Select

            Application.EnableEvents = True

            Exit For

        End If

    Next

End Sub

Ik gebruik deze code steeds in mijn bestanden omdat ik ze zo handig en tijdsbesparend vind, en in het algemeen, probeer ik steeds tabellen te gebruiken waar mogelijk.

Enjoy !

• « Vorig Excel artikel
• Volgend Excel artikel »