Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a Number and Date - VBA
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 - VBA
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 " 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a Number and Date - VBA
Thank you for responding, I'll try your suggestions. Much appreciated!
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Find a Number and Date - VBA
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 " wrote: Thank you for responding, I'll try your suggestions. Much appreciated! |
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 a Number and Date ... | Excel Programming | |||
Find number of unique entries within a date range | Excel Worksheet Functions | |||
Find the row number with a date in it | Excel Worksheet Functions |