TimeSerial does not reflect Windows Regional Settings

[Paul_Hossler]

I'm trying to make a workbook respond to changes in the Windows Regional Settings.

According to what I've read, this is the way to enter currency, date, and times.



Option Explicit
Sub Macro1()
Worksheets("Sheet1").Columns(1).Delete
Worksheets("Sheet1").Range("a1").Value = CCur(-12341234.45)
Worksheets("Sheet1").Range("a2").Value = DateSerial(2014, 10, 20)
Worksheets("Sheet1").Range("a3").Value = TimeSerial(10, 10, 15)
End Sub


The Currency and the Date have the Number Format = 'Currency' and 'Date' but the Time has a Number Format = 'Custom'

Currency and Date respond to changes in WRS, but since Time is 'Custom' it does not. If I manually apply the 'Time' format, then it does respond.

Can anyone suggest a correction or workaround?

Thanks

[snb]

Sub Macro1()
Worksheets("Sheet1").Columns(1).Delete
Worksheets("Sheet1").Range("a1").Value = CCur(-12341234.45)
Worksheets("Sheet1").Range("a2").Value = DateSerial(2014, 10, 20)
Worksheets("Sheet1").Range("a3").Value = TimeSerial(10, 10, 15) / 1
Worksheets("Sheet1").Range("a3").NumberFormat = "hh:mm:ss"
End Sub

[Paul_Hossler]

@snb -- that moved me a little in a different direction. SinceCCur and DateSerial didn't require any NumberFormat-ing, I was trying to figure out a similar way for TimeSerial. Just would have made things a little simpler over all.

Without the Locale ID [$-F400] it was not reliable and seemed to depend on the WRS setting when the data was entered. But so far (fingers crossed) this seems to be working



Option Explicit
'http://office.microsoft.com/en-us/excel-help/creating-international-number-formats-HA001034635.aspx
Sub Macro1()
Worksheets("Sheet1").Columns(1).Delete
Worksheets("Sheet1").Range("a1").Value = CCur(-12341234.45)
Worksheets("Sheet1").Range("a2").Value = DateSerial(2014, 10, 20)
Worksheets("Sheet1").Range("a3").Value = TimeSerial(20, 10, 15)
Worksheets("Sheet1").Range("a3").NumberFormatLocal = "[$-F400]h:mm:ss AM/PM"
End Sub

[snb]

Since you are making it a fixed format why don't you use ?


Worksheets("Sheet1").Range("a3").NumberFormat = "[$-F400]h:mm:ss AM/PM"