Code is changing date format
 

Hi
The following code converts any text which is input to uppercase regardless
of where it is input within the workbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each cell In Target
With cell
If Not .HasFormula Then
cell.Value = StrConv(cell.Value, vbUpperCase)
End If
End With
Next cell
ErrHandler:
Application.EnableEvents = True
End Sub

My problem is that it also converts the dates in column B from dd/mmm (UK
format) to dd/mmm (US format) when entered. IE today's date is put in column
B(automatically by code) as 06/Nov but it is changed immediately to 11/Jun.
Can column B be omited from the code above or is there another way around
this?
Thanks


--
Traa Dy Liooar

Jock

Code is changing date format
 

Hi Jock
I tried to replicate your problem but failed......
I am working in the UK - pasted oyu code into the workbook module
Code worked fine with text
Dates entered as 06.11.2007 entered and stayed the same
Only woerd thing is that I could not change the format of the date to
anything else - just did not happen (ie applying a custom dd mmmm yyyy to a
cell where a date had already been input had no effect on the default
dd.mm.yyyy format) - strange

"Jock" wrote:

Hi
The following code converts any text which is input to uppercase regardless
of where it is input within the workbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each cell In Target
With cell
If Not .HasFormula Then
cell.Value = StrConv(cell.Value, vbUpperCase)
End If
End With
Next cell
ErrHandler:
Application.EnableEvents = True
End Sub

My problem is that it also converts the dates in column B from dd/mmm (UK
format) to dd/mmm (US format) when entered. IE today's date is put in column
B(automatically by code) as 06/Nov but it is changed immediately to 11/Jun.
Can column B be omited from the code above or is there another way around
this?
Thanks


--
Traa Dy Liooar

Jock

Code is changing date format
 

Thanks Dom. Have got around it by using a Worksheet_Change sub instead.
Cheers
--
Traa Dy Liooar

Jock


"DomThePom" wrote:

Hi Jock
I tried to replicate your problem but failed......
I am working in the UK - pasted oyu code into the workbook module
Code worked fine with text
Dates entered as 06.11.2007 entered and stayed the same
Only woerd thing is that I could not change the format of the date to
anything else - just did not happen (ie applying a custom dd mmmm yyyy to a
cell where a date had already been input had no effect on the default
dd.mm.yyyy format) - strange

"Jock" wrote:

Hi
The following code converts any text which is input to uppercase regardless
of where it is input within the workbook:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo ErrHandler
Application.EnableEvents = False
For Each cell In Target
With cell
If Not .HasFormula Then
cell.Value = StrConv(cell.Value, vbUpperCase)
End If
End With
Next cell
ErrHandler:
Application.EnableEvents = True
End Sub

My problem is that it also converts the dates in column B from dd/mmm (UK
format) to dd/mmm (US format) when entered. IE today's date is put in column
B(automatically by code) as 06/Nov but it is changed immediately to 11/Jun.
Can column B be omited from the code above or is there another way around
this?
Thanks


--
Traa Dy Liooar

Jock