AW: Variablenzuweisung nicht korrekt
22.04.2004 17:00:45
Holger
Hallo Ulf
Das ist der Anfang
' Mitarbeiterinformationen
Sub Mitarb_Info()
Dim WSpA As Variant, WSpB As Variant, WSpC As Variant, WSpD As Variant, _
WSpE As Variant, NameAuf As Variant, NameInfo As Variant, _
StrAuf As Variant, StraßeInfo As Variant, BlattInfo As Variant, _
Leer As Variant, A As Variant, X As Variant, GI As Variant, istLeer As Variant
Dim zäRow, zaPos, zeigen As Long
Dim EIName, EIStraße, EINameVergl As Boolean
Dim Anzeige As UserForm1
Set Anzeige = UserForm1
Dim AktArb, AktBl As Object
Set AktArb = Application.ActiveWorkbook
Set AktBla = Application.ActiveSheet
zäRow = 2
zeigen = 0
StrAuf = ""
GI = "Gebäude-Info"
BlattInfo = "Informationen"
NameAuf = Range("B6").Value
StrAuf = Range("B8").Value ---------------HIER HAPERT ES --------------
NameAuf = Mid(NameAuf, 1, 1)
NameAuf = (NameAuf & "*")
Workbooks("Info von Mitarbeitern.xls").Activate
StraßeInfo = Sheets(BlattInfo).Cells(zäRow, 3).Value
Der Rest ist nur ellen lang
wie gesagt wenn ich in der zeile zeigen = 0 einen Haltepunkt setze und dann weiter mache funktioniert es
irgend eine Idee
While StraßeInfo <> ""
EIStraße = [StraßeInfo] Like StrAuf
If EIStraße = True Then
NameInfo = Sheets(BlattInfo).Cells(zäRow, 1).Value
EINameVergl = NameInfo Like NameAuf
If EINameVergl = True Then
zeigen = zeigen + 1
X = X + 1
If X = 1 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label6.Caption = GI
Anzeige.Label6.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label6.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label7.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label8.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label9.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox1.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox1.Visible = True
UserForm1.Label6.Visible = True
UserForm1.CommandButton1.Top = 60
UserForm1.CommandButton2.Top = 60
UserForm1.Height = 108
ElseIf X = 2 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label10.Caption = GI
Anzeige.Label10.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label10.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label11.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label12.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label13.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox2.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox2.Visible = True
UserForm1.Label10.Visible = True
UserForm1.CommandButton1.Top = 84
UserForm1.CommandButton2.Top = 84
UserForm1.Height = 132
ElseIf X = 3 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label14.Caption = GI
Anzeige.Label14.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label14.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label15.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label16.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label17.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox3.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox3.Visible = True
UserForm1.Label14.Visible = True
UserForm1.CommandButton1.Top = 108
UserForm1.CommandButton2.Top = 108
UserForm1.Height = 156
ElseIf X = 4 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label18.Caption = GI
Anzeige.Label18.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label18.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label19.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label20.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label21.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox4.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox4.Visible = True
UserForm1.Label18.Visible = True
UserForm1.CommandButton1.Top = 132
UserForm1.CommandButton2.Top = 132
UserForm1.Height = 180
ElseIf X = 5 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label22.Caption = GI
Anzeige.Label22.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label22.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label23.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label24.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label25.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox5.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox5.Visible = True
UserForm1.Label22.Visible = True
UserForm1.CommandButton1.Top = 156
UserForm1.CommandButton2.Top = 156
UserForm1.Height = 204
ElseIf X = 6 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label27.Caption = GI
Anzeige.Label27.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label27.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label28.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label29.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label30.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox6.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox6.Visible = True
UserForm1.Label27.Visible = True
UserForm1.CommandButton1.Top = 180
UserForm1.CommandButton2.Top = 180
UserForm1.Height = 228
ElseIf X = 7 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label31.Caption = GI
Anzeige.Label31.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label31.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label32.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label33.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label34.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox7.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox7.Visible = True
UserForm1.Label31.Visible = True
UserForm1.CommandButton1.Top = 204
UserForm1.CommandButton2.Top = 204
UserForm1.Height = 252
ElseIf X = 8 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label35.Caption = GI
Anzeige.Label35.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label35.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label36.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label37.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label38.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox8.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox8.Visible = True
UserForm1.Label35.Visible = True
UserForm1.CommandButton1.Top = 228
UserForm1.CommandButton2.Top = 228
UserForm1.Height = 276
ElseIf X = 9 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label39.Caption = GI
Anzeige.Label39.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label39.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label40.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label41.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label42.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox9.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox9.Visible = True
UserForm1.Label39.Visible = True
UserForm1.CommandButton1.Top = 252
UserForm1.CommandButton2.Top = 252
UserForm1.Height = 300
ElseIf X = 10 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label43.Caption = GI
Anzeige.Label43.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label43.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label44.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label45.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label46.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox10.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox10.Visible = True
UserForm1.Label43.Visible = True
UserForm1.CommandButton1.Top = 276
UserForm1.CommandButton2.Top = 276
UserForm1.Height = 324
ElseIf X = 11 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label47.Caption = GI
Anzeige.Label47.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label47.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label48.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label49.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label50.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox11.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox11.Visible = True
UserForm1.Label47.Visible = True
UserForm1.CommandButton1.Top = 300
UserForm1.CommandButton2.Top = 300
UserForm1.Height = 348
ElseIf X = 12 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label51.Caption = GI
Anzeige.Label51.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label51.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label52.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label53.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label54.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox12.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox12.Visible = True
UserForm1.Label51.Visible = True
UserForm1.CommandButton1.Top = 324
UserForm1.CommandButton2.Top = 324
UserForm1.Height = 372
ElseIf X = 13 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label55.Caption = GI
Anzeige.Label55.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label55.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label56.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label57.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label58.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox13.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox13.Visible = True
UserForm1.Label55.Visible = True
UserForm1.CommandButton1.Top = 348
UserForm1.CommandButton2.Top = 348
UserForm1.Height = 396
ElseIf X = 14 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label59.Caption = GI
Anzeige.Label59.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label59.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label60.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label61.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label62.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox14.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox14.Visible = True
UserForm1.Label59.Visible = True
UserForm1.CommandButton1.Top = 372
UserForm1.CommandButton2.Top = 372
UserForm1.Height = 420
ElseIf X = 15 Then
istLeer = Sheets(BlattInfo).Cells(zäRow, 1).Value
If istLeer = "" Then
Anzeige.Label63.Caption = GI
Anzeige.Label63.BackColor = RGB(255, 255, 0)
Else
Anzeige.Label63.Caption = Sheets(BlattInfo).Cells(zäRow, 1).Value
End If
Anzeige.Label64.Caption = Sheets(BlattInfo).Cells(zäRow, 2).Value
Anzeige.Label65.Caption = Sheets(BlattInfo).Cells(zäRow, 3).Value
Anzeige.Label66.Caption = Sheets(BlattInfo).Cells(zäRow, 4).Value
Anzeige.TextBox15.Value = Sheets(BlattInfo).Cells(zäRow, 5).Value
UserForm1.CheckBox15.Visible = True
UserForm1.Label63.Visible = True
UserForm1.CommandButton1.Top = 396
UserForm1.CommandButton2.Top = 396
UserForm1.Height = 444
ElseIf X = 16 Then
Anzeige.Label26.Visible = True
UserForm1.Width = 705
UserForm1.Show
Exit Sub
End If
End If
End If
zäRow = zäRow + 1
StraßeInfo = Sheets(BlattInfo).Cells(zäRow, 3).Value
Wend
If zeigen > 0 Then
UserForm1.Show
Exit Sub
Else
End If
Workbooks(AktArb.Name).Activate
Application.ScreenUpdating = True
End Sub