Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Changing the date format on the date table in a chart | Charts and Charting in Excel | |||
mm:ss keeps changing to a date format | Excel Discussion (Misc queries) | |||
changing format code?? please help!!! | Excel Discussion (Misc queries) | |||
changing date formatted as general number into date format | Excel Programming | |||
code to convert date from TEXT format (03-02) to DATE format (200203) | Excel Programming |