mach doch beides
20.03.2011 15:04:37
Tino
Hallo,
wenn es nur ein UserForm_Initialize gibt, muss man eben beides in dem einen machen.
In etwa so sollte es gehen.
Option Explicit
Dim lngErsteKunden&, lngErsteAuftrag&
Private Sub UserForm_Initialize()
Dim rngSorte As Range
With Worksheets("Kundendaten")
Set rngSorte = .Range(.Cells(3, 2), .Cells(.Rows.Count, 2).End(xlUp).Offset(0, 1))
lngErsteKunden = rngSorte.Cells(1, 1).Row
Me.ComboBox1.RowSource = "'" & .Name & "'!" & rngSorte.Address
End With
With Me.ComboBox1
.ColumnCount = 50
.ListWidth = 200
.ColumnWidths = "50Pt;100Pt"
End With
With Worksheets("Auftrag")
Set rngSorte = .Range(.Cells(3, 1), .Cells(.Rows.Count, 1).End(xlUp))
lngErsteAuftrag = rngSorte.Cells(1, 1).Row
Me.ComboBox2.RowSource = "'" & .Name & "'!" & rngSorte.Address
End With
With Me.ComboBox1
.ColumnCount = 50
.ListWidth = 200
' .ColumnWidths = "50Pt;100Pt"
End With
End Sub
Private Sub CommandButton1_Click()
kunden_daten.Show
End Sub
Private Sub CommandButton2_Click()
auf_trag.Show
End Sub
Private Sub ComboBox1_Change()
Dim Zeile As Long
Zeile = lngErsteKunden + Me.ComboBox1.ListIndex
With Worksheets("Kundendaten")
Me.TextBox9 = .Cells(Zeile, 6).Value
Me.TextBox10 = .Cells(Zeile, 3).Value
Me.TextBox11 = .Cells(Zeile, 4).Value
Me.TextBox12 = .Cells(Zeile, 5).Value
Me.TextBox13 = .Cells(Zeile, 7).Value
Me.TextBox14 = .Cells(Zeile, 8).Value
Me.TextBox15 = .Cells(Zeile, 9).Value
Me.TextBox16 = .Cells(Zeile, 10).Value
End With
End Sub
Private Sub ComboBox2_Change()
Dim Zeile As Long
Zeile = lngErsteAuftrag + Me.ComboBox2.ListIndex
With Worksheets("Auftrag")
Me.TextBox17 = .Cells(Zeile, 2).Value
Me.TextBox18 = .Cells(Zeile, 3).Value
Me.TextBox19 = .Cells(Zeile, 4).Value
Me.TextBox20 = .Cells(Zeile, 5).Value
Me.TextBox21 = .Cells(Zeile, 7).Value
Me.TextBox22 = .Cells(Zeile, 8).Value
Me.TextBox23 = .Cells(Zeile, 9).Value
Me.TextBox24 = .Cells(Zeile, 10).Value
Me.TextBox25 = .Cells(Zeile, 11).Value
Me.TextBox26 = .Cells(Zeile, 12).Value
Me.TextBox27 = .Cells(Zeile, 13).Value
Me.TextBox28 = .Cells(Zeile, 14).Value
Me.TextBox29 = .Cells(Zeile, 1).Value
End With
End Sub
Gruß Tino