ActiveCell.Value changes with Format?

[Sige]

Hello There,

I have a cell (containing formula: =D1)
which is formatted as follows:

ddd dd-mmm-jj;;"<Double Click Me>"

Upon launching a userform with a calendar it picks up the cell's value
or else takes current day.
Eventhough D1 = empty, my calendar shows me always: 5 march 2002, which
is value =37320

Private Sub UserForm_Initialize()
' Check if active cell contains a date. If 'yes' show
' same date on calendar. If 'no' show today's date.
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub

Anyone to overcome this?
Cheers Sige

 

[Norman Jones]

Hi Sige,

I have been unable to replicate your experience.

To test, I set the userform to open modelessly and added your initialize
code to a button: in this way I could select any cell and test the calendar
response by clicking the button.

However, with (or without) the buton, I had no problem

If you create a new workbook, add a userform (with a calendar control) and
minimal worksheet data, can you reproduce the problem?

 

[Sige]

Hi Norman,

If you create a new workbook, add a userform (with a calendar control) and
minimal worksheet data, can you reproduce the problem?

Nope... recreated a new wbk & userform ....works fine!
Hmmm, where is the catch this time again?
Sige

 

[Sige]

Norman,
I was too fast ... I did reproduce it ...as said..blank wbk,blank
userform with new calender control.
Upon creation of this calender control the "highlighted day is today.

BUT if you select another day on this control (during creation of the
userform) then this day will be shown ... while launching the userform
on my "specially formatted cell"
(in my case it was 5 march 2002.)

Thta's already that...,
Sige

 

[Norman Jones]

Hi Sige,

Try:

Private Sub UserForm_Initialize()
Calendar1.Value = Date
If IsDate(ActiveCell.Value) Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
End Sub

 

[Sige]

)

That's it! Thx again.

- Norman, I do not dare to ask ... would you like to give a look at:
Copy Locked Cell Status?
(Is "Outlining with condition" a possible card?)

))) Sige

 

[Norman Jones]

Hi Sige,

That's it! Thx again.

Better, however, might be:

Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
If Not ActiveCell.Value = 0 Then
Calendar1.Value = DateValue(ActiveCell.Value)
Else
Calendar1.Value = Date
End If
Else
Calendar1.Value = Date
End If
End Sub

 

[Sige]

Would not know why ... I trust you blindly!