Data|validation can't hurt.

But if you're only displaying the stuff you see in the cell, you could still
just use the .text property of the sending cell and use a Text format for the
receiving cell.

Or maybe you could incorporate something like:

Option Explicit
Sub testme()
Dim pfxChar As String
With ActiveSheet
pfxChar = .Range("a1").PrefixCharacter
.Range("b1").NumberFormat = .Range("a1").NumberFormat
.Range("b1").Value2 = pfxChar & .Range("a1").Value2
End With
End Sub

It worked in minor testing.

AB wrote:

Thanks!
The idea of keeping the Old/New format should be helpful.

A problem it doesn't seem to solve is in scenario like this:
- range formatted as dd/mm/yyyy
- a user enters: '03/01/2009 [i.e., a string (because of the " ' ")
that looks like a date]
- the code passes over the format dd/mm/yyyy
- the code changes the value from '03/01/2009 to 01/03/2009...

Unfortunately this scenario is possible as one can never know what a
user decides to enter...

Is there any solution to this? Or i should enforce some data
validation (or calendar control) on all ranges that should contain a
date?
Any ideas?

--

Dave Peterson