MultiPages
11.07.2004 23:38:48
sigi
ich habe eine MultiPage in der Userform1 erstellt
Jede Page hat eine Listbox welche jeweils Daten eines Tabellenblattes einliest.
Nun mein Problem:
Wie kann ich die Listboxes selectieren.
In meinem Beispiel werden immer die Daten von Tabelle1 angezeigt.
Bei Doppelclick wird in den einzelnen Text oder ComboBoxen jedoch die
richtigen Daten ausgegeben.
Wie muß ich hier verfahren?
Danke für die Hilfe
Nachfolgend die Anweisungen für die Listboxes.
Option Explicit
Private Sub UserForm_Initialize()
On Error Resume Next
Dim A_Datum As Date
Dim A_BV As Variant
Dim A_von As Variant
Dim A_bis As Variant
Dim A_Bewölkung As Variant
Dim A_Niederschlag As Variant
Dim A_Wind As Variant
Dim A_Temp As Variant
Dim A_Luft As Variant
Dim A_Zeit As Variant
Dim B_Datum As Date
Dim B_BV As Variant
Dim B_FA As Variant
Dim B_Eigen As Variant
Dim B_P1 As Variant
Dim B_B1 As Variant
Dim B_P2 As Variant
Dim B_B2 As Variant
Dim B_P3 As Variant
Dim B_B3 As Variant
Dim B_P4 As Variant
Dim B_B4 As Variant
Dim I As Variant
Dim sAdr As Variant
Dim Arr() As Variant
Dim iRowL As Integer, iRow As Integer, iCol As Integer, iRowU As Integer
UserForm1.ListBox1.Clear
iRowL = Sheets("1").Cells(Rows.Count, 1).End(xlUp).Row
For iRow = 2 To iRowL
If Not IsEmpty(Sheets("1").Cells(iRow, 1)) Then
ReDim Preserve Arr(0 To 6, 0 To iRowU)
Arr(0, iRowU) = Sheets("1").Cells(iRow, 1)
Arr(1, iRowU) = Sheets("1").Cells(iRow, 2)
Arr(2, iRowU) = Sheets("1").Cells(iRow, 3)
Arr(2, iRowU) = FormatDateTime(Arr(2, iRowU), vbShortTime)
Arr(3, iRowU) = Sheets("1").Cells(iRow, 4)
Arr(3, iRowU) = FormatDateTime(Arr(3, iRowU), vbShortTime)
iRowU = iRowU + 1
End If
Next iRow
UserForm1.ListBox1.Column = Arr
UserForm1.ListBox1.ListIndex = iRow - 3
UserForm1.ListBox1.Selected(UserForm1.ListBox1.ListIndex) = False
'Firmen
UserForm1.ListBox2.Clear
iRowL = Sheets("2").Cells(Rows.Count, 1).End(xlUp).Row
For iRow = 2 To iRowL
If Not IsEmpty(Sheets("2").Cells(iRow, 1)) Then
ReDim Preserve Arr(0 To 6, 0 To iRowU)
Arr(0, iRowU) = Sheets("2").Cells(iRow, 1)
Arr(1, iRowU) = Sheets("2").Cells(iRow, 2)
Arr(2, iRowU) = Sheets("2").Cells(iRow, 3)
'Arr(3, iRowU) = Sheets("2").Cells(iRow, 4)
iRowU = iRowU + 1
End If
Next iRow
UserForm1.ListBox2.Column = Arr
UserForm1.ListBox2.ListIndex = iRow - 3
UserForm1.ListBox2.Selected(UserForm1.ListBox2.ListIndex) = False
UserForm1.CommandButton6.Enabled = False
UserForm1.TextBox3.Visible = False
UserForm1.TextBox5.Value = "Bitte wählen Sie ein Datum aus..."
' Tagesdaten
Private Sub ListBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
Dim A_Datum As Date
Dim A_BV As Variant
Dim A_von As Variant
Dim A_bis As Variant
Dim A_Bewölkung As Variant
Dim A_Niederschlag As Variant
Dim A_Wind As Variant
Dim A_Temp As Variant
Dim A_Luft As Variant
Dim A_Zeit As Variant
Dim I As Integer
Dim Nummer As Integer
Nummer = UserForm1.ListBox1.ListIndex
Nummer = Nummer + 2
A_von = FormatDateTime(A_von, vbShortTime)
A_bis = FormatDateTime(A_bis, vbShortTime)
A_Zeit = FormatDateTime(A_Zeit, vbShortTime)
UserForm1.TextBox1.Visible = True
UserForm1.TextBox1.Value = "Änderungsmodus"
A_Datum = Sheets("1").Cells(Nummer, 1)
A_BV = Sheets("1").Cells(Nummer, 2)
A_von = Sheets("1").Cells(Nummer, 3)
A_bis = Sheets("1").Cells(Nummer, 4)
A_Bewölkung = Sheets("1").Cells(Nummer, 5)
A_Niederschlag = Sheets("1").Cells(Nummer, 6)
A_Wind = Sheets("1").Cells(Nummer, 7)
A_Temp = Sheets("1").Cells(Nummer, 8)
A_Luft = Sheets("1").Cells(Nummer, 9)
A_Zeit = Sheets("1").Cells(Nummer, 10)
UserForm1.CommandButton3.Enabled = True
A_von = FormatDateTime(A_von, vbShortTime)
A_bis = FormatDateTime(A_bis, vbShortTime)
A_Zeit = FormatDateTime(A_Zeit, vbShortTime)
UserForm1.ComboBox1.Value = A_Datum
UserForm1.ComboBox2.Value = A_BV
UserForm1.ComboBox3.Value = A_von
UserForm1.ComboBox4.Value = A_bis
UserForm1.ComboBox5.Value = A_Bewölkung
UserForm1.ComboBox6.Value = A_Niederschlag
UserForm1.ComboBox7.Value = A_Wind
UserForm1.ComboBox8.Value = A_Temp
UserForm1.ComboBox9.Value = A_Luft
UserForm1.ComboBox10.Value = A_Zeit
End Sub
'*********************************************************************************************************************
' Firmendaten
'*********************************************************************************************************************
Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
On Error Resume Next
Dim B_Datum As Date
Dim B_BV As Variant
Dim B_FA As Variant
Dim B_Eigen As Variant
Dim B_P1 As Variant
Dim B_B1 As Variant
Dim B_P2 As Variant
Dim B_B2 As Variant
Dim B_P3 As Variant
Dim B_B3 As Variant
Dim B_P4 As Variant
Dim B_B4 As Variant
Dim I As Integer
Dim Nummer As Integer
Nummer = UserForm1.ListBox2.ListIndex
Nummer = Nummer + 2
UserForm1.TextBox3.Visible = True
UserForm1.TextBox3.Value = "Änderungsmodus"
UserForm1.CommandButton3.Enabled = True
B_Datum = Sheets("2").Cells(Nummer, 1)
B_BV = Sheets("2").Cells(Nummer, 2)
B_Eigen = Sheets("2").Cells(Nummer, 3)
B_FA = Sheets("2").Cells(Nummer, 4)
B_P1 = Sheets("2").Cells(Nummer, 5)
B_B1 = Sheets("2").Cells(Nummer, 6)
B_P2 = Sheets("2").Cells(Nummer, 7)
B_B2 = Sheets("2").Cells(Nummer, 8)
B_P3 = Sheets("2").Cells(Nummer, 9)
B_B3 = Sheets("2").Cells(Nummer, 10)
B_P4 = Sheets("2").Cells(Nummer, 11)
B_B4 = Sheets("2").Cells(Nummer, 12)
ComboBox11.Value = B_Datum
ComboBox12.Value = B_BV
ComboBox13.Value = B_FA
TextBox4.Value = B_Eigen
ComboBox14.Value = B_P1
ComboBox15.Value = B_B1
ComboBox16.Value = B_P2
ComboBox17.Value = B_B2
ComboBox18.Value = B_P3
ComboBox19.Value = B_B3
ComboBox20.Value = B_P4
ComboBox21.Value = B_B4
End Sub