i have the following code which is from my Search Expenses page on my userform, i have a search by date as well as the listbox. if i put a date in and press search it populates a set of boxes and the same from the listbox. my issue is this, when i edit the item which it allows me to day it throws up saying that the item can not be found yet its put the item in the edit section
im just wondering if there is a problem with my search date and save buttons, could someone help please
'*************************************************************
'* Start Expences input and edit section *
'*************************************************************
'this shows the selected item in listbox2 in to the "about the item" boxes for editing
Private Sub ListBox2_Change()
TextBox62.Value = Sheet5.Cells(ListBox2.ListIndex + 3, 1)
TextBox64.Value = Sheet5.Cells(ListBox2.ListIndex + 3, 2)
TextBox63.Value = Sheet5.Cells(ListBox2.ListIndex + 3, 3)
TextBox66.Value = Sheet5.Cells(ListBox2.ListIndex + 3, 4)
TextBox61.Value = Sheet5.Cells(ListBox2.ListIndex + 3, 5)
End Sub
Private Sub CmdSave_Click()
'Save criteria and returns new data to sheet
Dim sFindIt As String, lRowFnd As Long
Dim datFind As Date
With Range("A:A")
sFindIt = TextBox65.Value
On Error GoTo NoFind:
datFind = DateValue(sFindIt)
lRowFnd = .Cells.Find(What:=datFind, After:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart _
, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False).Row
On Error GoTo 0
Rows(lRowFnd).EntireRow.Select
.Cells(lRowFnd, 1).Value = TextBox62.Value
.Cells(lRowFnd, 2).Value = TextBox64.Value
.Cells(lRowFnd, 3).Value = TextBox63.Value
.Cells(lRowFnd, 4).Value = TextBox66.Value
.Cells(lRowFnd, 5).Value = TextBox61.Value
End With
Exit Sub
NoFind:
MsgBox "Your Selection - " & sFindIt & " Was Not Found", vbExclamation
'if no date entered then popup shows
If TextBox65.Value = "" Then
MsgBox "Please Enter A Date To Search"
Exit Sub
End If
End Sub
'deletes items from the listbox as well as the Expences Sheet by double clicking item in list
Private Sub ListBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If vbYes = MsgBox("Are you sure you want to delete this item...?", vbYesNo + vbQuestion + vbDefaultButton2, "Confirm") Then
With ListBox2
strRange = .RowSource
Range(strRange).Cells(.ListIndex + 1, 1).Delete Shift:=xlUp
.RowSource = vbNullString
.RowSource = strRange
End With
End If
End Sub
'searches date supplied for Expences
Private Sub SearchDate_Click()
Dim strFind As String
Dim found As Boolean
Application.ScreenUpdating = False
Sheets("Expences").Activate
Range("A3").Select
strFind = TextBox65.Value
found = False
Do Until IsEmpty(ActiveCell)
If ActiveCell.Value = strFind Then
found = True
Exit Do
End If
ActiveCell.Offset(1, 0).Select
Loop
If found = True Then
TextBox62.Value = ActiveCell.Value
TextBox64.Value = ActiveCell.Offset(, 1).Value
TextBox63.Value = ActiveCell.Offset(, 2).Value
TextBox66.Value = ActiveCell.Offset(, 3).Value
TextBox61.Value = ActiveCell.Offset(, 4).Value
Else
MsgBox "No More Entrys for this date"
End If
ActiveCell.Offset(1, 0).Select
Application.ScreenUpdating = True
End Sub
'*************************************************************
'* End Expences input and edit section *
'*************************************************************
Bookmarks