Das Clever-Excel-Forum.de - Treffen
findet vom 15. - 17. September 2017 in Thüringen / Region Großer Inselsberg statt. Hotelbuchung ab sofort möglich.


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
?mage
to 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

Excel 2007
to top
#3
Hallöchen,
Die Sache mit 1 oder 01 bekommst Du auch mit dem Format -Befehl gebacken. :-)
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
to top
#4
(16.08.2014, 18: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.
?mage
to 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

Excel 2007
to top
#6
(16.08.2014, 19: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
?mage
to top
#7
Hallo Frank,
Wir geben uns alle Mühe :-)
   \\\|///      Hoffe, geholfen zu haben.
   ( ô ô )      Grüße, André aus G in T  
 ooO-(_)-Ooo    (Excel 97-2016)
to top


Möglicherweise verwandte Themen...
Thema Verfasser Antworten Ansichten Letzter Beitrag
  Code vorübergehend per Code ändern! cysu11 9 1.683 11.12.2014, 19:06
Letzter Beitrag: cysu11

Gehe zu:


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