Find a Number and Date - VBA

[mrmunka]

I have create this user form that employees use to enter appointments
we have made. Currently the form has fields to enter values, with drop
down list etc... The next and final part to this project (thank god)
is a second form the user will use to search for an appointment they
made and then enter the remaining fields with the results and notes.

The form goes like this...

[Enter Client #:] [Text Box] [Search Button]
[Enter Appointment Date:]

[Showed List Box]
[Closed List Box]

Etc...

Here is the code I started with...

Private Sub Search_Click() 'Search Button
On Error GoTo errorHandler
Dim ClientNum As String
Dim AppDate As String
Dim startRow As Integer
Dim stopRow As Integer
ClientNum = ClientNum.text 'Enter Client # Text Box
If ClientNum = "" Then End
AppDate = AppDate.text 'Enter Appointment Date Text Box
If AppDate = "" Then End
ClientNum = Format(startDate, "#")
AppDate = Format(stopDate, "mm/dd/yy")
startRow = Worksheets("Appointment
Log").Columns("A").Find(ClientNum, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("Appointment Log").Columns("Z").Find(AppDate,
_
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Appointment Log").Range("C" & startRow & ":B" &
stopRow).Copy _
Destination:=Worksheets("Report").Range("A1")
End
errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48
End Sub

What I would like to change and do is to have a hidden label show up
if the search results found the values or reverse if nothing was
found, instead of the MsgBox. If the values entered were found,
highlight the row in the work sheet and allow the user to fill in the
fields below the search fields. I plan on removing the Report
worksheet at the bottom of the code posted above.

Here is a picture of the second form that has been created.
http://picasaweb.google.com/mrmunka/Excel

I've beat my head like crazy trying to get this all to work and I'm
finally at the end and would like some assistance from thoughs of you
out there with much more experience than myself. I greatly appreciate
any help provided. Thanks in advance.

 

[Joel]

Here are a few of my comments

1) I did something similar redcently. I added a label to my userform and
then went to property window an set visible to false. I used this label
caption property to return information to my main return that did the
processing. The userform was private and this was the only method that I
found to return information. I always kept this text box invisible. Even if
it is invisible the caption property can be writen and read.

2) Does your find really work. Dates in worksheets are number they will not
compare with text strings. Using a Format to convert Startdate and Enddate
to a string and putting it in a FIND function to located a date in a
worksheet doesn't work!!! Get rid of the two Format statements. Dates in
spreadsheet are numbers. the may bew formated as 9/15/07 or Sep 7, 2007 put
they are really just a number. The number represents the number of days
from Jan 1, 1900. Fractional parts of the number represent part of a day. 8
hours is 8/24 = .3333.


It is better for days to do the comparision as follows
found = False
do
ClientNum = ClientNum.text 'Enter Client # Text Box
If ClientNum <> "" Then
AppDate = AppDate.text 'Enter Appointment Date Text Box
If AppDate <> "" Then
for each cell in Worksheets("Appointment Log").Columns("A")
if int(cell) = int(AppDate) then
Found = True
exit for
end if
next cell
else
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48
end if
else
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48
end if
loop while found = False

I have create this user form that employees use to enter appointments
we have made. Currently the form has fields to enter values, with drop
down list etc... The next and final part to this project (thank god)
is a second form the user will use to search for an appointment they
made and then enter the remaining fields with the results and notes.

The form goes like this...

[Enter Client #:] [Text Box] [Search Button]
[Enter Appointment Date:]

[Showed List Box]
[Closed List Box]

Etc...

Here is the code I started with...

Private Sub Search_Click() 'Search Button
On Error GoTo errorHandler
Dim ClientNum As String
Dim AppDate As String
Dim startRow As Integer
Dim stopRow As Integer
ClientNum = ClientNum.text 'Enter Client # Text Box
If ClientNum = "" Then End
AppDate = AppDate.text 'Enter Appointment Date Text Box
If AppDate = "" Then End
ClientNum = Format(startDate, "#")
AppDate = Format(stopDate, "mm/dd/yy")
startRow = Worksheets("Appointment
Log").Columns("A").Find(ClientNum, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("Appointment Log").Columns("Z").Find(AppDate,
_
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Appointment Log").Range("C" & startRow & ":B" &
stopRow).Copy _
Destination:=Worksheets("Report").Range("A1")
End
errorHandler:
MsgBox "There has been an error: " & Error() & Chr(13) _
& "Ending Sub.......Please try again", 48
End Sub

What I would like to change and do is to have a hidden label show up
if the search results found the values or reverse if nothing was
found, instead of the MsgBox. If the values entered were found,
highlight the row in the work sheet and allow the user to fill in the
fields below the search fields. I plan on removing the Report
worksheet at the bottom of the code posted above.

Here is a picture of the second form that has been created.
http://picasaweb.google.com/mrmunka/Excel

I've beat my head like crazy trying to get this all to work and I'm
finally at the end and would like some assistance from thoughs of you
out there with much more experience than myself. I greatly appreciate
any help provided. Thanks in advance.

 

[mrmunka]

Thank you for responding, I'll try your suggestions. Much appreciated!

 

[Joel]

There is one change if forgot

from
if int(cell) = int(AppDate) then
to
if int(cell) = int(datevalue(AppDate)) then

I made the mistake of comparing a string again a date-serial-number