Hi

I have a Userform that enters data onto a "Members" sheet one of the
TextBoxes is for members DOBirth now as you all known some ladies are
reluctant to give out that sort of information and I tend to get round it by
entering 01/01/07 and the everything works ok, if I forget to enter the date
because the cell is empty on the sheet I get an error. the code below will
hopefully show why


Private Sub Lb1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Members")

rownum = Lb1.ListIndex
UserForm1.Tb30.Value = Lb1.List(, 0)
UserForm1.Tb31.Value = Lb1.List(, 1)
UserForm1.Tb32.Value = Lb1.List(, 2)
UserForm1.Tb33.Value = Lb1.List(, 3)
UserForm1.Tb34.Value = Lb1.List(, 4)
UserForm1.Tb35.Value = Lb1.List(, 5)
UserForm1.Tb26.Text = Lb1.List(, 6)
UserForm1.Tb27.Text = Lb1.List(, 7)
UserForm1.Tb28.Text = Lb1.List(, 8)
UserForm1.Tb29.Value = Lb1.List(, 9)
UserForm1.Tb30A.Value = Lb1.List(, 10)
UserForm1.Tb31A.Value = Lb1.List(, 11)
UserForm1.Tb32A.Value = Lb1.List(, 12)
UserForm1.Tb33A.Value = Lb1.List(, 13)
UserForm1.Tb34A.Value = Lb1.List(, 14)
UserForm1.Tb35A.Value = Lb1.List(, 15)

UserForm1.Tb8.Value = Year(Date) - Year(Tb26) <<<<<<< the error occurs here
because no date is in the cell - what this does is tell me how old the member
is and how much he / she should be charged for membership based on their age.
UserForm1.Tb10.Value = Year(Date) - Year(Tb27)

Tb26.Text = Format(Tb26, "dd/mm/yy")
Tb27.Text = Format(Tb27, "dd/mm/yy")
Tb28.Text = Format(Tb28, "dd/mm/yy")
End Sub

What would I need to add to keep the code running even if the cell was empty??
--
Many thanks

hazel

With UserForm1
If .Tb6.Value = "" Then
.Tb8.Text = ""
Else
.Tb8.Text = Year(Date) - Year(.Tb26.Text)
End If
End With

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Hazel" wrote in message
...
Hi

I have a Userform that enters data onto a "Members" sheet one of the
TextBoxes is for members DOBirth now as you all known some ladies are
reluctant to give out that sort of information and I tend to get round it
by
entering 01/01/07 and the everything works ok, if I forget to enter the
date
because the cell is empty on the sheet I get an error. the code below will
hopefully show why


Private Sub Lb1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Members")

rownum = Lb1.ListIndex
UserForm1.Tb30.Value = Lb1.List(, 0)
UserForm1.Tb31.Value = Lb1.List(, 1)
UserForm1.Tb32.Value = Lb1.List(, 2)
UserForm1.Tb33.Value = Lb1.List(, 3)
UserForm1.Tb34.Value = Lb1.List(, 4)
UserForm1.Tb35.Value = Lb1.List(, 5)
UserForm1.Tb26.Text = Lb1.List(, 6)
UserForm1.Tb27.Text = Lb1.List(, 7)
UserForm1.Tb28.Text = Lb1.List(, 8)
UserForm1.Tb29.Value = Lb1.List(, 9)
UserForm1.Tb30A.Value = Lb1.List(, 10)
UserForm1.Tb31A.Value = Lb1.List(, 11)
UserForm1.Tb32A.Value = Lb1.List(, 12)
UserForm1.Tb33A.Value = Lb1.List(, 13)
UserForm1.Tb34A.Value = Lb1.List(, 14)
UserForm1.Tb35A.Value = Lb1.List(, 15)

UserForm1.Tb8.Value = Year(Date) - Year(Tb26) <<<<<<< the error occurs
here
because no date is in the cell - what this does is tell me how old the
member
is and how much he / she should be charged for membership based on their
age.
UserForm1.Tb10.Value = Year(Date) - Year(Tb27)

Tb26.Text = Format(Tb26, "dd/mm/yy")
Tb27.Text = Format(Tb27, "dd/mm/yy")
Tb28.Text = Format(Tb28, "dd/mm/yy")
End Sub

What would I need to add to keep the code running even if the cell was
empty??
--
Many thanks

hazel

Hi Bob

What a super trooper you are -- all I can say is magnifico yet again.

Thanks and
--
Many thanks

hazel


"Bob Phillips" wrote:

With UserForm1
If .Tb6.Value = "" Then
.Tb8.Text = ""
Else
.Tb8.Text = Year(Date) - Year(.Tb26.Text)
End If
End With

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)



"Hazel" wrote in message
...
Hi

I have a Userform that enters data onto a "Members" sheet one of the
TextBoxes is for members DOBirth now as you all known some ladies are
reluctant to give out that sort of information and I tend to get round it
by
entering 01/01/07 and the everything works ok, if I forget to enter the
date
because the cell is empty on the sheet I get an error. the code below will
hopefully show why


Private Sub Lb1_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Members")

rownum = Lb1.ListIndex
UserForm1.Tb30.Value = Lb1.List(, 0)
UserForm1.Tb31.Value = Lb1.List(, 1)
UserForm1.Tb32.Value = Lb1.List(, 2)
UserForm1.Tb33.Value = Lb1.List(, 3)
UserForm1.Tb34.Value = Lb1.List(, 4)
UserForm1.Tb35.Value = Lb1.List(, 5)
UserForm1.Tb26.Text = Lb1.List(, 6)
UserForm1.Tb27.Text = Lb1.List(, 7)
UserForm1.Tb28.Text = Lb1.List(, 8)
UserForm1.Tb29.Value = Lb1.List(, 9)
UserForm1.Tb30A.Value = Lb1.List(, 10)
UserForm1.Tb31A.Value = Lb1.List(, 11)
UserForm1.Tb32A.Value = Lb1.List(, 12)
UserForm1.Tb33A.Value = Lb1.List(, 13)
UserForm1.Tb34A.Value = Lb1.List(, 14)
UserForm1.Tb35A.Value = Lb1.List(, 15)

UserForm1.Tb8.Value = Year(Date) - Year(Tb26) <<<<<<< the error occurs
here
because no date is in the cell - what this does is tell me how old the
member
is and how much he / she should be charged for membership based on their
age.
UserForm1.Tb10.Value = Year(Date) - Year(Tb27)

Tb26.Text = Format(Tb26, "dd/mm/yy")
Tb27.Text = Format(Tb27, "dd/mm/yy")
Tb28.Text = Format(Tb28, "dd/mm/yy")
End Sub

What would I need to add to keep the code running even if the cell was
empty??
--
Many thanks

hazel

You could enter a default value in the code if the user doesn't enter
anything...
or you could force them to enter a valid value by preventing the
procedure from continuing without a valid response...