Option Explicit
Dim i As Integer
Dim Kost As String
Private Sub Userform_Initialize()
Dim i As Integer
For i = 2 To 6
Userform1.ComboBox1.AddItem _
Sheets("Vorgaben").Cells(i, 2).Value
Next i
Userform1.ComboBox1.ListIndex = 0
i = Userform1.ComboBox1.ListIndex
Userform1.cmdKostenstelle.Value = _
HoleKostenstelle(Userform1.ComboBox1.List(i))
Userform1.OptionButton1.Caption = _
Sheets("Vorgaben").Cells(2, 3).Value
Userform1.OptionButton2.Caption = _
Sheets("Vorgaben").Cells(3, 3).Value
Userform1.OptionButton3.Caption = _
Sheets("Vorgaben").Cells(4, 3).Valiue
Userform1.OptionButton1.Value = 1
End Sub
Function HoleKostenstelle(Abt As String) As String
Dim Kost As String
Application.ScreenUpdating = False
Sheets("Vorgaben").Activate
Range("B2").Select
Do Until ActiveCell.Value = "" Or Abt = ActiveCell.Value
ActiveCell.Offset(1, 0).Select
Loop
If Abt <> "" Then
Kost = ActiveCell.Offset(0, -1).Value
Else
Kost = ""
End If
HoleKostenstelle = Kost
Sheets("Mitarbeiter").Activate
Application.ScreenUpdating = True
End Function
Private Sub ComboBox1_Change()
i = Userform1.ComboBox1.ListIndex
Userform1.cmdKostenstelle.Value = _
HoleKostenstelle(Userform1.ComboBox1.List(i))
End Sub