und zwar trage ich in meiner Liste in eine Zelle ein Datum ein über eine Userform.
In meinem Fall ist es die TextBox6 in diesem Code:
Option Explicit
Dim Anz%, Zeile%, Data
Private Sub CommandButton1_Click()
Dim i%, found As Boolean
found = False
For i = 1 To Anz
If TextBox1.Value = Data(i, 5) Then
'If TextBox2.Value = Data(i, 2) Then
'If TextBox3.Value = Data(i, 3) Then
Zeile = i + 1
TextBox9.Value = "Gefunden in Zeile " & Zeile
ComboBox1.Value = Data(i, 13)
TextBox5.Value = Data(i, 16)
TextBox6.Value = Data(i, 18)
ComboBox2.Value = Data(i, 20)
ComboBox3.Value = Data(i, 21)
ComboBox4.Value = Data(i, 22)
ComboBox5.Value = Data(i, 23)
TextBox10.Value = Data(i, 24)
found = True
Exit For
End If
'End If
'End If
Next i
If found Then
ComboBox1.Enabled = True
TextBox5.Enabled = True
TextBox6.Enabled = True
ComboBox2.Enabled = True
ComboBox3.Enabled = True
ComboBox4.Enabled = True
ComboBox5.Enabled = True
TextBox10.Enabled = True
Range(Cells(Zeile, 5), Cells(Zeile, 24)).Select
Else
TextBox9.Value = "Es wurde keine Maschine gefunden"
End If
End Sub
Private Sub CommandButton2_Click()
If TextBox1.text = "" Then
MsgBox "Bitte erst eine Maschine suchen"
Me.TextBox1.SetFocus
Exit Sub
End If
Cells(Zeile, 5) = TextBox1.Value
'Cells(Zeile, 2) = TextBox2.Value
'Cells(Zeile, 3) = TextBox3.Value
Cells(Zeile, 13) = ComboBox1.Value
Cells(Zeile, 16) = TextBox5.Value
Cells(Zeile, 18) = TextBox6.Value
Cells(Zeile, 20) = ComboBox2.Value
Cells(Zeile, 21) = ComboBox3.Value
Cells(Zeile, 22) = ComboBox4.Value
Cells(Zeile, 23) = ComboBox5.Value
Cells(Zeile, 24) = TextBox10.Value
TextBox9.Value = "Werte gespeichert"
MsgBox "Maschinendaten geändert"
End Sub
Private Sub CommandButton3_Click()
Hide
End Sub
Private Sub CommandButton4_Click()
TextBox1.Value = ""
ComboBox1.Value = ""
ComboBox3.Value = ""
ComboBox4.Value = ""
ComboBox5.Value = ""
TextBox5.Value = ""
TextBox6.Value = ""
ComboBox2.Value = ""
TextBox10.Value = ""
TextBox9.Value = "Bitte Seriennummer eingeben"
TextBox5.Enabled = False
TextBox6.Enabled = False
ComboBox1.Enabled = False
ComboBox2.Enabled = False
ComboBox3.Enabled = False
ComboBox4.Enabled = False
ComboBox5.Enabled = False
TextBox10.Enabled = False
Cells(1, 1).Select
End Sub
Private Sub UserForm_Initialize()
Anz = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row - 1
Data = Range(Cells(2, 1), Cells(Anz + 1, 24))
TextBox9.Value = "Bitte Seriennummer eingeben"
ComboBox1.Enabled = False
TextBox5.Enabled = False
TextBox6.Enabled = False
ComboBox2.Enabled = False
ComboBox3.Enabled = False
ComboBox4.Enabled = False
ComboBox5.Enabled = False
TextBox10.Enabled = False
'Zentrieren auf beiden Bildschirmen des Fenstern
Dim sngTop As Single, sngLeft As Single
Me.StartUpPosition = 0
sngLeft = Application.Left + Application.Width / 2 - Me.Width / 2
sngTop = Application.Top + Application.Height / 2 - Me.Height / 2
Me.Left = sngLeft
Me.Top = sngTop
Label13 = Date
Label14 = Time
End Sub
'###Keine Eingaben möglich ohne gültigem Datum###
'
Private Sub TextBox6_Exit(ByVal Cancel As MSForms.ReturnBoolean)
'If IsDate(TextBox6.Text) Then
' TextBox6.Text = Format(TextBox1.Text, "DD.MM.YYYY")
'Else
' TextBox6.Text = "Kein gültiges Datum!"
' Cancel = True
'End If
'End Sub
In einer weiteren Form suche ich nach dem Datum um mir die Zeilen anzeigen zu lassen in der sich das Datum befindet. Hier der Code dazu:
Option Explicit
Private Sub cmdSuchen_Click()
Dim lngRow As Long, lngLast As Long
If IsDate(TextBox1) Then
With Sheets("Maschinenliste")
lngLast = Application.Max(19, .Cells(.Rows.Count, 1).End(xlUp).Row)
ListBox1.Clear
For lngRow = 19 To lngLast
If .Cells(lngRow, 18).Value = CLng(CDate(TextBox1)) Then
ListBox1.AddItem Cells(lngRow, 1).text
ListBox1.Column(0, ListBox1.ListCount - 1) = .Cells(lngRow, 3).text
ListBox1.Column(1, ListBox1.ListCount - 1) = .Cells(lngRow, 4).text
ListBox1.Column(2, ListBox1.ListCount - 1) = .Cells(lngRow, 5).text
ListBox1.Column(3, ListBox1.ListCount - 1) = .Cells(lngRow, 24).text
ListBox1.Column(4, ListBox1.ListCount - 1) = .Cells(lngRow, 13).Value
End If
Next
End With
Else
MsgBox "Kein gültiges Datum!"
End If
End Sub
Private Sub CommandButton2_Click()
'Erstellen der Email für Outlook
Dim objOutlook As Object, objMail As Object
Dim lngIndex As Long, HTML As String, bolStripe As Boolean, strColor As String
With ListBox1
If .ListCount > 0 Then
HTML = _
"
"
HTML = HTML & _
"Typ:
_
/td>"
HTML = HTML & " Größe: "
HTML = HTML & "Seriennummer: "
HTML = HTML & "Bemerkung: "
For lngIndex = 0 To .ListCount - 1
Select Case .List(lngIndex, 4)
Case "0": strColor = "red"
Case "1": strColor = "#e6e600"
Case "2": strColor = "green"
Case Else: strColor = "#000000"
End Select
HTML = HTML & "" & .List(lngIndex, 0) & " "
HTML = HTML & "" & .List(lngIndex, 1) & " "
HTML = HTML & "" & .List(lngIndex, 2) & " "
HTML = HTML & "" & .List(lngIndex, 3) & " "
bolStripe = Not bolStripe
Next
HTML = HTML & "
"
Else
MsgBox "Keine Maschinendaten vorhanden!"
End If
End With
If Len(HTML) Then
Set objOutlook = CreateObject("Outlook.Application")
Set objMail = objOutlook.CreateItem(0)
With objMail
.To = ""
.CC = ""
.BCC = ""
.Subject = "Ready Maschinen"
.HTMLBody = "Hallo zusammen,
Maschinen in grün sind Ready
_
/i> und in Rot gesperrt:
" & HTML & "
Grüße " & " _
" & Application.UserName
.Display 'Erstellt die Email und öffnet diese. Der Versand erfolgt anschließend _
manuell vom User!
End With
End If
Set objOutlook = Nothing
Set objMail = Nothing
End Sub
Private Sub CommandButton3_Click()
TextBox1.Value = CStr(Date)
ListBox1.Clear
End Sub
Private Sub ListBox1_Click()
End Sub
'Zentrieren auf beiden Bildschirmen des Fenstern
Private Sub UserForm_Initialize()
Dim sngTop As Single, sngLeft As Single
Me.StartUpPosition = 0
sngLeft = Application.Left + Application.Width / 2 - Me.Width / 2
sngTop = Application.Top + Application.Height / 2 - Me.Height / 2
Me.Left = sngLeft
Me.Top = sngTop
Label13 = Date
Label28 = Time
'Spaltenbreite festlegen
ListBox1.ColumnCount = 5
ListBox1.ColumnWidths = "30 Pt;60 Pt;100 Pt;100 Pt;0 Pt"
TextBox1 = CStr(Date)
End Sub
Mein Problem hierbei ist das er das Datum nicht findet. Sprich er zeigt mir keine Zeilen an. Wenn ich jedoch einmal doppelklick mache auf das eingetragene Datum in der Excel und wieder weg klicke finde ich dieses.
Was läuft hier falsch?