Excel VBA Interactief gebruikersformulier

Hieronder zullen we een programma bekijken in Excel VBA dat creëert een interactief gebruikersformulier. Het Userform dat we gaan maken ziet er als volgt uit:

Uitleg: telkens wanneer u een waarde invoert in het ID-tekstvak, laadt Excel VBA de bijbehorende record. Wanneer u op de knop Bewerken / Toevoegen klikt, bewerkt Excel VBA de record op het werkblad of voegt de record toe wanneer de ID nog niet bestaat. Met de knop Wissen worden alle tekstvakken gewist. De knop Sluiten sluit het gebruikersformulier.

Om dit gebruikersformulier aan te maken, voert u de volgende stappen uit.

1. Open de Visual Basic-editor. Als de Projectverkenner niet zichtbaar is, klikt u op Beeld, Projectverkenner.

2. Klik op Invoegen, Gebruikersformulier. Als de Toolbox niet automatisch verschijnt, klikt u op Beeld, Werkset. Uw scherm moet worden ingesteld zoals hieronder.

Gebruikersformulier Scherminstellingen in Excel VBA

3. Voeg de labels, tekstvakken (eerst bovenaan, de tweede onder de eerste, enzovoort) en opdrachtknoppen toe. Zodra dit is voltooid, moet het resultaat consistent zijn met de afbeelding van het eerder weergegeven gebruikersformulier. U kunt bijvoorbeeld een tekstvakbesturingselement maken door in de werkset op Tekstvak te klikken. Vervolgens kunt u een tekstvak op het gebruikersformulier slepen.

4. U kunt de namen en bijschriften van de bedieningselementen wijzigen. Namen worden gebruikt in de Excel VBA-code. Onderschriften zijn diegene die op je scherm verschijnen. Het is een goede gewoonte om de namen van de besturingselementen te wijzigen, maar dit is hier niet nodig, omdat we in dit voorbeeld slechts enkele besturingselementen gebruiken. Als u het bijschrift van de labels, tekstvakken en opdrachtknoppen wilt wijzigen, klikt u op Beeld, Venster Eigenschappen en klikt u op elk besturingselement.

5. Om het gebruikersformulier weer te geven, plaatst u een opdrachtknop op uw werkblad en voegt u de volgende coderegel toe:

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub

We gaan nu de Sub UserForm_Initialize maken. Wanneer u de methode Show voor het Userform gebruikt, wordt deze sub automatisch uitgevoerd.

6. Open de Visual Basic-editor.

7. Klik in de projectverkenner met de rechtermuisknop op UserForm1 en klik vervolgens op Code weergeven.

8. Kies Userform in de linker vervolgkeuzelijst. Kies Initialiseren in de vervolgkeuzelijst.

9. Voeg de volgende coderegel toe:

Private Sub UserForm_Initialize()

TextBox1.SetFocus

End Sub

Uitleg: deze coderegel stelt de focus op het eerste tekstvak in, omdat dit is waar we willen beginnen wanneer het gebruikersformulier is geladen.

We hebben nu het eerste deel van het gebruikersformulier gemaakt. Hoewel het er al goed uitziet, gebeurt er nog niets wanneer we een waarde invoeren in het ID-tekstvak of wanneer we op een van de opdrachtknoppen klikken.

10. Klik in de projectverkenner met de rechtermuisknop op UserForm1 en klik vervolgens op View Code.

11. Kies TextBox1 in de vervolgkeuzelijst links. Kies Wijzigen in de rechter vervolgkeuzelijst.

12. Voeg de volgende coderegel toe:

Private Sub TextBox1_Change()

GetData

End Sub

13. Dubbelklik in de projectverkenner op UserForm1.

14. Dubbelklik op de opdrachtknop Bewerken / Toevoegen.

15. Voeg de volgende coderegel toe:

Private Sub CommandButton1_Click()

EditAdd

End Sub

16. Dubbelklik op de knop Opdracht wissen.

17. Voeg de volgende coderegel toe:

Private Sub CommandButton2_Click()

ClearForm

End Sub

Uitleg: deze subs bellen andere subs die we in een seconde gaan aanmaken.

18. Dubbelklik op de opdrachtknop Sluiten.

19. Voeg de volgende coderegel toe:

Private Sub CommandButton3_Click()

Unload Me

End Sub

Uitleg: deze coderegel sluit het gebruikersformulier.

Tijd om de ondertitels te maken. Je kunt door ons Functie- en Subhoofdstuk gaan voor meer informatie over subs. Als u gehaast bent, plaatst u gewoon de volgende subs in een module (Klik in de Visual Basic Editor op Invoegen, Module).

20. Verklaar eerst drie variabelen van het type Integer en een variabele van het type Boolean. Verklaar de variabelen in de sectie Algemene verklaringen (bovenaan de module). Op deze manier hoeft u de variabelen slechts eenmaal te declareren en kunt u ze in meerdere subs gebruiken.

Dim id As Integer, i As Integer, j As Integer, flag As Boolean

21. Voeg de GetData-sub toe.

Sub GetData()

If IsNumeric(UserForm1.TextBox1.Value) Then
    flag = False
    i = 0
    id = UserForm1.TextBox1.Value

    Do While Cells(i + 1, 1).Value <> ""

        If Cells(i + 1, 1).Value = id Then
            flag = True
            For j = 2 To 3
                UserForm1.Controls("TextBox" & j).Value = Cells(i + 1, j).Value
            Next j
        End If

        i = i + 1

    Loop

    If flag = False Then
        For j = 2 To 3
            UserForm1.Controls("TextBox" & j).Value = ""
        Next j
    End If

Else
    ClearForm
End If

End Sub

Uitleg: Als het ID-tekstvak een numerieke waarde bevat, zoekt Excel VBA naar de ID en laadt de bijbehorende record. We gebruiken de Controls Collection om eenvoudig door tekstvakken te bladeren. Als Excel VBA de ID niet kan vinden (vlag is nog steeds False), leegt dit het tweede en derde tekstvak. Als het ID-tekstvak geen numerieke waarde bevat, roept Excel VBA de ClearForm-sub aan.

22. Voeg de ClearForm-sub toe.

Sub ClearForm()

For j = 1 To 3
UserForm1.Controls("TextBox" & j).Value = ""
Next j

End Sub

Uitleg: Excel VBA wist alle tekstvakken.

23. Voeg de Edit Edit-sub toe.

Sub EditAdd()

Dim emptyRow As Long

If UserForm1.TextBox1.Value <> "" Then
    flag = False
    i = 0
    id = UserForm1.TextBox1.Value
    emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1

    Do While Cells(i + 1, 1).Value <> ""

        If Cells(i + 1, 1).Value = id Then
            flag = True
            For j = 2 To 3
                Cells(i + 1, j).Value = UserForm1.Controls("TextBox" & j).Value
            Next j
        End If

        i = i + 1

    Loop

    If flag = False Then
        For j = 1 To 3
            Cells(emptyRow, j).Value = UserForm1.Controls("TextBox" & j).Value
        Next j
    End If

End If

End Sub

Uitleg: Als het ID-tekstvak niet leeg is, bewerkt Excel VBA de record op het blad (het tegenovergestelde van het laden van een record zoals we eerder hebben gezien). Als Excel VBA de ID niet kan vinden (vlag is nog steeds False), wordt de record toegevoegd aan de volgende lege rij. De variabele emptyRow is de eerste lege rij en neemt toe telkens wanneer een record wordt toegevoegd.

24. Sluit de Visual Basic Editor, voer de onderstaande labels in rij 1 in en test het gebruikersformulier.