Hi there i am struggling with this and most likely just cant see the woods
for the trees!

I am relatively new to VBA but getting on reasonably well. My problem hers
is that I have a user form that will, on selection of the relevant cell on
the worksheet (enddate) be shown. Its function is to validate the date
entered by the user and to enter this onto the worksheet or to leave the cell
blank. the following code is fired on enter being clicked.

Public Sub enter_Click()


Dim datecheck As Boolean
Dim yearend
Dim dated As Date
Dim response As Integer
Dim startdate As Boolean
Dim dates


startcheck = False
startdate = IsEmpty(Worksheets("panel form").range("startdate").Value)
If startdate = True Then
MsgBox Title:="No Start Date", prompt:="Please enter the Start Date
before entering an End Date", Buttons:=vbCritical
Me.Hide
Exit Sub
End If
answer = dateend.Value
datecheck = IsDate(answer)
If datecheck = False Then
If Not answer = "" Then
MsgBox Title:="date Error", prompt:=" You have not entered a
correct date, Please use dd/mm/yyyy or dd-mm-yyyy format", Buttons:
=vbCritical
dateend.Value = ""
dateend.SetFocus
Exit Sub
End If
End If

yearend = Worksheets("panelinformation").range("yearend").Va lue
If Not answer = "" Then
dates = answer
dated = DateValue(dates)
End If
If dated = yearend Then

response = MsgBox(Title:="Year End", prompt:="Your End date is beyond the
current Year End date. Do you wish to use this date?", Buttons:=vbYesNo +
vbCritical)
If response = 7 Then
dateend.Value = ""
Frame1.SetFocus
Exit Sub
End If

End If
If Not answer = "" Then
With Worksheets("panel Form")
.Unprotect
.range("enddate").Value = dated

.Protect
End With
Else
With Worksheets("panel Form")
.Unprotect
.range("enddate").Value = answer

.Protect
End With
End If
days
Me.Hide

End Sub


the above is included for info and works ok (improve if you see fit)

The following sub is what i am having problems with I want to calculate days
and weeks here but if the weeks exceed 52 then enddate will have to equal
yearend (which happens to be 31-3-06 at this time)

if enddate is left blank then I assume Yearend.

Public Sub days()

'calculate weeks and days
Dim enddate As Date
Dim yearend As Date
Dim startdate As Date
Dim weeks As Integer
Dim days As Integer
Dim check

check = Worksheets("panel form").range("enddate").Value ' before this line in
locals window "check" is empty and is type variant/empty
'now check has no value and is type is variant/variant(1 to 1, 1 to 5)


MsgBox check ' run time 13 type missmatch here?

yearend = Worksheets("panelinformation").range("yearend").Va lue
enddate = Worksheets("panel form").range("enddate").Value

startdate = (Worksheets("Panel form").range("startdate").Value)
days = (enddate - startdate)
weeks = Int((days / 7))

With Worksheets("panel form")

.Unprotect
.range("weeks").Value = weeks
.range("days").Value = days
.Protect

End With

I would appreciate any comments or help with this cos I have become blind to
the problem which is probably clear!

Thanks

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200603/1