Hallo Detlev,
grade erst deinen neuen Beitrag gesehen, eine Prozedur dafür zu verwenden ist natürlich besser als meine Lösung, welche auch garnicht so funktioniert, wie ich grade festgestellt habe. Bin bei dir allerdings auch skeptisch, woher nimmst du die Variable "tb1"?
Ich würd's so machen:
'check if value is numeric and stop if it isnt
Private Sub Check(str$, Key As MSForms.ReturnInteger)
If Not IsNumeric(str & Chr(Key)) Then
Key = 0
Beep
End If
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Insgesamt dann so:
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
'check if value is numeric and stop if it isnt
Private Sub Check(str$, Key As MSForms.ReturnInteger)
If Not IsNumeric(str & Chr(Key)) Then
Key = 0
Beep
End If
End Sub
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Private Sub TextBox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Private Sub TextBox5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Private Sub TextBox6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Private Sub TextBox8_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Private Sub TextBox9_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Private Sub TextBox10_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Private Sub TextBox11_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Private Sub TextBox12_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
Private Sub TextBox13_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl.Text, KeyAscii
End Sub
|