CODEWITHSUNDEEP
excelvba
Don Desrosiers
Don Desrosiers

Reputation: 143

number formatting is changed erroneously to time/date

I have a spreadsheet with numerous formats, mostly time and whole number. Data validation is present to assure proper formats. certain fields can be selected and VBA programmatically inserts the system time to the selected cell. This works perfectly and is very helpful when using this sheet. The problem occurs if a cell that should have a whole number (and definitely not a time) is accidently selected, then the "enter system time" code is run. This changes the format of that cell to time/date. said in another way, A1 is formatted for Number, 0 decimal places. A1 is selected and the "Enter system time" macro is run. The macro is not affected by the data validation rule that would otherwise prohibit anything but a whole number between 1 and 999. Now that the formatting is changed by VBA (somehow), when you enter, say, 5 into that (formerly) number-formatted cell, displayed is 1/5/1900 0:00. Because this is a compiled workbook, the change is permanent; the cell format cannot be changed back. so, my question is: Is there a workaround? Is there a way to prevent this sort of format change? is there a way to unlock the cell if the format is wrong? Is there any sort of suggested method to avoid this?

Upvotes: 0

Views: 259

Answers (2)

Don Desrosiers
Don Desrosiers

Reputation: 143

Finally got this. The correct number formatting command is: If Selection.NumberFormat = "[$-409]h:mm AM/PM;@" Then...

Upvotes: 0

Phillip Beck
Phillip Beck

Reputation: 86

You can check if the format is a number with 0 decimal places using the NumberFormat property of the Range object like:

If Range("A1").NumberFormat = "0" Then
    'Some Code
End If

Check https://msdn.microsoft.com/pt-br/library/office/ff196401.aspx for more information on formats

Upvotes: 1

Related Questions