Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a Number and Date ...
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a Number and Date ...
I think your masjor problem is that text boxes returns text not numbers and
date. You need to convert the text ClientNum = ClientNum.text 'Enter Client # Text Box If ClientNum = "" Then End AppDate = AppDate.text 'Enter Appointment Date Text Box If AppDate = "" Then End format converts numbers to text, you want text to number. Why is ClientNum being set to a date???? from ClientNum = Format(startDate, "#") to ClientNum = val(ClientNum) from: AppDate = Format(stopDate, "mm/dd/yy") to: AppDate = datevalue(stopDate) " wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Julian date - find next highest date/number | Excel Worksheet Functions | |||
How to find the number of matched date? | Excel Discussion (Misc queries) | |||
Find number of unique entries within a date range | Excel Worksheet Functions | |||
Find the row number with a date in it | Excel Worksheet Functions | |||
How do I find total number workdays from a range of date | Excel Worksheet Functions |