Calendar Control Changing System Date

[rob_parkhill]

I have added a calendar control to one of my forms, and now every time
that I select a date and execute the macro behind the form, the system
date changes. Has anyone else experienced this? Is there a
work-around?

I have added the Microsoft Calendar Control 11.0 in the references. m

I just have never experienced it modifying the system date before.

Thanks for any assistance.

Rob

[Ron de Bruin]

Hi Rob

Can we see the code ?


--
Regards Ron de Bruin
http://www.rondebruin.nl


"rob_parkhill" <[email protected]> wrote in message news:[email protected]...
>I have added a calendar control to one of my forms, and now every time
> that I select a date and execute the macro behind the form, the system
> date changes. Has anyone else experienced this? Is there a
> work-around?
>
> I have added the Microsoft Calendar Control 11.0 in the references. m
>
> I just have never experienced it modifying the system date before.
>
> Thanks for any assistance.
>
> Rob
>

[rob_parkhill]

Ron,

Here is a snippet. I have included the loop that I do checking the
Date. I have tried modifying the name (Date) to something else, seeing
as I think that is most likely the problem, but when I do that, it
doesn't match the dates in the column at all.

Private Sub Go_Click()
Application.ScreenUpdating = False

file = filename.Value
Date = DTPicker1.Value
samp = samples.Value

If file <> "" And Date <> "" And samp <> "" Then

reportval = CStr(file) & ".dbf"
Location = "C:\Documents and Settings\Classroom 1\Desktop\peas\" &
reportval
Original = ThisWorkbook.Name

firstsheet = ActiveSheet.Name

cspot = "A2"

Worksheets("Datasheet").Activate
Range("A2:BB30").Select
Selection.Clear
Range(cspot).Select
On Error GoTo err_handler
Workbooks.Open filename:=Location


startval = "E" & CStr(2)
Endval = "AF" & CStr(11)



Windows(reportval).Activate
Range("j2").Select
counter = 0
For Each x In ActiveCell.CurrentRegion.Cells
counter = counter + 1
spot = "J" & CStr(counter)

Range(spot).Select
If Selection.Value <> "" Then
If Selection.Value = Date Then
pos = counter
Exit For
End If
End If
Next x
If pos = "" Then
MsgBox "No Matching Dates"
End If
If pos <> "" Then
select1 = "E" & CStr(pos)
select2 = "AF" & CStr(samp + pos - 1)
Range(select1, select2).Select
End If

Thanks for any help
Rob

[Ron de Bruin]

Hi Rob

Sorry for the late response

You are correct that this will change your date
> Date = DTPicker1.Value

But I do not understand why you can't change it to
DateStr = Format(DTPicker1.Value,"mm/yy/dd")

You can use the same format as the cells in the column


--
Regards Ron de Bruin
http://www.rondebruin.nl


"rob_parkhill" <[email protected]> wrote in message news:[email protected]...
> Ron,
>
> Here is a snippet. I have included the loop that I do checking the
> Date. I have tried modifying the name (Date) to something else, seeing
> as I think that is most likely the problem, but when I do that, it
> doesn't match the dates in the column at all.
>
> Private Sub Go_Click()
> Application.ScreenUpdating = False
>
> file = filename.Value
> Date = DTPicker1.Value
> samp = samples.Value
>
> If file <> "" And Date <> "" And samp <> "" Then
>
> reportval = CStr(file) & ".dbf"
> Location = "C:\Documents and Settings\Classroom 1\Desktop\peas\" &
> reportval
> Original = ThisWorkbook.Name
>
> firstsheet = ActiveSheet.Name
>
> cspot = "A2"
>
> Worksheets("Datasheet").Activate
> Range("A2:BB30").Select
> Selection.Clear
> Range(cspot).Select
> On Error GoTo err_handler
> Workbooks.Open filename:=Location
>
>
> startval = "E" & CStr(2)
> Endval = "AF" & CStr(11)
>
>
>
> Windows(reportval).Activate
> Range("j2").Select
> counter = 0
> For Each x In ActiveCell.CurrentRegion.Cells
> counter = counter + 1
> spot = "J" & CStr(counter)
>
> Range(spot).Select
> If Selection.Value <> "" Then
> If Selection.Value = Date Then
> pos = counter
> Exit For
> End If
> End If
> Next x
> If pos = "" Then
> MsgBox "No Matching Dates"
> End If
> If pos <> "" Then
> select1 = "E" & CStr(pos)
> select2 = "AF" & CStr(samp + pos - 1)
> Range(select1, select2).Select
> End If
>
> Thanks for any help
> Rob
>

[Ron de Bruin]

You see in my other reply that my date is wrong<g>


--
Regards Ron de Bruin
http://www.rondebruin.nl


"rob_parkhill" <[email protected]> wrote in message news:[email protected]...
> Ron,
>
> Here is a snippet. I have included the loop that I do checking the
> Date. I have tried modifying the name (Date) to something else, seeing
> as I think that is most likely the problem, but when I do that, it
> doesn't match the dates in the column at all.
>
> Private Sub Go_Click()
> Application.ScreenUpdating = False
>
> file = filename.Value
> Date = DTPicker1.Value
> samp = samples.Value
>
> If file <> "" And Date <> "" And samp <> "" Then
>
> reportval = CStr(file) & ".dbf"
> Location = "C:\Documents and Settings\Classroom 1\Desktop\peas\" &
> reportval
> Original = ThisWorkbook.Name
>
> firstsheet = ActiveSheet.Name
>
> cspot = "A2"
>
> Worksheets("Datasheet").Activate
> Range("A2:BB30").Select
> Selection.Clear
> Range(cspot).Select
> On Error GoTo err_handler
> Workbooks.Open filename:=Location
>
>
> startval = "E" & CStr(2)
> Endval = "AF" & CStr(11)
>
>
>
> Windows(reportval).Activate
> Range("j2").Select
> counter = 0
> For Each x In ActiveCell.CurrentRegion.Cells
> counter = counter + 1
> spot = "J" & CStr(counter)
>
> Range(spot).Select
> If Selection.Value <> "" Then
> If Selection.Value = Date Then
> pos = counter
> Exit For
> End If
> End If
> Next x
> If pos = "" Then
> MsgBox "No Matching Dates"
> End If
> If pos <> "" Then
> select1 = "E" & CStr(pos)
> select2 = "AF" & CStr(samp + pos - 1)
> Range(select1, select2).Select
> End If
>
> Thanks for any help
> Rob
>