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

Based on what you say, the defined name Enddate is 1 row, five columns wide.

Change it to a single cell or use something like

check = Worksheets("panel form").range("enddate")(1).Value

which returns the value from the first cell in the defined name Enddate

--
Regards,
Tom Ogilvy



"pjbur2005 via OfficeKB.com" <u18722@uwe wrote in message
news:5cb7580a49e3c@uwe...
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

Tom

thank you for taking the trouble to reply, in the last four months I have
learnt a lot about VBA and excel. This site has proved to be an excelent
learning resource for me. I assure you I search for answers before asking
questions. i value the time you put in to looking at peoples questions and
helping to solve them. Thank you.

your suggestion worked spot on and I can see what i have been missing. My
worksheet works as an on screen form and has lots of ranges similar to
enddate ie 1 rowm many columns). when i run checks to determine if ranges
have been completed or not thers is no problem ecept where I have used a user
form which will put information infto that range. your solution will solve
that problem for me. Thanks again

Very best wishes Paul

Tom Ogilvy wrote:
Based on what you say, the defined name Enddate is 1 row, five columns wide.

Change it to a single cell or use something like

check = Worksheets("panel form").range("enddate")(1).Value

which returns the value from the first cell in the defined name Enddate

Hi there i am struggling with this and most likely just cant see the woods
for the trees!
[quoted text clipped - 116 lines]

Thanks

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