Textboxe på userforms/formularer i Excel VBA

Når man arbejder med VBA (makroer) i Excel, har man ofte brug for brugerens input via en Userform (formular), som man selv designer. Det kan være tekst (f.eks. et navn) eller talværdier, og tekstboksen er et af de mest brugte kontrolelementer til den slags.

Man indsætter en eller flere tekstbokse på sin formular, og beder brugeren indtaste de relevante data. Derefter må man kontrollere, om brugerens indtastning nu også "fulgte reglerne". Det nytter jo ikke noget, at han indsætter en tekst, hvis man skal bruge et tal.

Herunder følger et eksempel på en simpel kontrol af indholdet i en tekstboks, og derefter viser jeg, hvordan man kan gøre arbejdet meget nemmere med et klassemodul (class module), hvis man har mange tekstbokse, som skal behandles på samme måde.

Klasseløsningen forhindrer også brugeren i at paste en værdi/tekst ind i din tekstboks.

Du kan markere koden her på siden, kopiere den (CTRL + C) og sætte den ind (CTRL + V) i din VBA-kode. Bruger du en lille skærm, kan nogle af kodelinjerne være tekstombrudte, men linjeskiftene er OK, når du indsætter det kopierede.

Den simple kontrol

Hvis du f.eks. har en formular med en tekstboks (TextBox1) og en kommandoknap, kan du bruge flg. kode i kommandokanappens klikprocedure (Private Sub CommandButton1_Click()), som køres, når brugeren klikker på kommandoknappen. Vi antager, at brugeren skulle indsætte et tal.

Først kontrolleres, om tekstboksen indeholder en numerisk værdi eller har længden 0. Hvis det indtastede ikke er en talværdi, sletter vi tekstboksens indhold, placerer cursoren i tekstboksen (.SetFocus kommandoen) og undlader at lukke formularen.

Hvis det derimod er en talværdi, konverterer vi den til datatypen Double, indsætter værdien i celle A1 på det aktive ark og lukker formularen.

Når vi konverterer til datatypen Double, er det fordi indholdet i en tekstboks altid er af datatypen String, og så ville det gå galt, hvis vi f.eks. skulle bruge værdien til et regnestykke i vores kode.

Private Sub CommandButton1_Click()
Dim dTal As Double

With TextBox1
   If IsNumeric(.Text) = False Or Len(.Text) = 0 Then
      .Text = ""
      MsgBox "Det skal være et tal"
      .SetFocus
      Exit Sub
   Else
      'Teksten konverteres til et tal
      dTal = CDbl(.Text)
   End If
End With

'Tallet indsættes i celle A1
Range("A1").Value = dTal

'Formularen lukkes
Unload Me
End Sub

Den smarte måde: TextBox klassen

Ovenstående er fint nok til en simpel kontrol, men hvis man har mange tekstbokse på én eller flere formularer, og de alle (eller nogle af dem) skal behandles på samme måde, er det et stort arbejde at skrive kontrolkode for hver eneste tekstbox.

Det kan man løse smart ved at lave en tekstboksklasse, så al kontrol kun foretages ét sted. Det kan være, at alle bogstaver skal være med stort, eller at der kun må indtastes tal. Fidusen er at fortælle VBA, at tekstboksene på en formular er af tekstboksklassen, og når brugeren så indsætter noget i en af tekstboksene, udløser handlingen (en "event"), at inputtet behandles i tekstboksklassens kode.

Det lyder lidt indviklet og er da også lidt langhåret, men du behøver jo ikke forstå det hele med det samme - bare det virker!

Start med at lave en userform og indsæt nogle tekstboxe. Antallet er underordnet. Du skal også indsætte en kommandoknap, som brugeren kan klikke på for at lukke formularen.

Derefter kopierer du følgende kode og indsætter den øverst i formularens kode. Bemærk, at jeg deklarerer en global variabel: "Public InputTalCol As Collection". Det vil man normalt gøre i et modul og ikke i en formular.

Option Explicit
Public InputTalCol As Collection

'Initialize-proceduren kører, når formularen åbnes
Private Sub UserForm_Initialize()
Dim InputTalEvt As clTextBoxClass
Dim ctl As control

On Error GoTo ErrorHandle

'Opretter den collection som skal
'indeholde vores textboxklasser.
Set InputTalCol = New Collection

'Nu gennemløbes formularens kontrolelementer for textboxe
For Each ctl In Me.Controls
   'Hvis kontrolelementet er en textbox
   If TypeOf ctl Is MSForms.TextBox Then
      'oprettes et nyt eksemplar af textboxklassen
      Set InputTalEvt = New clTextBoxClass
      'og knytter det til denne textbox
      Set InputTalEvt.InputTextBox = ctl
      'som vi tilføjer vores collection, InputTalCol
      InputTalCol.Add InputTalEvt
   End If
Next

Exit Sub
ErrorHandle:
MsgBox Err.Description
End Sub

'Og her klik-proceduren for vores kommandoknap
Private Sub CommandButton1_Click()
'Kører når brugeren klikker på kommandoknappen

'Indsæt kode her for evt. aktion før
'formularen lukkes.

'Nu destrueres vores collection med dens indhold
'af textboxklasser for at frigøre hukommelse.
Set InputTalCol = Nothing

'Formularen lukkes
Unload Me
End Sub

Og nu til klassemodulet

Lav et klassemodul (i VBA-editorens menu vælger du "Insert" og "Class Module"), og i klassemodulets Properties vindue ændrer du navnet (Name) til "clTextBoxClass". Hvis dette vindue ikke er synligt, kan du vælge det i menuen "View" eller bruge genvejstasten F4.

Derefter markerer og kopierer du koden herunder (CTRL + C) og indsætter den (CTRL + V) i klassemodulets kodevindue. Men lad mig først lige forklare, hvad der foregår.

I dette eksempel accepteres kun talværdier, komma og minus (for negative tal). Dette styres af proceduren

Private Sub InputTextBox_keypress _
(ByVal KeyAscii As MSForms.ReturnInteger)

som kaldes, når brugeren trykker på en tast. Vi kontrollerer tegnets Ascii-kode og accepterer kun tal.

Det samme kan selvfølgelig gøres, hvis vi f.eks. kun vil acceptere store bogstaver, og nederst på siden kan du se, hvordan proceduren så skal se ud.

Keypress-proceduren fanger imidlertid kun "normale" tastetryk (ANSI-karakterer), så hvis brugeren paster en kopieret tekst ind i tekstboksen med CTRL + V, bliver vi snydt. Klassen forhindrer derfor brugeren i at paste tekst ind i en tekstboks. Løsningen er måske ikke den mest elegante, men den virker.

Private Sub InputTextBox_BeforeDropOrPaste

som fanger, hvis brugeren vil paste noget. Her sættes et flag "bPaste = True", som efterfølgende vil få proceduren

Private Sub InputTextBox_Change()

Option Explicit
Public WithEvents InputTextBox As MSForms.TextBox
Private mvarPaste As Boolean
Private mvarText As String

'****
Private Sub InputTextBox_keypress _
(ByVal KeyAscii As MSForms.ReturnInteger)
'Tillader kun tal, komma og minus
'Ascii 44 er komma, Ascii 45 er minus.

Select Case KeyAscii
   Case 44 To 57
   Case Else
      KeyAscii = 0
End Select

'Vi gemmer teksten i en variabel
sText = InputTextBox.Text

End Sub

'****
Private Sub InputTextBox_BeforeDropOrPaste _
(ByVal Cancel As MSForms.ReturnBoolean, _
ByVal Action As MSForms.fmAction, _
ByVal Data As MSForms.DataObject, _
ByVal X As Single, ByVal Y As Single, _
ByVal Effect As MSForms.ReturnEffect, _
ByVal Shift As Integer)

'Hvis brugeren skal til at paste, sættes et flag
bPaste = True

End Sub

'****
Private Sub InputTextBox_Change()
'Denne procedure eksekveres,
'når textboxens indhold er ændret.

With InputTextBox
'Hvis flaget for paste er sat,
'sættes teksten til det, den
'var tidligere.
   If bPaste Then
      bPaste = False
      .Text = sText
   End If
End With

End Sub


'Her følger vores properties. De kunne også
'være deklareret som variable på modulniveau,
'men da det nu er et klassemodul, har jeg
'for eksemplets skyld lavet properties.
Property Get bPaste() As Boolean
   bPaste = mvarPaste
End Property
Property Let bPaste(ByVal vData As Boolean)
   mvarPaste = vData
End Property
Property Get sText() As String
   sText = mvarText
End Property
Property Let sText(ByVal vData As String)
   mvarText = vData
End Property

Hvis du kun vil tillade store bogstaver

I ovenstående eksempel tillod vi kun tal. Her følger den Keypress-procedure, du skal anvende i stedet, hvis du kun vil tillade store bogstaver i dine tekstbokse:

Private Sub InputTextBox_keypress _
(ByVal KeyAscii As MSForms.ReturnInteger)
'Omdanner små bogstaver til store

Select Case KeyAscii
'Kontrolkarakterer
Case 8 To 10, 13, 27
   KeyAscii = KeyAscii
'Store bogstaver
Case 65 To 90, 194, 197, 198, 212, 216, 219
   KeyAscii = KeyAscii
'Små bogstaver laves til store
Case 97 To 122, 226, 229, 230, 244, 248, 251
   KeyAscii = Asc(UCase$(Chr$(KeyAscii)))
'Drop alt andet
Case Else
   KeyAscii = 0
End Select

'Vi gemmer teksten i en variabel
sText = InputTextBox.Text

End Sub

Andre events

Med textboxklassen kan man fange og behandle næsten de samme events, som man kan i formularens textbox-kode. Dvs. events som:

Public WithEvents InputTextBox As MSForms.TextBox

hvor det centrale er udtrykket "WithEvents" - som vel bedst kan beskrives som "når der indtræffer en begivenhed eller handling med vores textbox".

Af samme grund kan vi ikke som i formularen fange en ting som "AfterUpdate" eller "Exit", for i de situationer er løbet så at sige kørt.