Upper case and Date problem

[Chris Hankin]

Hello,

I am using the following code to try to make all text in my worksheet go
to Upper case:

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A3:AZ102")) Is Nothing Then
Target(1).Value = UCase(Target(1).Value)
End If
Application.EnableEvents = True
End Sub

However, when I enter dates into my worksheet whilst using the
above-mentioned code, the format of the dates changes from dd-mmm-yy
(eg:14-Mar-01) to dd/mm/yyyy (eg: 14/03/2001). I tried to format the
date cells but was unable to change the date format back to dd-mmm-yy.

If I disable the above-mentioned code, I am able to change the date
format back to dd-mmm-yy.

Could someone please advise on how I can change my worksheet's text to
upper case whilst preserving the date format in dd-mmm-yy?

Any help would be greatly appreciated.

Kind regards,

Chris.

Live Long and Prosper

 

[Guest]

Hi Chris,

Try this...

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Not Application.Intersect(Target, Range("A3:AZ102")) Is Nothing Then
If Not IsDate(Target.Value) Then
Target.Value = UCase(Target.Value)
Else
Target.Value = Format(Target.Value, "dd-mmm-yy")
End If
End If
Application.EnableEvents = True
End Sub

 

[Chris Hankin]

Hello Anant Basant,

Thanks for all your help - very much appreciated.

Cheers,

Chris.

Live Long and Prosper