Cell data change formatting

[Guest]

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

 

[merjet]

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

 

[Guest]

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]

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

Hth,
Merjet

 

[Guest]

thanks
Paco.E