Automatic cell formatting

[Werner]

--------------------------------------------------------------------------------

Hi,

I have a problem with cells formatting. For an unknown reason, currency
numbers that I write in a form I created sometimes don't want to be used
in a VBA division of a range by another. But the VBA code is ok, because
it works most of the time. But sometimes, when I enter values in it, it
seems to be currency values but VBA says incompatible type on the
division line code. So I found a way to solve my problem by formatting
the cell in standard before writing and then changing it in anything I
want after that. So, I ask if there's a way when a user writes in a
cell to automatically activate a cell formating of that cell in
standard format before what he writes actually be in the cells.

I say it that way cause I never fixed my problem when I tried to change
a cells format after it's writen. To give a hint of the nature of the
problem, when I try to change the problematic currency value in simple
number, in the cell format, it says it's a simple number but if you
look at the cell content, it's still currency!

Thx for any help!

Werner

 

[Werner]

I found the solution in another forum. A great code that solves the
problem for good like magic. A big thanks to this guy, Dave Hawley.
Here's the code :


Code:
--------------------


Sub ForceToNumber()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
With wSheet
.Range("IV65536") = vbNullString
.Range("IV65536").Copy
.UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
End With
Next wSheet
End Sub