Automatic cell formatting

  • 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

  • Re: Automatic cell formatting


    I've found a previous thread in this forum that had the same damn problem in it and inside there was a wonderful and simple solution gave by Dave Hawley. It's an administrator there. I give a link to this thread and copy the code here. I am so happy. This problem was being very painful in my brain. :)


    Werner


    http://www.ozgrid.com/forum/sh…d.php?p=184087#post184087


    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
  • Re: Automatic cell formatting


    Glad you found the answer. And it is good that you came back and provided the reference.


    Well done! :)


    Software: OpenOffice 3.0/NeoOffice 3.0 on Mac OS X 10.5.6
    Humanware: Older than dirt


    Old, slow, and confused - but at least I'm inconsistent!


    Rich
    (retired Excel 2003 user, 3.28.2008)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!