I have a user form where cell A1 is a input field for "WeekNo., No of days,
or Date. The date is selected by use of the build in calandar.
The range A2-A12 are referenced to A1+1, A1+2 etc.
This works fine, as long the Date is not used, because the Calendar will
convert the formatting of the cell to the "date" format.
The formula: & Text(today(),"mm/dd/yy") cannot be used as this format the
Cell to text, not allowing the range A2-A12 to be updated when the date is
selected.

Your help or guidance is appriciated.
thanks.
Paco.E

I didn't understand your remark about WeekNo., No of days. With A2 =
A1+1 etc. A2:A12 work fine for me, except they show serial dates. They
are easily switched to date format, e.g.:
Private Sub Calendar1_Click()
Sheets("Sheet1").Range("A1") = Calendar1.Value
Sheets("Sheet1").Range("A2:A12").NumberFormat = "m/d/yyyy"
End Sub

If this doesn't work for WeekNo. or No of days, you can change the
format of A2:A12 in a similar way.

Hth,
Merjet

Hi Merjet,
Thks, sorry for the slow reply, but was tight up in Lunar NY celebrations
"Con chi fa Chai"

Yes, my question is confusing.
Here again,

How do I reset cell A1 format after the use of the calandar, which format
the cell A1 to Date (e.g dd/mm/yy)
So the next case cell A1 is used for number format
(as needed for No.of days; e.g. "3"),
The cell still indicate a date format e.g. 03/01/00 instead of the number 3
How do i reset the date back to numeric??

Thnks



"merjet" wrote:

I didn't understand your remark about WeekNo., No of days. With A2 =
A1+1 etc. A2:A12 work fine for me, except they show serial dates. They
are easily switched to date format, e.g.:
Private Sub Calendar1_Click()
Sheets("Sheet1").Range("A1") = Calendar1.Value
Sheets("Sheet1").Range("A2:A12").NumberFormat = "m/d/yyyy"
End Sub

If this doesn't work for WeekNo. or No of days, you can change the
format of A2:A12 in a similar way.

Hth,
Merjet

Range("A1").NumberFormat = "0"

Hth,
Merjet

thanks
Paco.E

"merjet" wrote:

Range("A1").NumberFormat = "0"

Hth,
Merjet