Hi,
hier mal mein Vorschlag:
Option Explicit
Dim Bereich As Range
Dim Zellen() As Range
Private Sub UserForm_Initialize()
ZellenEinlesen
End Sub
Private Sub UserForm_Activate()
'Erstes Auswahlfeld Markieren
With TextBox2
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
'Überschrift
Me.TextBox1.Value = ActiveSheet.Range("B36").Text
Dim i%
For i = 0 To 11
Me.Controls("TextBox" & i + 2).Text = Zellen(i).Value
Next
End Sub
Private Sub ZellenEinlesen()
Dim E1%, E2%
Set Bereich = Range("E28:G31")
With Bereich
E1 = .Rows.Count
E2 = .Columns.Count
End With
ReDim Zellen(E1 * E2 - 1)
Dim r%, c%, i%
For r = 1 To E1
For c = 1 To E2
Set Zellen(i) = Bereich(r, c)
i = i + 1
Next
Next
End Sub
Private Sub CommandButton1_Click()
Dim i%
For i = 2 To 13
If Me.Controls("TextBox" & i).Text = "" Then
MsgBox ("Bitte einen Wert eingeben")
Exit For
End If
Next
If i > 13 Then Exit Sub
For i = 0 To 11
Zellen(i).Value = Me.Controls("TextBox" & i + 2).Text
Next
UF_Tabelle_01.Hide 'hier eintragen
End Sub
Private Sub CommandButton1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
CommandButton1.SetFocus
End Sub
Private Sub CommandButton2_Click()
UF_Tabelle_01.Hide 'hier eintragen
End Sub
Private Sub CommandButton2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
CommandButton2.SetFocus
End Sub
Private Sub CommandButton3_Click()
Dim x As Range
For Each x In Zellen
x = 0
Next
Unload Me
UF_Tabelle_01.Show 'hier eintragen
End Sub
Private Sub CommandButton3_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
CommandButton3.SetFocus
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Private Sub TextBox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Private Sub TextBox5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Private Sub TextBox6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Private Sub TextBox8_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Private Sub TextBox9_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Private Sub TextBox10_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Private Sub TextBox11_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Private Sub TextBox12_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Private Sub TextBox13_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(TextBox2 & Chr(KeyAscii)) Then
KeyAscii = 0
Beep
End If
End Sub
Ich finde es immer sinnvoll sowas zu kürzen. Irgendwann bastelt man doch wieder dran rum bzw. ist dann schneller, wenn man nochmal etwas Ähnliches programmieren will, außerdem sind Fehler schneller gefunden!
Noch schneller geht es vielleicht wenn du die Textboxen mit einer Klasse verknüpfst, in anderen Sprachen kann man Steuerelemente einfach beerben und muss solche Bedingungen nur einmal schreiben. In VBA ist das leider nicht so einfach, ist aber glaub ich auch irgendwie möglich.
Gruß
Till
|