Add New Customer

[adam]

Hi Anyone,

I have created A userform in excel with the name add Add New Customer. The fileds in the form include as follows:
Customer ID
Name
Address
Date of Birth
Age
Sex
Comments

When I press the Tab button in the keyboard after writing the Value in Customer ID Row the tab moves to the OK button not the Address Row in the user form.
I want the form fields to be filled in the above manner when I press the tab.

I also want the age to appear in the age column or row of the form when I enter the Date of Birth in the form.

Here is the code Im using

Code: Select all

Option Explicit
Private Sub cmdCancel_Click()
    Unload Me
End Sub

Private Sub cmdClear_Click()
' Clear the form
    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        End If
    Next ctl
End Sub

Private Sub cmdOK_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("CustomerList")

'find  first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row

'check for a Customer ID
If Trim(Me.txtCustomerID.Value) = "" Then
  Me.txtCustomerID.SetFocus
  MsgBox "Please enter a Customer ID"
  Exit Sub
End If

    If Not IsDate(Me.txtDateofBirth.Value) Then
        MsgBox "The Date box must contain a date.", vbExclamation, "Add New Customer"
        Me.txtDateofBirth.SetFocus
        Exit Sub
    End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtCustomerID.Value
ws.Cells(iRow, 2).Value = Me.txtName.Value
ws.Cells(iRow, 3).Value = Me.txtAddress.Value
ws.Cells(iRow, 4).Value = Me.txtDateofBirth.Value
ws.Cells(iRow, 5).Value = Me.txtAge.Value
ws.Cells(iRow, 6).Value = Me.cboSex.Value
ws.Cells(iRow, 7).Value = Me.cboComments.Value

'clear the data
Me.txtCustomerID.Value = ""
Me.txtName.Value = ""
Me.txtAddress.Value = ""
Me.txtDateofBirth.Value = ""
Me.txtAge.Value = ""
Me.cboSex.Value = ""
Me.cboComments = ""
Me.txtCustomerID.SetFocus

End Sub

Private Sub Label6_Click()

End Sub

Private Sub txtCustomerID_Change()

End Sub

Private Sub UserForm_Initialize()
cboSex.List = Array("Male", "Female")
cboComments.List = Array("New", "Regular")
End Sub

[HansV]

Make sure that the userform is the active window in the Visual Basic Editor.
Select View | Tab Order...
You'll see a list of the controls on your userform
You can change the tab order of the controls by selecting a control name and using the Move Up and Move Down buttons.

x45.png

To calculate the age, you should create an AfterUpdate event procedure for the txtDateOfBirth text box:

Code: Select all

Private Sub txtDateOfBirth_AfterUpdate()
  Dim d1 As Date
  Dim d2 As Date
  Dim Age As Integer
  If Not IsDate(Me.txtDateofBirth.Value) Then
    MsgBox "The Date box must contain a date.", vbExclamation, "Add New Customer"
    Me.txtAge = ""
  Else
    d1 = CDate(Me.txtDateOfBirth)
    d2 = Date
    Age = Year(d2) - Year(d1)
    If Month(d2) < Month(d1) Or (Month(d2) = Month(d1) And Day(d2) < Day(d1)) Then
      Age = Age - 1
    End If
    Me.txtAge = Age
  End If
End Sub

[adam]

I have added a code to the form frmAddNewCustomer as follows

Code: Select all

Private Sub txtDateofBirth_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    frmAddNewCustomer.txtDateofBirth.Value = ""
    UserForm1.Show
End Sub

when the mouse is placed over the textbox "txtDateofBirth" a userform with calendar is displayed where the user can select day year and month from it in order to write the date of birth to the text box "txtDateofBirth".

But when the date of birth is written in this manner the textbox "txtage" does not get updated with the age.

But if the above code is removed and date of birth is written the age box gets updated with age.

What may be the reason for this?

[HansV]

I don't think the MouseMove event is suitable - each time the user moves the mouse over the text box, whether intentionally or by accident, the text box txtDateofBirth will be cleared.

[adam]

I don't think the MouseMove event is suitable - each time the user moves the mouse over the text box, whether intentionally or by accident, the text box txtDateofBirth will be cleared.

What you have said is true I guess. For that reason a change of mind is made. Here's the alternative

Code: Select all

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
 frmAddNewCustomer.txtDateofBirth.Value = ""
UserForm1.Show
End Sub

But I cannot make the following code work. What i'm trying to do is to change the textbox either to Male or Female on double click.

Code: Select all

Private Sub txtSex_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
frmAddNewCustomer.txtSex.Value = "Male"
frmAddNewCustomer.txtSex.Value = "Female"
End Sub

How can this be done?

[HansV]

Your code first sets the value to "Male" and then immediately to "Female". As a result, the value will always be "Female". You will have to use an If statement to check the current value, for example:

Code: Select all

Private Sub txtSex_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
  With Me.txtSex
    If .Value = "Male" Then
      .Value = "Female"
    Else
      .Value = "Male"
    End If
  End With
End Sub

But personally, I would prefer to use a combo box or radio buttons instead of a text box.

[adam]

But personally, I would prefer to use a combo box or radio buttons instead of a text box.

I would be happy if you could let me know the reason

[HansV]

If you use a combo box or radio buttons, users can't make mistakes: they can only select Male or Female.

x36.png

If you use a text box, they can enter an invalid value, for example "Frmale".

[adam]

But if Im not mistaken how could it happen when the code is already written in VBA as Male or Female. Do you mean if the user accidently writes Female in the text box as "frmale"?

[HansV]

If the user double-clicks the text box, the text box is filled by code. But the user can still type something in the text box.

[adam]

That's very true Hans. I have stopped my decision to change the text box event with double click. Instead I'll prefer to use the combo box. Thankyou for the valuable advice.

[adam]

How could I make the text that I write in the text box of the userform to get copied to the worksheet as I press the tab button after writing the value in the text box.

I tried the following code. But it does not seem to work.

Code: Select all

Private Sub txtCustomerID_Change()
Sheets("NewMemo").Range("D8") = Me.txtCustomerID.Text
End Sub

[HansV]

The Change event occurs as the user types; if you want the cell to be updated when the user presses Tab, you should use the AfterUpdate event instead of the Change event of the text box.

[adam]

I did try using

Code: Select all

Private Sub txtCustomerID_AfterUpdate()
Sheets("NewMemo").Range("D8") = Me.txtCustomerID.Text
End Sub

But am not able to succeed.

[adam]

Oh sorry Hans. It works with both the events. Thanks for the help.

[adam]

But when the date of birth is written in this manner the textbox "txtage" does not get updated with the age.

Code: Select all

=DATEDIF(A1,NOW(),"y") & " years, " & DATEDIF(A1,NOW(),"ym") & " months, " & DATEDIF(A1,NOW(),"md") & " days"

the above formula when written in column "B" works when the date is written in the worksheet column "A" as follows
12/25/1999

But it does not work if the date is written as follows
25/12/1999

How may I change it so that it works when the date is written as above

[HansV]

It depends on the date format set in the Regional and Language Options control panel. If the format is mm/dd/yyyy (as in the USA), 12/25/1999 will work but 25/12/1999 won't. If the format is dd/mm/yyyy (as in the UK), 25/12/1999 will work but 12/25/1999 won't.

So if you want 25/12/1999 to be accepted, you have to set the system date format to dd/mm/yyyy.

[adam]

Thanks for the help, Hans. It worked great. But with this formula the age seems to show up with year, month & days.
How the formula could be adjusted so that it displays the age in its units.
Say or example if the date of birth is 02/01/2010 I want the age to be 3 months.
If its 18/01/1983 I want the text that appears in the column "B" to be 27 years.
I hope I have made my question clear.

[HansV]

How about

=IF(DATEDIF(A1,NOW(),"y")>0,DATEDIF(A1,NOW(),"y") & " years",IF(DATEDIF(A1,NOW(),"m")>0,DATEDIF(A1,NOW(),"ym") & " months",DATEDIF(A1,NOW(),"md") & " days"))

[adam]

Thanks, Hans. That works awesome.