Hi,
also ich habe mal deine Module von 22 auf 4 reduziert:
Kontrollmodul zum laden der Userform mit verschiedenen Parametern (aKontrollmodul):
Hier kannst du Parameter eingeben/verändern und erweitern.
Option Explicit
Sub EingabeStarten() '*Userform über dieses Makro aufrufen!
ShowForm
End Sub
Sub ShowForm(Optional ByVal Index As Integer = 0)
Load UF_Tabelle
Dim Parameter(10) As New UFParameter
Dim Start%, Sh As Worksheet
'Startblatt
Start = Sheets("GSP ORR PKW intern gesamt").Index
'verschiedene Parameter
With Parameter(0)
Set Sh = Sheets(Start)
Set .Bereich = Sh.Range("E28:G31")
.TitelText = Sh.Range("B36")
.TextBoxBereich = "2-13"
End With
With Parameter(1)
Set Sh = Sheets(Start + 1)
Set .Bereich = Sh.Range("E28:G31")
.TitelText = Sh.Range("B36")
.TextBoxBereich = "2-13"
End With
With Parameter(2)
'hier erweitern
End With
'...
'Auswahl Blatt
If Index > UBound(Parameter) Then Index = 0 'Anzahl Blätter
With UF_Tabelle
Set .aVorgegebenerBereich = Parameter(Index).Bereich
.aTitelZelle = Parameter(Index).TitelText
.aIndex = Index
.aTextBoxBereich = Parameter(Index).TextBoxBereich
End With
'Initialisieren & Anzeigen
With UF_Tabelle
.Caption = "Checkliste C Befüllung" & " - Blatt " & Index & "/10"
.Initialize
.Show
End With
End Sub
Klassenmodul für den Umgang mit den Textboxen (Datentransfer):
Option Explicit
Private Zellen() As Range
Private Controls() As Control
Public Bereich As Range
Public UF As UserForm
Public ControlType As String
Sub ZellenEinlesen(rng As Range)
Dim E1%, E2%
Set Bereich = rng
If Bereich Is Nothing Then Exit Sub
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
Sub ControlsEinlesen(ControlNames As String)
Dim Arr
Dim E1%, E2%, i%
If InStr(ControlNames, "-") Then
Arr = Split(ControlNames, "-")
If UBound(Arr) = 1 Then
If IsNumeric(Arr(0)) Then
E1 = Arr(0)
If IsNumeric(Arr(1)) Then
E2 = Arr(1)
Else: Exit Sub
End If
Else: Exit Sub
End If
ReDim Controls(E2 - E1)
Dim j%
For i = E1 To E2
Set Controls(j) = UF.Controls(ControlType & i)
j = j + 1
Next
End If
ElseIf InStr(ControlNames, ",") Then
Arr = Split(ControlNames, ",")
E2 = UBound(Arr)
ReDim Controls(E2)
For i = 0 To E2
Set Controls(i) = UF.Controls(ControlType & Arr(i))
Next
End If
End Sub
Sub Import()
If Not IsArray(Controls) Then Exit Sub
Dim i%
For i = 0 To UBound(Controls)
Controls(i).Text = Zellen(i)
Next
End Sub
Sub Export()
If Not IsArray(Controls) Then Exit Sub
Dim i%
For i = 0 To UBound(Controls)
Zellen(i) = Controls(i).Text
Next
End Sub
Function Überprüfen() As Boolean
If Not IsArray(Controls) Then Exit Sub
Dim X
For Each X In Controls
If X.Text = "" Then
MsgBox "Bitte " & X.Name & " ausfüllen!"
Exit Function
End If
Next
Überprüfen = True
End Function
Sub SteuerelementeResetten()
If Not IsArray(Controls) Then Exit Sub
Dim X
For Each X In Controls
X.Text = ""
Next
End Sub
Klassenmodul für Userform Parameter (UFParameter):
Public Bereich As Range
Public TextBoxBereich As String
Public TitelText As String
Und etwas veränderte Userform (UF_Tabelle):
Option Explicit
'Public
Public aVorgegebenerBereich As Range
Public aTitelZelle As String
Public aTextBoxBereich As String
Public aIndex%
'Initialisieren
Dim ZielBlatt As Worksheet
Dim DT As New Datentransfer
Sub Initialize()
If aVorgegebenerBereich Is Nothing Or aTextBoxBereich = "" Then
NewInput
Exit Sub
End If
aVorgegebenerBereich.Parent.Activate
With DT
Set .UF = Me
.ZellenEinlesen aVorgegebenerBereich
.ControlType = "TextBox"
.ControlsEinlesen aTextBoxBereich
.Import
End With
End Sub
Private Sub UserForm_Activate()
'Erstes Auswahlfeld Markieren
With TextBox2
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
'Überschrift
Me.TextBox1.Value = aTitelZelle
End Sub
'Schalter
Private Sub cmdSave_Click()
With DT
.ControlsEinlesen aTextBoxBereich
If .Überprüfen Then
.ControlsEinlesen aTextBoxBereich
.Export
End If
End With
End Sub
Private Sub cmdNewInput_Click()
NewInput
End Sub
Private Sub cmdAbort_Click()
Unload Me
End Sub
Private Sub cmdReset_Click()
DT.Bereich = 0
Unload Me
UF_Tabelle.Show 'hier eintragen
End Sub
'Eingabekontrolle
Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
Private Sub TextBox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
Private Sub TextBox5_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
Private Sub TextBox6_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
Private Sub TextBox7_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
Private Sub TextBox8_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
Private Sub TextBox9_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
Private Sub TextBox10_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
Private Sub TextBox11_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
Private Sub TextBox12_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
Private Sub TextBox13_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Check ActiveControl, KeyAscii
End Sub
'*****************
'*Hilfsfunktionen*
'*****************
Private Sub NewInput()
Set aVorgegebenerBereich = Nothing
aTitelZelle = ""
aTextBoxBereich = ""
DT.SteuerelementeResetten
ShowForm aIndex + 1
End Sub
'check if value is numeric and stop if it isnt
Private Sub Check(ctr As Control, Key As MSForms.ReturnInteger)
Dim str As String
If TypeOf ActiveControl Is Frame Then
str = Controls(Me.ActiveControl.Name).ActiveControl.Text
Else
str = ActiveControl.Text
End If
If Not IsNumeric(str & Chr(Key)) Then
Key = 0
Beep
End If
End Sub
'kann nach belieben alle zulässigen Controls anwählen
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Dim ctr As Control
For Each ctr In Me.Controls
Dim cX%, cY%
With ctr
If X >= .Left - 1 And X <= .Left + .Width + 1 Then
If Y >= .Top - 1 And Y <= .Top + .Height + 1 Then
If InStr(ctr.Name, "cmd") > 0 Then
ctr.SetFocus
End If
End If
End If
End With
Next
End Sub
Und hier die ganze Datei (Musste sie als .xls bearbeiten, solltest du aber auch mit 2007/2010 bearbeiten/konvertieren können):
http://www.yourfilelink.com/get.php?fid=821881
Gruß
Till
|