AW: Textboxen etc. löschen
04.10.2005 22:37:55
Walter
Hallo K.Rola,
hoffentlich blickts Du durch meinen "Schungel",
Gruß Walter
Private Sub UserForm_Initialize()
Application.ScreenUpdating = False
Dim Datei
Dim Fname
Dim bolOpen As Boolean
Dim wb As Workbook
Dim wsDatabase As Worksheet
Dim aVarData()
Dim VarTmp
Dim HerstNr
Dim TypNr
'--------------- für Bildschirmanpassung --------------------------------------
Dim hwndForm As Long, hwndMenu As Long
Dim intY, intLast, intNext As Integer
On Error Resume Next
With walter
.StartUpPosition = 0
.Top = 0
.Left = 0
.Height = GetDeviceCaps(GetDC(0&), 8)
.Width = GetDeviceCaps(GetDC(0&), 10)
End With
ReleaseDC 0, GetDC(0&)
hwndForm = FindWindow(vbNullString, Me.Caption)
'------------ ab hier festgelegt, UF kann nicht verschoben werden ----------------
If hwndForm <> 0 Then
hwndMenu = GetSystemMenu(hwndForm, 0)
If hwndMenu <> 0 Then DeleteMenu hwndMenu, &HF010, &H0
End If
walter.Caption = " Daten - Eingabe - Maske " & _
" " & _
" " & _
getStrUpdate() ' "Update: 01.09.05 Vers.VB 97-33/1mR / 01.04.05"
Sheets("Prov-Blatt").Select
Range("A1").Select
Sheets("Prov-Blatt").Unprotect ("wwpa")
Sheets("Kulanzblatt-VK").Visible = True
Sheets("Kulanzblatt-VK").Select
Range("A1").Select
Sheets("Kulanzblatt-VK").Unprotect ("wwpa")
Sheets("Auftragsblatt").Visible = True
Sheets("Auftragsblatt").Select
Range("A1").Select
Sheets("Auftragsblatt").Unprotect ("wwpa")
Sheets("GF-TAB-Neu").Visible = True
Sheets("GF-TAB-Neu").Select
Range("A1").Select
Sheets("GF-TAB-Neu").Unprotect ("wwpa")
Sheets("Datenbank").Visible = True
Sheets("Datenbank").Select
Range("A1").Select
Sheets("Datenbank").Unprotect ("wwpa")
Sheets("Prov-Blatt").Select
Range("A1").Select
'--------------- verk 1 -------------------------------------------
Label1.Caption = ThisWorkbook.Worksheets("Kulanzblatt-VK").Range("Y11")
TextBox1.Value = Format(ThisWorkbook.Worksheets("Kulanzblatt-VK").Range("Y10").Value, ("000")) 'VK-Nr.
If Worksheets("Kulanzblatt-VK").Range("H71").Value = "1" Then
OptionButton1 = True
OptionButton1.ForeColor = &H8000&
End If
If Worksheets("Kulanzblatt-VK").Range("H71").Value = "2" Then
OptionButton2.ForeColor = &H8000&
OptionButton2 = True
Else
OptionButton2.ForeColor = &H80000012
End If
If Worksheets("Kulanzblatt-VK").Range("H71").Text = "3" Then
OptionButton3.ForeColor = &H8000&
OptionButton3 = True
Else
OptionButton3.ForeColor = &H80000012
End If
If Worksheets("Kulanzblatt-VK").Range("H71").Text = "4" Then
OptionButton5.ForeColor = &H8000&
OptionButton5 = True
Else
OptionButton5.ForeColor = &H80000012
End If
If Worksheets("Kulanzblatt-VK").Range("H71").Text = "5" Then
OptionButton6.ForeColor = &H8000&
OptionButton6 = True
Else
OptionButton6.ForeColor = &H80000012
End If
If Worksheets("Kulanzblatt-VK").Range("H71").Text = "6" Then
OptionButton7.ForeColor = &H8000&
OptionButton7 = True
Else
OptionButton7.ForeColor = &H80000012
End If
If TextBox30 = "0,00" Then
OptionButton1.ForeColor = &H80000012 'Grün
OptionButton2.ForeColor = &H80000012 'Schwarz
OptionButton3.ForeColor = &H80000012 'Schwarz
OptionButton5.ForeColor = &H80000012 'Schwarz
OptionButton6.ForeColor = &H80000012 'Schwarz
OptionButton7.ForeColor = &H80000012 'Schwarz
End If
'---------- Vk-Abfrage dann Topf-Nr. -----------------------------------------------
If Worksheets("Kulanzblatt-VK").Range("U3") > 0 Then
TextBox38.Enabled = True
TextBox38.BackColor = vbWhite
TextBox38 = Format(Worksheets("Kulanzblatt-VK").Range("U3").Text, ("000")) 'Topf-Nr.
Label63.Enabled = True
Else
TextBox38.Enabled = False
TextBox38.BackColor = Me.BackColor
Label63.Enabled = False
End If
TextBox33 = Format(Worksheets("Kulanzblatt-VK").Range("AH22").Value, "00"))
TextBox4 = Format(Worksheets("Kulanzblatt-VK").Range("AI22").Value,"00"))
TextBox5 = Format(Worksheets("Kulanzblatt-VK").Range("AJ22").Value, ("0000")) 'Jahr
TextBox14 = Format(Worksheets("Kulanzblatt-VK").Range("T11").Value, ("000 000"))
TextBox7 = Worksheets("Kulanzblatt-VK").Range("AU337").Value
TextBox8 = Worksheets("Kulanzblatt-VK").Range("AU338").Value ' Vorname
TextBox22 = Worksheets("Kulanzblatt-VK").Range("AY338").Value ' Kunden-Name
TextBox9 = Worksheets("Kulanzblatt-VK").Range("AU339").Value ' Ansprechpartner
TextBox10 = Worksheets("Kulanzblatt-VK").Range("AU341").Value
TextBox11 = Worksheets("Kulanzblatt-VK").Range("AY341").Value
TextBox12 = Format(Worksheets("Kulanzblatt-VK").Range("AU342").Value, ("00000")) ' PLZ
TextBox13 = Worksheets("Kulanzblatt-VK").Range("AY342").Value ' Ort
TextBox15 = Worksheets("Kulanzblatt-VK").Range("BA378").Value
If Worksheets("Kulanzblatt-VK").Range("M18") > "0" Then
TextBox16 = Format(CDate(Worksheets("Kulanzblatt-VK").Range("AU378").Value), ("DD.MM.YYYY"))
End If
TextBox17 = Format(Worksheets("Kulanzblatt-VK").Range("BA379").Value, ("0,000")) 'KM
TextBox18 = Worksheets("Kulanzblatt-VK").Range("V14").Value 'Bau Code
TextBox24 = Format(Worksheets("Kulanzblatt-VK").Range("T19").Text, ("0 000 00000"))
If Worksheets("Kulanzblatt-VK").Range("T20") <= "0" Then
Worksheets("Kulanzblatt-VK").Range("T20") = ""
Else
TextBox25 = Format(Worksheets("Kulanzblatt-VK").Range("T20").Value, ("0.0"))
End If
'--------------- Prov. 1,24 -------------------------------------------------
TextBox30.Value = Format(Worksheets("Kulanzblatt-VK").Range("U63").Value, ("0.00"))
'--------------- Eroberung 0,50 -------------------------------------------------
If CheckBox1 = True Then
TextBox35.Enabled = True
TextBox35.BackColor = vbWhite
Else
TextBox35.Enabled = False
TextBox35.BackColor = Me.BackColor
End If
TextBox35.Value = Format(Worksheets("Kulanzblatt-VK").Range("T67").Value, ("0.00"))
If Worksheets("Kulanzblatt-VK").Range("AN393") = "Mercedes" Then
MsgBox "Sie haben Mercedes als Hersteller ausgewählt." _
& Chr(13) & Chr(13) & _
" Mercedes wird NICHT als Eroberungsprämie gewertet !" & Chr(13) & Chr(13) & _
Chr(13) & Chr(13) & "Eroberungsprämie im EINGABEFELD wird gelöscht !" _
, vbInformation, " Hinweis !"
TextBox35.Value = "0.00"
ThisWorkbook.Worksheets("Kulanzblatt-VK").Range("T67") = TextBox35.Text
TextBox35 = Format(Worksheets("Kulanzblatt-VK").Range("T67").Text, ("#,##0.00"))
End If
If Worksheets("Kulanzblatt-VK").Range("T67").Value > "0" Then
Worksheets("Kulanzblatt-VK").Range("U23").Value = "J"
End If
'--------------- NDL Euro Prämie -------------------------------------------------
TextBox31 = Format(Worksheets("Kulanzblatt-VK").Range("T69").Value, ("#,##0.00"))
'--------------- Verk. 2 Endbenutzer Verkäufer Nr.--------------------------------
TextBox36.Value = Format(ThisWorkbook.Worksheets("Kulanzblatt-VK").Range("T71").Value, ("000"))
'--------------- Verkäufer 2 Prov. ENDBENUTZER ----------------------------------
TextBox37.Value = Format(Worksheets("Kulanzblatt-VK").Range("U73").Value, ("0.00"))
'-------------------- Endbenutzer-Provison ----------------------------------------
USFKulanzblatt.Label182.Caption = Format(Worksheets("Kulanzblatt-VK").Range("I66").Value, ("#,##0.00"))
USFKulanzblatt.Label183.Caption = Format(Worksheets("Kulanzblatt-VK").Range("M66").Value, ("0.00")) 'NDL W/T/Z
'--------------- NDL Prov in Euro + % --------------------------------
USFKulanzblatt.Label113.Caption = Format(Worksheets("Kulanzblatt-VK").Range("I48").Value, ("#,##0.00")) 'NDL W/T/Z
USFKulanzblatt.Label140.Caption = Format(Worksheets("Kulanzblatt-VK").Range("M48").Value, ("0.00"))
USFKulanzblatt.Label133.Caption = Format(Worksheets("Kulanzblatt-VK").Range("I49").Value, ("#,##0.00")) 'NDL W/T/Z
USFKulanzblatt.Label141.Caption = Format(Worksheets("Kulanzblatt-VK").Range("M49").Value, ("#,##0.00")) 'NDL W/T/Z
'---------------- Kauf - Datum festlegen NEU ---------------------------------
If TextBox39.Value = Format(Now, "dd.mm.yyyy") <> "" Then
TextBox39.Value = Worksheets("Kulanzblatt-VK").Range("F11")
Else
TextBox39.Value = Format(Now, "dd.mm.yyyy")
End If
'------------------ NDL Nr. -----------------------------------------
Sheets("Kulanzblatt-VK").Select
ComboBox3.RowSource = ("Z18:Z23")
' ComboBox3.ListIndex = 0 ' ersten Wert anzeigen bleibt die NDL Nr.
ComboBox3.Value = Worksheets("Kulanzblatt-VK").Range("U1")
'------------------ GF Box -----------------------------------------
'If Worksheets("Prov-Blatt").Range("AD86").Value >= "0" Then
If Worksheets("Kulanzblatt-VK").Range("AU396").Value >= "0" Then
Sheets("GF-Tab-Neu").Select
ComboBox1.RowSource = ("I62:I77")
Sheets("Kulanzblatt-VK").Select
ComboBox1.ListIndex = Worksheets("Kulanzblatt-VK").Range("AN394") ComboBox2.ListIndex = Worksheets("Kulanzblatt-VK").Range("AN397")
Else
CheckBox1.Value = False
ComboBox1.Visible = False
ComboBox2.Visible = False
Label25.Enabled = False 'Grau hinterlegt
Label25.Visible = False 'ausblenden kompl.
End If
'---------------- Provision Kennzeichen -------------------------------
Dim dat As Date
dat = CDate(Worksheets("Kulanzblatt-VK").Range("F11"))
If dat >= DateValue("01.07.2005") Then
Sheets("Kulanzblatt-VK").Select
ComboBox4.RowSource = ("BG322:BG332")
ComboBox4.Value = Worksheets("Kulanzblatt-VK").Range("BG318")
Else
Sheets("Kulanzblatt-VK").Select
Sheets("Kulanzblatt-VK").Unprotect ("wwpa")
ComboBox4.RowSource = ("AV322:AV332")
ComboBox4.Value = Worksheets("Kulanzblatt-VK").Range("AV318")
End If
'------------------ Fahrzeug Typ Auswählen -------------------------------
Sheets("Kulanzblatt-VK").Select
ComboBox6.RowSource = ("V89:V500")
ComboBox6.Value = Worksheets("Kulanzblatt-VK").Range("T10")
If Worksheets("Kulanzblatt-VK").Range("H3").Value = "" And _
Worksheets("Kulanzblatt-VK").Range("L3").Value = "" And _
Worksheets("Kulanzblatt-VK").Range("Q3").Value = "" Then
CheckBox2.Value = False
CheckBox5.Value = False
CheckBox9.Value = False
End If
If Worksheets("Kulanzblatt-VK").Range("T10").Value <= "0" Then
CheckBox11.Enabled = False
CheckBox12.Enabled = False
CheckBox26.Enabled = False
CheckBox27.Enabled = False
CheckBox28.Enabled = False
End If
If Worksheets("Kulanzblatt-VK").Range("T10").Value > "0" Then
CheckBox11.Enabled = True
CheckBox12.Enabled = True
CheckBox26.Enabled = True
CheckBox27.Enabled = True
CheckBox28.Enabled = True
End IF
'--------------- Alters Bonus --------------------------------------------
Sheets("Kulanzblatt-VK").Select
Sheets("Kulanzblatt-VK").Unprotect ("wwpa")
ComboBox7.RowSource = ("Z66:Z78")
ComboBox7.Value = Worksheets("Kulanzblatt-VK").Range("Z65")
Worksheets("Kulanzblatt-VK").Range("AA65") = ComboBox7.ListIndex
'----------------- ab hier ausblenden ------------------------------------
If CheckBox2.Value = False And CheckBox5.Value = False And CheckBox9.Value = False Then
CheckBox16.Value = False
CheckBox16.BackColor = Me.BackColor
CheckBox16.Enabled = False
Else
CheckBox16.Enabled = True
End If
'------------- ab hier ausblenden wenn noch kein NW oder VF festgelegt ist --------
If CheckBox2.Value = False And CheckBox5.Value = False And CheckBox9.Value = False Then
CheckBox11.Value = False
CheckBox11.BackColor = Me.BackColor
CheckBox12.Value = False
CheckBox12.BackColor = Me.BackColor
CheckBox26.Value = False
CheckBox26.BackColor = Me.BackColor
CheckBox27.Value = False
CheckBox27.BackColor = Me.BackColor
CheckBox28.Value = False
CheckBox28.BackColor = Me.BackColor
ComboBox7.BackColor = Me.BackColor
ComboBox7.Enabled = False
ComboBox8.BackColor = Me.BackColor
ComboBox8.Enabled = False
CheckBox14.Value = False
CheckBox14.BackColor = Me.BackColor
CheckBox14.Enabled = False
CheckBox13.Value = False
CheckBox13.BackColor = Me.BackColor
CheckBox13.Enabled = False
CheckBox16.Value = False 'ASV-Verm. CheckBox
CheckBox16.BackColor = Me.BackColor
CheckBox16.Enabled = False
CheckBox6.Value = False
CheckBox6.BackColor = Me.BackColor
CheckBox6.Enabled = False
CheckBox7.Value = False
CheckBox7.BackColor = Me.BackColor
CheckBox7.Enabled = False
End If
If CheckBox2.Value = False Then
Label7.Enabled = False
TextBox24.Enabled = False
TextBox24.BackColor = Me.BackColor
TextBox24.Enabled = False
Label34.Enabled = False 'Produktions Code
TextBox18.Enabled = False
TextBox18.BackColor = Me.BackColor
Label35.Enabled = False
Label36.Enabled = False
TextBox25.Enabled = False
TextBox25.BackColor = Me.BackColor
Label31.Enabled = False
Label22.Enabled = False
Label23.Enabled = False
End If
If CheckBox2.Value = True Then
Label7.Enabled = True
TextBox24.Enabled = True
TextBox24.BackColor = vbWhite
Label34.Enabled = True 'Produktions Code
TextBox18.Enabled = True
TextBox18.BackColor = vbWhite
Label35.Enabled = True 'Altersboni
ComboBox7.BackColor = vbWhite
ComboBox7.Enabled = True
ComboBox8.BackColor = vbWhite
ComboBox8.Enabled = True
Label36.Enabled = True
TextBox25.Enabled = True
TextBox25.BackColor = vbWhite
Label31.Enabled = True
CheckBox14.Enabled = True
CheckBox13.Enabled = True
' TextBox20.Enabled = True
'TextBox20.BackColor = vbWhite
CheckBox16.Enabled = True
Label22.Enabled = True
Label23.Enabled = False
CheckBox6.Enabled = True
CheckBox7.Enabled = True
End If
If CheckBox5.Value = True Then Label26.Enabled = True
If CheckBox5.Value = True Then Label27.Enabled = True
If CheckBox5.Value = True Then Label28.Enabled = True
If CheckBox5.Value = True Then TextBox15.Visible = True
If CheckBox5.Value = True Then TextBox16.Visible = True
If CheckBox5.Value = True Then TextBox17.Visible = True
If CheckBox5.Value = False Then Label26.Enabled = False
If CheckBox5.Value = False Then Label27.Enabled = False
If CheckBox5.Value = False Then Label28.Enabled = False
If CheckBox5.Value = False Then TextBox15.Visible = False
If CheckBox5.Value = False Then TextBox16.Visible = False
If CheckBox5.Value = False Then TextBox17.Visible = False
'------------ Neuwagen ob Kreuz drin ist ------------------------------
If Worksheets("Kulanzblatt-VK").Range("H3").Value = "X" Then CheckBox2.Value = True
'------------ Vorführwagen ob Kreuz drin ist --------------------------
If Worksheets("Kulanzblatt-VK").Range("L3").Value = "X" Then CheckBox5.Value = True
If CheckBox5.Value = True Then Label22.Enabled = False 'Schrift Transport
If CheckBox5.Value = True Then CheckBox6.Enabled = False 'Rg Transport
If CheckBox5.Value = True Then CheckBox7.Enabled = False 'selbstabholung
If CheckBox9.Value = True Then Label22.Enabled = False 'Schrift Transport
If CheckBox9.Value = True Then CheckBox6.Enabled = False 'Rg Transport
If CheckBox9.Value = True Then CheckBox7.Enabled = False 'selbstabholung
'------------ Tageszulassung ob Kreuz drin ist --------------------------------
If Worksheets("Kulanzblatt-VK").Range("M17").Value = "Ja" Then
CheckBox9.Enabled = True 'True = einblenden
CheckBox9.Value = True
CheckBox2.Value = False
End If
'---------------- für Auftragsnummer Label7 -------------------------------------
If CheckBox2.Value = False And CheckBox5.Value = False And CheckBox9.Value = False Then
Label7.Enabled = False
Label7.BackColor = Me.BackColor
Label7.Enabled = False
Else
Label7.Enabled = True
End If
'---- wenn keinTransport, kein Selbsta.+ kein Transport Sonderp. X setzen ------
If Worksheets("Kulanzblatt-VK").Range("U38").Value = "X" Then
CheckBox6.Value = True
'Label43.Caption = Format(Worksheets("Kulanzblatt-VK").Range("G40").Value, ("#,##0.00"))
End If
If CheckBox6.Value = False Then
Worksheets("Kulanzblatt-VK").Range("U38").Value = ""
Label43.Visible = False
Label43.Enabled = False
End If
'------------ Fremdvermittlung ----------------------------------------
If CheckBox14.Value = False And CheckBox13.Value = False Then
Me.TextBox20.BackColor = Me.BackColor
TextBox20.Enabled = False
Else
If CheckBox14.Value = True Then
TextBox20.BackColor = vbWhite
TextBox20.Enabled = True
If CheckBox13.Value = True Then
TextBox20.BackColor = vbWhite
TextBox20.Enabled = True
End If
End If
End If
'------------ Fremdvermittlung EURO -------------------------------------
If Worksheets("Kulanzblatt-VK").Range("T55").Value > "0" Then
TextBox20.Value = Format(Worksheets("Kulanzblatt-VK").Range("T55").Value, ("0.00"))
CheckBox14.Value = True
CheckBox13.Value = False
TextBox20 = Format(Worksheets("Kulanzblatt-VK").Range("T55").Value, ("0.00"))
Worksheets("Kulanzblatt-VK").Range("T54").Value = "JA"
Worksheets("Kulanzblatt-VK").Range("U55").Value = "0,0"
Else
'------------ Fremdvermittlung % -----------------------------------------
If Worksheets("Kulanzblatt-VK").Range("U55") > "0" Then
TextBox20 = Format(Worksheets("Kulanzblatt-VK").Range("U55").Value, ("0.0"))
CheckBox13.Value = True
CheckBox14.Value = False
TextBox20 = Format(Worksheets("Kulanzblatt-VK").Range("U55").Value, ("0.0"))
Worksheets("Kulanzblatt-VK").Range("T54").Value = "JA"
Format (Worksheets("Kulanzblatt-VK").Range("T55").Text = ("0.00"))
End If
End If
If Worksheets("Kulanzblatt-VK").Range("U55") = "0" Then
Worksheets("Kulanzblatt-VK").Range("T54").Value = ""
If Worksheets("Kulanzblatt-VK").Range("T55") = "0" Then
Worksheets("Kulanzblatt-VK").Range("T54").Value = ""
CheckBox14.Value = False
CheckBox13.Value = False
End If
End If
'--------------- A S V Vertragswerkstatt auswählen -------------------------------
Sheets("Kulanzblatt-VK").Select
Sheets("Kulanzblatt-VK").Unprotect ("wwpa")
ComboBox8.RowSource = "D100:D140"
'' ComboBox8.ListIndex = 1 ' ersten Wert anzeigen '1.Zeile auswählen
ComboBox8.Value = Worksheets("Kulanzblatt-VK").Range("M20")
Worksheets("Kulanzblatt-VK").Range("M20") = ComboBox8.Value
'------------ A S V Vertragswerkstatt -----------------------------------------
If Worksheets("Kulanzblatt-VK").Range("U58").Value > "0" Then
CheckBox16.Value = True
TextBox21.BackColor = vbWhite
TextBox21.Enabled = True
ComboBox8.BackColor = vbWhite
ComboBox8.Enabled = True
TextBox21.Value = Format(Worksheets("Kulanzblatt-VK").Range("U58").Value, ("0.0"))
Else
If CheckBox16.Value = False Then
Me.TextBox21.BackColor = Me.BackColor
TextBox21.Enabled = False
Label40.Enabled = False
End If
End If
If CheckBox2.Value = False And CheckBox5.Value = False And CheckBox9.Value = False Then
CheckBox16.Value = False
CheckBox16.BackColor = Me.BackColor
CheckBox16.Enabled = False
End If
'------------ GF Bonus EURO -------------------------------------
If Worksheets("Kulanzblatt-VK").Range("T60").Value > "0" Then
TextBox26.Value = Format(Worksheets("Kulanzblatt-VK").Range("T60").Value, ("#,##0.00"))
CheckBox17.Value = True
TextBox26 = Format(Worksheets("Kulanzblatt-VK").Range("T60").Text, ("#,##0.00"))
Worksheets("Kulanzblatt-VK").Range("AS394").Value = "X"
TextBox26.BackColor = vbWhite
TextBox26.Enabled = True
Label39.Enabled = True
Else
If CheckBox17.Value = False Then
Me.TextBox26.BackColor = Me.BackColor
TextBox26.Enabled = False
Label39.Enabled = False
End If
End If
If Worksheets("Kulanzblatt-VK").Range("T60").Value <= "0" Then
CheckBox17.Value = False
End If
'--------------------- wenn GF eingetauscht wird ----------------------------------
If Worksheets("Kulanzblatt-VK").Range("U24").Value = "J" Then CheckBox25.Value = True
If Worksheets("Kulanzblatt-VK").Range("U24").Value = "N" Then CheckBox25.Value = False
'-------------------- Bar Scheck Finanzierung Leasing --------------------------
If CheckBox21.Value = False And CheckBox22.Value = False And CheckBox23.Value = False And CheckBox24.Value = False Then
Me.TextBox29.BackColor = Me.BackColor
TextBox29.Enabled = False
End If
If Worksheets("Kulanzblatt-VK").Range("BE388").Value = "X" Then 'Bar
CheckBox21.Value = True
CheckBox22.Value = False
CheckBox23.Value = False
CheckBox24.Value = False
Me.TextBox29.BackColor = Me.BackColor
TextBox29.Enabled = False
End If
If Worksheets("Kulanzblatt-VK").Range("AZ388").Value = "X" Then 'Scheck
CheckBox22.Value = True
CheckBox21.Value = False
CheckBox23.Value = False
CheckBox24.Value = False
Me.TextBox29.BackColor = Me.BackColor
TextBox29.Enabled = False
End If
If Worksheets("Kulanzblatt-VK").Range("AS388").Value = "X" Then 'Finanzierung
CheckBox23.Value = True
CheckBox21.Value = False
CheckBox22.Value = False
CheckBox24.Value = False
TextBox29.BackColor = vbWhite
TextBox29.Enabled = True
TextBox29 = Worksheets("Kulanzblatt-VK").Range("AS390").Value
End If
If Worksheets("Kulanzblatt-VK").Range("AV388").Value = "X" Then 'Leasing
CheckBox24.Value = True
CheckBox21.Value = False
CheckBox22.Value = False
CheckBox23.Value = False
TextBox29.BackColor = vbWhite
TextBox29.Enabled = True
TextBox29 = Worksheets("Kulanzblatt-VK").Range("AS390").Value
End If
If ThisWorkbook.Worksheets("Kulanzblatt-VK").Range("F33") = "Autovermieter" Then
CheckBox26.Value = True
Else
CheckBox26.Value = False
End If
If ThisWorkbook.Worksheets("Kulanzblatt-VK").Range("F33") = "Fahrschule" Then
CheckBox27.Value = True
Else
CheckBox27.Value = False
End If
If ThisWorkbook.Worksheets("Kulanzblatt-VK").Range("F33") = "Journalist" Then
CheckBox12.Value = True
Else
CheckBox12.Value = False
End If
'------------------ Vorsteuer Ja dann Haken ------------------------------------
If Worksheets("Kulanzblatt-VK").Range("M7").Value = "JA" Then CheckBox11.Value = True
If Worksheets("Kulanzblatt-VK").Range("M7").Value = "" Then CheckBox11.Value = False
'------------------ Journalist Ja dann Haken ------------------------------------
If Worksheets("Kulanzblatt-VK").Range("T29").Value = "JA" Then CheckBox12.Value = True
If Worksheets("Kulanzblatt-VK").Range("T29").Value = "" Then CheckBox12.Value = False
'------------ Selbstabholung ob Kreuz drin ist --------------------------
If Worksheets("Kulanzblatt-VK").Range("Q3").Value = "X" Then CheckBox7.Value = True
If Worksheets("Kulanzblatt-VK").Range("Q3").Value = "X" Then CheckBox6.Value = False
If Worksheets("Kulanzblatt-VK").Range("Q3").Value = "" Then CheckBox7.Value = False
'------------------- Postanschrift --------------------------------------------
Sheets("Kulanzblatt-VK").Select
If Worksheets("Kulanzblatt-VK").Range("AR366").Value = "" Then 'Text drin
Me.TextBox23.BackColor = Me.BackColor
TextBox23.Enabled = False
Else
TextBox23.Value = Worksheets("Kulanzblatt-VK").Range("AR366").Value
CheckBox18.Value = True
TextBox23.Value = Worksheets("Kulanzblatt-VK").Range("AR366").Value
TextBox23.BackColor = vbWhite
TextBox23.Enabled = True
End If
'------------------- Kennzeichen -----------------------------------------------
Sheets("Kulanzblatt-VK").Select
If Worksheets("Kulanzblatt-VK").Range("AU370").Value = "" Then 'Kennzeichen
Me.TextBox27.BackColor = Me.BackColor
TextBox27.Enabled = False
Else
TextBox27.Value = Worksheets("Kulanzblatt-VK").Range("AU370").Value
CheckBox19.Value = True
TextBox27.Value = Worksheets("Kulanzblatt-VK").Range("AU370").Value
TextBox27.BackColor = vbWhite
TextBox27.Enabled = True
End If
'------------------- Sonstiger Text --------------------------------------------
Sheets("Kulanzblatt-VK").Select
If Worksheets("Kulanzblatt-VK").Range("AU398").Value = "" Then 'Text Sonstiger
Me.TextBox28.BackColor = Me.BackColor
TextBox28.Enabled = False
Else
TextBox28.Value = Worksheets("Kulanzblatt-VK").Range("AU398").Value 'Text Sonstiger
CheckBox20.Value = True
TextBox28.Value = Worksheets("Kulanzblatt-VK").Range("AU398").Value 'Text Sonstiger
TextBox28.BackColor = vbWhite
TextBox28.Enabled = True
End If
Set wsDatabase = Worksheets("Datenbank")
For intY = 2 To 1000 ' freie Zelle in Datenbankblatt finden
If wsDatabase.Cells(intY, 11) = "" Then ''stand (intY, 3)' wenn freie Zelle gefunden
Exit For ' geh aus der Schleife raus
Else: ReDim Preserve aVarData(intY - 1) ' Felddimension um eins erhöhen
aVarData(intY - 1) = wsDatabase.Cells(intY, 11).Value ' Feld füllen
End If
Next
aVarData(0) = " " ' setze nicht gefülltes Feld auf " "
' Feld sortieren
For intLast = 0 To UBound(aVarData) ' gehe alle Felder durch
For intNext = intLast + 1 To UBound(aVarData)
If aVarData(intLast) > aVarData(intNext) Then
VarTmp = aVarData(intLast) ' umsortieren
aVarData(intLast) = aVarData(intNext)
aVarData(intNext) = VarTmp
End If
Next intNext
Next intLast
ComboBox5.List = aVarData() ' Dropdown das Feld zuweisen
ComboBox3.SetFocus
With ComboBox3
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
If Worksheets("Kulanzblatt-VK").Range("H3").Value = "" And _
Worksheets("Kulanzblatt-VK").Range("L3").Value = "" And _
Worksheets("Kulanzblatt-VK").Range("Q3").Value = "" Then
CheckBox2.Value = False
CheckBox5.Value = False
CheckBox9.Value = False
End If
If ComboBox6.ListIndex <= "0" Then
CheckBox2.Enabled = False
CheckBox5.Enabled = False
CheckBox9.Enabled = False
Label13.Caption = ""
End If
If ComboBox6.ListIndex > "0" And CheckBox2.Enabled = True Then
CheckBox5.Enabled = False
CheckBox9.Enabled = False
End If
If ComboBox6.ListIndex > "0" And CheckBox5.Enabled = True Then
CheckBox2.Enabled = False
CheckBox9.Enabled = False
End If
If ComboBox6.ListIndex > "0" And CheckBox9.Enabled = True Then
CheckBox2.Enabled = False
CheckBox5.Enabled = False
End If
Sheets("Prov-Blatt").Select
Range("A1").Select
Application.ScreenUpdating = True
End Sub
Hallo K.Rola, wenn Dir das zu unübersichtlich ist, werde ich halt Schritt für Schritt löschen, geht auch dauert nur länger !
Gruß Walter