AW: Codezeile 42 der Userform ist falsch! (owT)
17.07.2020 17:44:29
Christian
Hallo Uwe,
die Datei hochladen geht leider nicht, da es etwas dienstliches ist. Der Code des Subs der zum Laden der UserForm1 angeschubst wird lautet wie folgt - ich habe den wegen der Länge zuvor nicht gepostet. Im Wesentlichen werden dort die Textboxen usw. formatiert und positioniert:
Sub UserForm1_laden()
'LÄDT DIE VORAB GESETZTEN VARIABLEN
Call Variablen
'SUBVARIABLEN DEKLARIEREN
Dim SpalteRowSource As String
Dim Zeile1RowSource As String
Dim Zeile2RowSource As String
'HIER WIRD DIE USERFORM KONFIGURIERT
'USERFORM1
'KOORDINATEN
'HEIGHT FEHLT NOCH!!!!!!!!!!!!!!!!!!!
UserForm1.Top = 5
UserForm1.Width = HEADERSpalte1 + PAGE1Spalte1 + PAGE1Spalte4 + 130 + PAGE1Spalte1 + _
Zwischenspalte
UserForm1.Left = Application.Left + Application.Width / 2 - UserForm1.Width / 2
UserForm1.ScrollBars = fmScrollBarsNone
UserForm1.KeepScrollBarsVisible = fmScrollBarsNone
'HEADER
'KOORDINATEN
'SANTANDER LOGO
UserForm1.ImageSantanderLogo.Left = UserForm1.Width - HEADERSpalte1 - UserForm1. _
ImageSantanderLogo.Width
'HEADER
'KOORDINATEN
'ÜBERSCHRIFT "RORWA PRICING TOOL"
UserForm1.LabelRPT.Left = HEADERSpalte1
'HEADER
'KOORDINATEN
'AUSFÜLLHILFE
'TEXTFELD
UserForm1.LabelAusfüllhilfeView.Top = HEADERTopline
UserForm1.LabelAusfüllhilfeView.Left = HEADERSpalte1
UserForm1.LabelAusfüllhilfeView.Height = HöheEingabefeld + HöheEingabefeld
UserForm1.LabelAusfüllhilfeView.Width = UserForm1.Width - HEADERSpalte1 - _
HEADERSpalte1 - 1
'HEADER
'KOORDINATEN
'DATUM
'ÜBERSCHRIFT
UserForm1.LabelDatum.Top = UserForm1.LabelAusfüllhilfeView.Top + UserForm1. _
LabelAusfüllhilfeView.Height + Zeilenabstand + Zwischenzeile
UserForm1.LabelDatum.Left = HEADERSpalte1
UserForm1.LabelDatum.Height = HöheÜberschrift
If UserForm1.TextBoxDatum.TextLength = 0 Then
UserForm1.LabelDatum.ForeColor = vbRed
Else
UserForm1.LabelDatum.ForeColor = vbBlack
End If
'DATUMFELD
UserForm1.TextBoxDatum.Locked = True
UserForm1.TextBoxDatum.BackColor = vbWhite
UserForm1.TextBoxDatum.Top = UserForm1.LabelDatum.Top + UserForm1.LabelDatum.Height _
+ Zwischenzeile
UserForm1.TextBoxDatum.Left = HEADERSpalte1
UserForm1.TextBoxDatum.Height = HöheEingabefeld
'SPINFELD
UserForm1.SpinButtonDatum.Top = UserForm1.TextBoxDatum.Top - 2
UserForm1.SpinButtonDatum.Left = HEADERSpalte1 + UserForm1.TextBoxDatum.Width + 1
UserForm1.SpinButtonDatum.Height = UserForm1.TextBoxDatum.Height + 2
'HEADER
'KOORDINATEN
'CUSTOMER ID
'ÜBERSCHRIFT
UserForm1.LabelCustomerID.Top = UserForm1.LabelDatum.Top
UserForm1.LabelCustomerID.Left = HEADERSpalte2 + UserForm1.TextBoxJ0000.Width + _
Zwischenspalte
UserForm1.LabelCustomerID.Height = HöheÜberschrift
'J0000
UserForm1.TextBoxJ0000.Top = UserForm1.LabelCustomerID.Top + UserForm1. _
LabelCustomerID.Height + Zwischenzeile
UserForm1.TextBoxJ0000.Left = HEADERSpalte2
UserForm1.TextBoxJ0000.Height = HöheEingabefeld
'XXXXX EINGABE
If UserForm1.TextBoxDatum.Value = "" Then
UserForm1.TextBoxCustomerID.Locked = True
UserForm1.TextBoxCustomerID.ForeColor = &H80000004
UserForm1.TextBoxCustomerID.BackColor = &H80000004
Else
UserForm1.TextBoxCustomerID.Locked = False
UserForm1.TextBoxCustomerID.ForeColor = vbBlack
UserForm1.TextBoxCustomerID.BackColor = vbWhite
End If
If UserForm1.TextBoxCustomerID.Locked = True Then
UserForm1.LabelCustomerID.ForeColor = vbBlack
Else
If UserForm1.TextBoxCustomerID.TextLength = 0 Then
UserForm1.LabelCustomerID.ForeColor = vbRed
Else
UserForm1.LabelCustomerID.ForeColor = vbBlack
End If
End If
UserForm1.TextBoxCustomerID.Top = UserForm1.LabelCustomerID.Top + UserForm1. _
LabelCustomerID.Height + Zwischenzeile
UserForm1.TextBoxCustomerID.Left = HEADERSpalte2 + UserForm1.TextBoxJ0000.Width + _
Zwischenspalte
UserForm1.TextBoxCustomerID.Height = HöheEingabefeld
'HEADER
'KOORDINATEN
'BCB HUB
'ÜBERSCHRIFT
UserForm1.LabelBCB_Hub.Top = UserForm1.LabelDatum.Top
UserForm1.LabelBCB_Hub.Left = HEADERSpalte3 + 8
UserForm1.LabelBCB_Hub.Height = HöheÜberschrift
'EINGABE
UserForm1.ComboBoxBCB_Hub.Top = UserForm1.LabelBCB_Hub.Top + UserForm1.LabelBCB_Hub. _
Height + Zwischenzeile
UserForm1.ComboBoxBCB_Hub.Left = HEADERSpalte3
UserForm1.ComboBoxBCB_Hub.Height = HöheEingabefeld
'ROWSOURCE
With Sheets("Dropdowns")
SpalteRowSource = WorksheetFunction.HLookup(UserForm1.LabelBCB_Hub.Caption, .Range(" _
A10:ZZ100"), 2, False)
Zeile1RowSource = WorksheetFunction.HLookup(UserForm1.LabelBCB_Hub.Caption, .Range(" _
A10:ZZ100"), 3, False)
Zeile2RowSource = WorksheetFunction.HLookup(UserForm1.LabelBCB_Hub.Caption, .Range(" _
A10:ZZ100"), 4, False)
UserForm1.ComboBoxBCB_Hub.RowSource = "'" & .Name & "'!" & SpalteRowSource & _
Zeile1RowSource & ":" & SpalteRowSource & Zeile2RowSource
End With
'HEADER
'KOORDINATEN
'RM
'ÜBERSCHRIFT
UserForm1.LabelRM.Top = UserForm1.LabelDatum.Top
UserForm1.LabelRM.Left = HEADERSpalte4 + 8
UserForm1.LabelRM.Height = HöheÜberschrift
'EINGABE
UserForm1.TextBoxRM.Top = UserForm1.LabelRM.Top + UserForm1.LabelRM.Height + _
Zwischenzeile
UserForm1.TextBoxRM.Left = HEADERSpalte4
UserForm1.TextBoxRM.Height = HöheEingabefeld
'HEADER
'KOORDINATEN
'RATING
'ÜBERSCHRIFT
UserForm1.LabelRating.Top = UserForm1.LabelDatum.Top
UserForm1.LabelRating.Left = HEADERSpalte5
UserForm1.LabelRating.Height = HöheÜberschrift
'EINGABE RATING
UserForm1.TextBoxRating.Locked = True
UserForm1.TextBoxRating.BackColor = &H80000004
UserForm1.TextBoxRating.Top = UserForm1.LabelRating.Top + UserForm1.LabelRating. _
Height + Zwischenzeile
UserForm1.TextBoxRating.Left = HEADERSpalte5
UserForm1.TextBoxRating.Height = HöheEingabefeld
'SPINFELD
UserForm1.SpinButtonRating.Top = UserForm1.TextBoxRating.Top - 2
UserForm1.SpinButtonRating.Left = HEADERSpalte5 + UserForm1.TextBoxRating.Width + 1
UserForm1.SpinButtonRating.Height = UserForm1.TextBoxRating.Height + 2
'HEADER
'KOORDINATEN
'COMPANY
'ÜBERSCHRIFT
UserForm1.LabelCompany.Top = UserForm1.LabelDatum.Top + Absatz
UserForm1.LabelCompany.Left = HEADERSpalte1 + 8
UserForm1.LabelCompany.Height = HöheÜberschrift
'EINGABE
UserForm1.TextBoxCompany.Top = UserForm1.LabelCompany.Top + UserForm1.LabelCompany. _
Height + Zwischenzeile
UserForm1.TextBoxCompany.Left = HEADERSpalte1
UserForm1.TextBoxCompany.Height = HöheEingabefeld
UserForm1.TextBoxCompany.Width = UserForm1.LabelAusfüllhilfeView.Width
'HEADER
'KOORDINATEN
'NEW CUSTOMER
'CHECKBOX
UserForm1.CheckBoxNewCustomer.Top = UserForm1.LabelCompany.Top
UserForm1.CheckBoxNewCustomer.Left = UserForm1.TextBoxCompany.Left + UserForm1. _
TextBoxCompany.Width - UserForm1.CheckBoxNewCustomer.Width
'ÜBERSCHRIFT
UserForm1.LabelNewCustomer.Top = UserForm1.CheckBoxNewCustomer.Top
UserForm1.LabelNewCustomer.Left = UserForm1.CheckBoxNewCustomer.Left - UserForm1. _
LabelNewCustomer.Width - 1
UserForm1.LabelNewCustomer.Height = HöheÜberschrift
'MULTIPAGE
'KOORDINATEN
'HEIGHT FEHLT NOCH!!!!!!!!!!!!!!!!!!!
UserForm1.MultiPage1.Top = UserForm1.TextBoxCompany.Top + UserForm1.TextBoxCompany. _
Height + Zeilenabstand + Zeilenabstand
UserForm1.MultiPage1.Width = UserForm1.Width - HEADERSpalte1 - HEADERSpalte1
UserForm1.MultiPage1.Left = HEADERSpalte1
'HEADER
'STARTFORMATIERUNG
If UserForm1.TextBoxDatum.TextLength = 0 Or UserForm1.TextBoxCustomerID.TextLength = 0 _
Then
UserForm1.LabelAusfüllhilfeView.BackColor = &H80000004
UserForm1.TextBoxJ0000.Locked = True
UserForm1.TextBoxJ0000.BackColor = &H80000004
UserForm1.ComboBoxBCB_Hub.Locked = True
UserForm1.ComboBoxBCB_Hub.ForeColor = &H80000004
UserForm1.ComboBoxBCB_Hub.BackColor = &H80000004
UserForm1.TextBoxRM.Locked = True
UserForm1.TextBoxRM.ForeColor = &H80000004
UserForm1.TextBoxRM.BackColor = &H80000004
UserForm1.TextBoxRating.Locked = True
UserForm1.SpinButtonRating.Enabled = False
UserForm1.TextBoxRating.ForeColor = &H80000004
UserForm1.TextBoxRating.BackColor = &H80000004
UserForm1.TextBoxCompany.Locked = True
UserForm1.TextBoxCompany.ForeColor = &H80000004
UserForm1.TextBoxCompany.BackColor = &H80000004
UserForm1.CheckBoxNewCustomer.Locked = True
UserForm1.CheckBoxNewCustomer.SpecialEffect = fmButtonEffectFlat
Else
UserForm1.LabelAusfüllhilfeView.BackColor = &H80000004
UserForm1.TextBoxJ0000.Locked = True
UserForm1.TextBoxJ0000.BackColor = &H80000004
UserForm1.ComboBoxBCB_Hub.Locked = False
UserForm1.ComboBoxBCB_Hub.ForeColor = vbBlack
UserForm1.ComboBoxBCB_Hub.BackColor = vbWhite
UserForm1.TextBoxRM.Locked = False
UserForm1.TextBoxRM.ForeColor = vbBlack
UserForm1.TextBoxRM.BackColor = vbWhite
UserForm1.TextBoxRating.Locked = True
UserForm1.SpinButtonRating.Enabled = True
UserForm1.TextBoxRating.ForeColor = vbBlack
UserForm1.TextBoxRating.BackColor = vbWhite
UserForm1.TextBoxCompany.Locked = False
UserForm1.TextBoxCompany.ForeColor = vbBlack
UserForm1.TextBoxCompany.BackColor = vbWhite
UserForm1.CheckBoxNewCustomer.Locked = False
UserForm1.CheckBoxNewCustomer.SpecialEffect = fmButtonEffectSunken
End If
'USERFORM1
'ALLE FELDER VON PAGE1 AUSBLENDEN
Call ALLAWAY_PAGE1
'USERFORM1
'PAGE1 AUSBLENDEN
UserForm1.MultiPage1.Pages(1).VISIBLE = False
'USERFORM1
'PAGE "INFORMATIONEN" ANSTEUERN
UserForm1.MultiPage1.Value = 0
'USERFORM1
'OVERVIEW LADEN
Call OVERVIEW_LADEN
UserForm1.Show vbModeless
End Sub