VBA Code schlanken
#1
Hallo, nach längerem schreiben an meinem noch nicht fertig Projekt "Kegeln",
hab ich etliche Zeilen mit der Endung "False" und "True",
bzw. Txt_W01 = "" bis Txt_W20 = "" und dann noch Txt_WR01 = "" bis Txt_WR20 = ""

Kann man solche Anweisungen nicht eventuell in kleine Pakete packen: Einzeiler pro Hauptname der Gruppe ( Txt_W; Txt_WR; Lbl_W; Lbl_WR ) ???
Ein paar Beispielcodeschnipsel
Code:
Private Sub CboSpiel_Change()
Dim i As Integer
Dim tb As TextBox
    With UserForm1.CboSpiel
        If .Value <> "" Then
            UserForm1.Txt_SpID.Value = .List(.ListIndex, 1)
            UserForm1.Txt_gesWurfKeg.Value = .List(.ListIndex, 3)
            UserForm1.Txt_Runden.Value = .List(.ListIndex, 2)
        Else
            UserForm1.Txt_SpID.Value = ""
            UserForm1.Txt_gesWurfKeg.Value = ""
            UserForm1.Txt_Runden.Value = ""
        End If
        UserForm1.Txt_W01.Visible = False
        UserForm1.Txt_W02.Visible = False
        UserForm1.Txt_W03.Visible = False
        UserForm1.Txt_W04.Visible = False
        UserForm1.Txt_W05.Visible = False
        UserForm1.Txt_W06.Visible = False
        UserForm1.Txt_W07.Visible = False
        UserForm1.Txt_W08.Visible = False
        UserForm1.Txt_W09.Visible = False
        UserForm1.Txt_W10.Visible = False
        UserForm1.Txt_W11.Visible = False
        UserForm1.Txt_W12.Visible = False
        UserForm1.Txt_WR01.Visible = False
        UserForm1.Txt_WR02.Visible = False
        UserForm1.Txt_WR03.Visible = False
        UserForm1.Txt_WR04.Visible = False
        UserForm1.Txt_WR05.Visible = False
        UserForm1.Txt_WR06.Visible = False
        UserForm1.Txt_WR07.Visible = False
        UserForm1.Txt_WR08.Visible = False
        UserForm1.Txt_WR09.Visible = False
        UserForm1.Txt_WR10.Visible = False
        UserForm1.Txt_WR11.Visible = False
        UserForm1.Txt_WR12.Visible = False
        UserForm1.Lbl_W01.Visible = False
        UserForm1.Lbl_W02.Visible = False
        UserForm1.Lbl_W03.Visible = False
        UserForm1.Lbl_W04.Visible = False
        UserForm1.Lbl_W05.Visible = False
        UserForm1.Lbl_W06.Visible = False
        UserForm1.Lbl_W07.Visible = False
        UserForm1.Lbl_W08.Visible = False
        UserForm1.Lbl_W09.Visible = False
        UserForm1.Lbl_W10.Visible = False
        UserForm1.Lbl_W11.Visible = False
        UserForm1.Lbl_W12.Visible = False
        UserForm1.Lbl_WR01.Visible = False
        UserForm1.Lbl_WR02.Visible = False
        UserForm1.Lbl_WR03.Visible = False
        UserForm1.Lbl_WR04.Visible = False
        UserForm1.Lbl_WR05.Visible = False
        UserForm1.Lbl_WR06.Visible = False
        UserForm1.Lbl_WR07.Visible = False
        UserForm1.Lbl_WR08.Visible = False
        UserForm1.Lbl_WR09.Visible = False
        UserForm1.Lbl_WR10.Visible = False
        UserForm1.Lbl_WR11.Visible = False
        UserForm1.Lbl_WR12.Visible = False
'        UserForm1.Label1.Visible = False
'        UserForm1.Label2.Visible = False
'        UserForm1.Label3.Visible = False
'        UserForm1.Label4.Visible = False
    End With
End Sub
oder
Code:
If CboSpiel = "2 auf die Vollen" Then
                        UserForm1.Txt_W01.Visible = True
                        UserForm1.Txt_W02.Visible = True
                        UserForm1.Lbl_W01.Visible = True
                        UserForm1.Lbl_W02.Visible = True
                    ElseIf CboSpiel = "gr.H.nr." Then
                        UserForm1.Txt_W01.Visible = True
                        UserForm1.Txt_W02.Visible = True
                        UserForm1.Txt_W03.Visible = True
                        UserForm1.Lbl_W01.Visible = True
                        UserForm1.Lbl_W02.Visible = True
                        UserForm1.Lbl_W03.Visible = True
                    ElseIf CboSpiel = "kl.H.nr." Then
                        UserForm1.Txt_W01.Visible = True
                        UserForm1.Txt_W02.Visible = True
                        UserForm1.Txt_W03.Visible = True
                        UserForm1.Lbl_W01.Visible = True
                        UserForm1.Lbl_W02.Visible = True
                        UserForm1.Lbl_W03.Visible = True
                    End If
                    
                   '----------------------------------------------------
                    If CboSpiel1 = "Dreihunderteins" Then
                        UserForm1.Txt_W01.Visible = True
                        UserForm1.Txt_W02.Visible = True
                        UserForm1.Txt_W03.Visible = True
                        UserForm1.Txt_W04.Visible = True
                        UserForm1.Txt_W05.Visible = True
                        UserForm1.Txt_W06.Visible = True
                        UserForm1.Txt_W07.Visible = True
                        UserForm1.Txt_W08.Visible = True
                        UserForm1.Txt_W09.Visible = True
                        UserForm1.Txt_W10.Visible = True
                        UserForm1.Lbl_W01.Visible = True
                        UserForm1.Lbl_W02.Visible = True
                        UserForm1.Lbl_W03.Visible = True
                        UserForm1.Lbl_W04.Visible = True
                        UserForm1.Lbl_W05.Visible = True
                        UserForm1.Lbl_W06.Visible = True
                        UserForm1.Lbl_W07.Visible = True
                        UserForm1.Lbl_W08.Visible = True
                        UserForm1.Lbl_W09.Visible = True
                        UserForm1.Lbl_W10.Visible = True
                    ElseIf CboSpiel1 = "Fünfhunderteins" Then
                        UserForm1.Txt_W01.Visible = True
                        UserForm1.Txt_W02.Visible = True
                        UserForm1.Txt_W03.Visible = True
                        UserForm1.Txt_W04.Visible = True
                        UserForm1.Txt_W05.Visible = True
                        UserForm1.Txt_W06.Visible = True
                        UserForm1.Txt_W07.Visible = True
                        UserForm1.Txt_W08.Visible = True
                        UserForm1.Txt_W09.Visible = True
                        UserForm1.Txt_W10.Visible = True
                        UserForm1.Txt_W11.Visible = True
                        UserForm1.Txt_W12.Visible = True
                        UserForm1.Txt_WR01.Visible = True
                        UserForm1.Txt_WR02.Visible = True
                        UserForm1.Txt_WR03.Visible = True
                        UserForm1.Lbl_W01.Visible = True
                        UserForm1.Lbl_W02.Visible = True
                        UserForm1.Lbl_W03.Visible = True
                        UserForm1.Lbl_W04.Visible = True
                        UserForm1.Lbl_W05.Visible = True
                        UserForm1.Lbl_W06.Visible = True
                        UserForm1.Lbl_W07.Visible = True
                        UserForm1.Lbl_W08.Visible = True
                        UserForm1.Lbl_W09.Visible = True
                        UserForm1.Lbl_W10.Visible = True
                        UserForm1.Lbl_W11.Visible = True
                        UserForm1.Lbl_W12.Visible = True
                        UserForm1.Lbl_WR01.Visible = True
                        UserForm1.Lbl_WR02.Visible = True
                        UserForm1.Lbl_WR03.Visible = True
                    ElseIf CboSpiel1 = "Bingo" Then
                        UserForm1.Txt_W01.Visible = True
                        UserForm1.Txt_W02.Visible = True
                        UserForm1.Txt_W03.Visible = True
                        UserForm1.Txt_W04.Visible = True
                        UserForm1.Txt_W05.Visible = True
                        UserForm1.Lbl_W01.Visible = True
                        UserForm1.Lbl_W02.Visible = True
                        UserForm1.Lbl_W03.Visible = True
                        UserForm1.Lbl_W04.Visible = True
                        UserForm1.Lbl_W05.Visible = True
                    End If
vorab für konstruktives Helfen, Danke
Top
#2
Hallo Frank,

das geht über die Controls Eigenschaft.
Dazu solltest Du die Steuerelemente nich mit 01, 02 etc am Ende nummerieren, sonder mit 1, 2, 3 etc.

Also statt Txt_W01 so: Txt_W1

Dann kannst Du sie in einer Scleife nach diesem Muster abarbeien:

Code:
Dim i As Long
For i = 1 To 12
   UserForm1.Controls("Txt_W" & i).Visible = False
Next i

obige Code-Zeilen würden diese Zeilen bei Dir ersetzen:

Code:
UserForm1.Txt_W01.Visible = False
        UserForm1.Txt_W02.Visible = False
        UserForm1.Txt_W03.Visible = False
        UserForm1.Txt_W04.Visible = False
        UserForm1.Txt_W05.Visible = False
        UserForm1.Txt_W06.Visible = False
        UserForm1.Txt_W07.Visible = False
        UserForm1.Txt_W08.Visible = False
        UserForm1.Txt_W09.Visible = False
        UserForm1.Txt_W10.Visible = False
        UserForm1.Txt_W11.Visible = False
        UserForm1.Txt_W12.Visible = False
Gruß Atilla
Top
#3
Hallöchen,
Die Sache mit 1 oder 01 bekommst Du auch mit dem Format -Befehl gebacken. Smile
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Top
#4
(16.08.2014, 19:39)atilla schrieb: ...statt Txt_W01 so: Txt_W1...
Hallo Attila, freue mich über diese schnelle Hilfe. Danke
Werde sofort Code dahingehend ändern.

Schönes W.E.
Top
#5
Hallo Andre,


richtig, bin nicht darauf gekommen.

Frank mit Andres Tipp kannst Du die Bezeichnungen so lassen wie gehabt, dann sähe der Code so aus:

Code:
Me.Controls("Txt_W" & Format(i, "00")).Visible = False
Gruß Atilla
Top
#6
(16.08.2014, 20:14)atilla schrieb: Hallo Andre,


richtig, bin nicht darauf gekommen.

Frank mit Andres Tipp kannst Du die Bezeichnungen so lassen wie gehabt, dann sähe der Code so aus:

Code:
Me.Controls("Txt_W" & Format(i, "00")).Visible = False
Hallo André & Atilla :19:
Ha ha ha, wollt'z mich ärgern :100: (leichtes schmunzeln über den Wangen)
Gerade denn ganzen Code im Editor nach allen Relevanten durchsucht und ersetzt.
Danke für den Tipp, werd morgen mal den Original-Code nochmals kopieren und diese Änderung hineinpflegen. So - Kino-Time (Blu Ray) :15:
Top
#7
Hallo Frank,
Wir geben uns alle Mühe Smile
.      \\\|///      Hoffe, geholfen zu haben.
       ( ô ô )      Grüße, André aus G in T  
  ooO-(_)-Ooo    (Excel 97-2019+365)
Top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste