Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forms - Verify date format
Hi All,
I have a form to input staff details and l am having trouble verifying the date format. The staff details exist on 1 Excel sheet in a "database" and when the form is lauched the existing details are displayed in the form. The DOB column is formatted as dd/mm/yyyy as is the short date in the systems regional settings. The code below is a snippet of the full code but is flawed in that: 1) As it is it crashes on the IF, OR, Then statement for cmbHREnter_Click 2) If l dont declare the variable type it runs fine but if the user overwrites the date it is written to Excel in the incorrect format Can anybody please tell me where l am going wrong as l have spent hours trying to correct this problem. All comments gratefully received. Dim DOB As Date Dim Nino As String Dim Surname As String Private Sub UserForm_Initialize tbDOB.Text = ActiveCell.Offset(0, 3).Value End Sub Private Sub cmbHREnter_Click() DOB = tbDOB.Value If Nino = "" Or _ Surname = "" Or _ DOB = "" Or _ Then MsgBox ("One of the compulsory fields is blank. Please re-enter") Else ActiveCell.Offset(0, -1).Value = Nino ActiveCell.Value = Surname ActiveCell.Offset(0, 3).Value = DOB End If End Sub Regards Michael beckinsale |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forms - Verify date format
Dim DOB As Date
Dim Nino As String Dim Surname As String Private Sub UserForm_Initialize tbDOB.Text = ActiveCell.Offset(0, 3).Text End Sub Private Sub cmbHREnter_Click() if isdate(tbDob.Value) then DOB = cdate(tbDOB.Value) else DOB = 0 end if If Nino = "" Or _ Surname = "" Or _ DOB = 0 Or _ Then MsgBox ("One of the compulsory fields is blank. Please re-enter") Else ActiveCell.Offset(0, -1).Value = Nino ActiveCell.Value = Surname ActiveCell.Offset(0, 3).Value = DOB End If End Sub -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Hi All, I have a form to input staff details and l am having trouble verifying the date format. The staff details exist on 1 Excel sheet in a "database" and when the form is lauched the existing details are displayed in the form. The DOB column is formatted as dd/mm/yyyy as is the short date in the systems regional settings. The code below is a snippet of the full code but is flawed in that: 1) As it is it crashes on the IF, OR, Then statement for cmbHREnter_Click 2) If l dont declare the variable type it runs fine but if the user overwrites the date it is written to Excel in the incorrect format Can anybody please tell me where l am going wrong as l have spent hours trying to correct this problem. All comments gratefully received. Dim DOB As Date Dim Nino As String Dim Surname As String Private Sub UserForm_Initialize tbDOB.Text = ActiveCell.Offset(0, 3).Value End Sub Private Sub cmbHREnter_Click() DOB = tbDOB.Value If Nino = "" Or _ Surname = "" Or _ DOB = "" Or _ Then MsgBox ("One of the compulsory fields is blank. Please re-enter") Else ActiveCell.Offset(0, -1).Value = Nino ActiveCell.Value = Surname ActiveCell.Offset(0, 3).Value = DOB End If End Sub Regards Michael beckinsale |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forms - Verify date format
Michael:
I think that it is because you are testing DOB as a string and not a date. DOB = "" Or _ Try testing the value in the cell ie: len(tbDOB.Value) = 0 ' no text in the box. -- HTHs Martin "Michael Beckinsale" wrote: Hi All, I have a form to input staff details and l am having trouble verifying the date format. The staff details exist on 1 Excel sheet in a "database" and when the form is lauched the existing details are displayed in the form. The DOB column is formatted as dd/mm/yyyy as is the short date in the systems regional settings. The code below is a snippet of the full code but is flawed in that: 1) As it is it crashes on the IF, OR, Then statement for cmbHREnter_Click 2) If l dont declare the variable type it runs fine but if the user overwrites the date it is written to Excel in the incorrect format Can anybody please tell me where l am going wrong as l have spent hours trying to correct this problem. All comments gratefully received. Dim DOB As Date Dim Nino As String Dim Surname As String Private Sub UserForm_Initialize tbDOB.Text = ActiveCell.Offset(0, 3).Value End Sub Private Sub cmbHREnter_Click() DOB = tbDOB.Value If Nino = "" Or _ Surname = "" Or _ DOB = "" Or _ Then MsgBox ("One of the compulsory fields is blank. Please re-enter") Else ActiveCell.Offset(0, -1).Value = Nino ActiveCell.Value = Surname ActiveCell.Offset(0, 3).Value = DOB End If End Sub Regards Michael beckinsale |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forms - Verify date format
Tom,
Many thanks. That solved the problem of the IF, OR, THEN statement crashing however l still get the wrong date format. For instance if the original date displays in the Excel sheet as 08/09/1955 when the form launches it is in the same format. If the user then enters 11/12/1955 it is written to Excel as 12/11/1955. I have tried modifying the code using FORMAT ie the code now reads as follows: Dim DOB As Date Dim Nino As String Dim Surname As String Private Sub UserForm_Initialize tbDOB.Text = Format(ActiveCell.Offset(0, 3).Text, "dd/mm/yyyy") End Sub Private Sub cmbHREnter_Click() if isdate(tbDob.Value) then DOB = cdate(tbDOB.Value) else DOB = 0 end if If Nino = "" Or _ Surname = "" Or _ DOB = 0 Or _ Then MsgBox ("One of the compulsory fields is blank. Please re-enter") Else ActiveCell.Offset(0, -1).Value = Nino ActiveCell.Value = Surname ActiveCell.Offset(0, 3).Value = Format(DOB, "dd/mm/yyyy") End If End Sub Any ideas ? This is proving to be a right so and so...................... Regards Michael "Martin Fishlock" wrote in message ... Michael: I think that it is because you are testing DOB as a string and not a date. DOB = "" Or _ Try testing the value in the cell ie: len(tbDOB.Value) = 0 ' no text in the box. -- HTHs Martin "Michael Beckinsale" wrote: Hi All, I have a form to input staff details and l am having trouble verifying the date format. The staff details exist on 1 Excel sheet in a "database" and when the form is lauched the existing details are displayed in the form. The DOB column is formatted as dd/mm/yyyy as is the short date in the systems regional settings. The code below is a snippet of the full code but is flawed in that: 1) As it is it crashes on the IF, OR, Then statement for cmbHREnter_Click 2) If l dont declare the variable type it runs fine but if the user overwrites the date it is written to Excel in the incorrect format Can anybody please tell me where l am going wrong as l have spent hours trying to correct this problem. All comments gratefully received. Dim DOB As Date Dim Nino As String Dim Surname As String Private Sub UserForm_Initialize tbDOB.Text = ActiveCell.Offset(0, 3).Value End Sub Private Sub cmbHREnter_Click() DOB = tbDOB.Value If Nino = "" Or _ Surname = "" Or _ DOB = "" Or _ Then MsgBox ("One of the compulsory fields is blank. Please re-enter") Else ActiveCell.Offset(0, -1).Value = Nino ActiveCell.Value = Surname ActiveCell.Offset(0, 3).Value = DOB End If End Sub Regards Michael beckinsale |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forms - Verify date format
the reason I used cdate is that it understands the regional date setting.
when you use format and assign the cell the value as a string, you cancel that out and ask vba to interpret the date which it does using US Centric interpretation. If you use ActiveCell.Offset(0, 3).Value = DOB as I suggested, where DOB was set with DOB = cdate(tbDOB.Value) then it should work for you. I don't know the point of using format with ActiveCell.Offset(0,3).Text tbDOB.Text = Format(ActiveCell.Offset(0, 3).Text, "dd/mm/yyyy") Text would place the date as displayed in the cell. demo'd from the immediate window: Activecell.NumberFormat = "mmm dd, yyyy" ? activeCell.Text Jan 15, 2006 ? activeCell.Value 01/15/2006 if you insist on using format (perhaps you are changing the way it is displayed), then use tbDOB.Text = Format(ActiveCell.Offset(0, 3).Value, "dd/mm/yyyy") Avoid using strings to represent dates. Use a date variable or a date serial number. Use Cdate to convert a string to a date/date serial. Stephen Bullen has made his chapter on international issues available on the web: From one of his past posts There's much more about these types of issue in my 'International Issues' chapter of John Green's "Excel 2002 VBA Programmer's Reference", which can also be read online on my web site, at: http://www.oaltd.co.uk/ExcelProgRef/ch22/ -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, Many thanks. That solved the problem of the IF, OR, THEN statement crashing however l still get the wrong date format. For instance if the original date displays in the Excel sheet as 08/09/1955 when the form launches it is in the same format. If the user then enters 11/12/1955 it is written to Excel as 12/11/1955. I have tried modifying the code using FORMAT ie the code now reads as follows: Dim DOB As Date Dim Nino As String Dim Surname As String Private Sub UserForm_Initialize tbDOB.Text = Format(ActiveCell.Offset(0, 3).Text, "dd/mm/yyyy") End Sub Private Sub cmbHREnter_Click() if isdate(tbDob.Value) then DOB = cdate(tbDOB.Value) else DOB = 0 end if If Nino = "" Or _ Surname = "" Or _ DOB = 0 Or _ Then MsgBox ("One of the compulsory fields is blank. Please re-enter") Else ActiveCell.Offset(0, -1).Value = Nino ActiveCell.Value = Surname ActiveCell.Offset(0, 3).Value = Format(DOB, "dd/mm/yyyy") End If End Sub Any ideas ? This is proving to be a right so and so...................... Regards Michael "Martin Fishlock" wrote in message ... Michael: I think that it is because you are testing DOB as a string and not a date. DOB = "" Or _ Try testing the value in the cell ie: len(tbDOB.Value) = 0 ' no text in the box. -- HTHs Martin "Michael Beckinsale" wrote: Hi All, I have a form to input staff details and l am having trouble verifying the date format. The staff details exist on 1 Excel sheet in a "database" and when the form is lauched the existing details are displayed in the form. The DOB column is formatted as dd/mm/yyyy as is the short date in the systems regional settings. The code below is a snippet of the full code but is flawed in that: 1) As it is it crashes on the IF, OR, Then statement for cmbHREnter_Click 2) If l dont declare the variable type it runs fine but if the user overwrites the date it is written to Excel in the incorrect format Can anybody please tell me where l am going wrong as l have spent hours trying to correct this problem. All comments gratefully received. Dim DOB As Date Dim Nino As String Dim Surname As String Private Sub UserForm_Initialize tbDOB.Text = ActiveCell.Offset(0, 3).Value End Sub Private Sub cmbHREnter_Click() DOB = tbDOB.Value If Nino = "" Or _ Surname = "" Or _ DOB = "" Or _ Then MsgBox ("One of the compulsory fields is blank. Please re-enter") Else ActiveCell.Offset(0, -1).Value = Nino ActiveCell.Value = Surname ActiveCell.Offset(0, 3).Value = DOB End If End Sub Regards Michael beckinsale |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Forms - Verify date format
Tom,
Once again many thanks. Too much "give it a try" coding. Have taken out all coding that refers to FORMAT and now all appears OK. Now l need a beer........................ Regards Michael "Tom Ogilvy" wrote in message ... the reason I used cdate is that it understands the regional date setting. when you use format and assign the cell the value as a string, you cancel that out and ask vba to interpret the date which it does using US Centric interpretation. If you use ActiveCell.Offset(0, 3).Value = DOB as I suggested, where DOB was set with DOB = cdate(tbDOB.Value) then it should work for you. I don't know the point of using format with ActiveCell.Offset(0,3).Text tbDOB.Text = Format(ActiveCell.Offset(0, 3).Text, "dd/mm/yyyy") Text would place the date as displayed in the cell. demo'd from the immediate window: Activecell.NumberFormat = "mmm dd, yyyy" ? activeCell.Text Jan 15, 2006 ? activeCell.Value 01/15/2006 if you insist on using format (perhaps you are changing the way it is displayed), then use tbDOB.Text = Format(ActiveCell.Offset(0, 3).Value, "dd/mm/yyyy") Avoid using strings to represent dates. Use a date variable or a date serial number. Use Cdate to convert a string to a date/date serial. Stephen Bullen has made his chapter on international issues available on the web: From one of his past posts There's much more about these types of issue in my 'International Issues' chapter of John Green's "Excel 2002 VBA Programmer's Reference", which can also be read online on my web site, at: http://www.oaltd.co.uk/ExcelProgRef/ch22/ -- Regards, Tom Ogilvy "Michael Beckinsale" wrote in message ... Tom, Many thanks. That solved the problem of the IF, OR, THEN statement crashing however l still get the wrong date format. For instance if the original date displays in the Excel sheet as 08/09/1955 when the form launches it is in the same format. If the user then enters 11/12/1955 it is written to Excel as 12/11/1955. I have tried modifying the code using FORMAT ie the code now reads as follows: Dim DOB As Date Dim Nino As String Dim Surname As String Private Sub UserForm_Initialize tbDOB.Text = Format(ActiveCell.Offset(0, 3).Text, "dd/mm/yyyy") End Sub Private Sub cmbHREnter_Click() if isdate(tbDob.Value) then DOB = cdate(tbDOB.Value) else DOB = 0 end if If Nino = "" Or _ Surname = "" Or _ DOB = 0 Or _ Then MsgBox ("One of the compulsory fields is blank. Please re-enter") Else ActiveCell.Offset(0, -1).Value = Nino ActiveCell.Value = Surname ActiveCell.Offset(0, 3).Value = Format(DOB, "dd/mm/yyyy") End If End Sub Any ideas ? This is proving to be a right so and so...................... Regards Michael "Martin Fishlock" wrote in message ... Michael: I think that it is because you are testing DOB as a string and not a date. DOB = "" Or _ Try testing the value in the cell ie: len(tbDOB.Value) = 0 ' no text in the box. -- HTHs Martin "Michael Beckinsale" wrote: Hi All, I have a form to input staff details and l am having trouble verifying the date format. The staff details exist on 1 Excel sheet in a "database" and when the form is lauched the existing details are displayed in the form. The DOB column is formatted as dd/mm/yyyy as is the short date in the systems regional settings. The code below is a snippet of the full code but is flawed in that: 1) As it is it crashes on the IF, OR, Then statement for cmbHREnter_Click 2) If l dont declare the variable type it runs fine but if the user overwrites the date it is written to Excel in the incorrect format Can anybody please tell me where l am going wrong as l have spent hours trying to correct this problem. All comments gratefully received. Dim DOB As Date Dim Nino As String Dim Surname As String Private Sub UserForm_Initialize tbDOB.Text = ActiveCell.Offset(0, 3).Value End Sub Private Sub cmbHREnter_Click() DOB = tbDOB.Value If Nino = "" Or _ Surname = "" Or _ DOB = "" Or _ Then MsgBox ("One of the compulsory fields is blank. Please re-enter") Else ActiveCell.Offset(0, -1).Value = Nino ActiveCell.Value = Surname ActiveCell.Offset(0, 3).Value = DOB End If End Sub Regards Michael beckinsale |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I verify the password date of an excel workbook | Excel Discussion (Misc queries) | |||
Verify the format of the content of a textbox included in a frame | Excel Programming | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming | |||
Verify Data Format via VB | Excel Programming | |||
Change a date in text format xx.xx.20xx to a recognised date format | Excel Programming |