ANmaCellFormat

Reads or set Numberformatting for a cell or range.
Can pass sheet and workbook name.
If "ChangeTo" argument is passed, it will change the format of number to be displayed for that cell, otherwise, it will return the format.
"General" or 1
"Number", "#" or 2
"Text", "@" or 3

CodeFunctionName
What is this?

Public

Tested

Original Work
Function ANmaCellFormat(CellAddress, Optional ChangeTo = "DoNotChange", Optional Shee = "Active", Optional WB = "This")
    ' Returns format of cell, or change it to new format
    ' Returns
    ' Number Format = "#" > Number | "@" > Text | "General" > General
    '    = 0 failed to read
    '     = 1 General, change it to...
    '     = 2 Number, not Currency, change it to...
    '     = 3 Text
    ' ChangeTo
    '    = "DoNotChange", do not change keep it
    '    = "General" or 1 > > Make it General
    '    = "Number", "#" or 2 > > Make it Number format
    '    = "Text", "@" or 3 > > Make it Text
    '
    Rett = 0
    If WB = "This" Then WB = Thisworkbook.name
    If Shee = "Active" Then Shee = Workbooks(WB).ActiveSheet.name
    Foo = Workbooks(WB).Worksheets(Shee).Range(CellAddress).Numberformat
    Rett = Foo
    If Foo = "General" Then Rett = 1
    If Foo = "#" Then Rett = 2 ' Number
    If Foo = "@" Then Rett = 3 ' "Text"
    If ChangeTo = "DoNotChange" Then
        ' Do not Change, keep it
    Else
        If Val(ChangeTo) = 1 Or ChangeTo = "General" Then
            Foo2 = "General"
        ElseIf ChangeTo = "#" Or ChangeTo = "Number" Or Val(ChangeTo) = 2 Then
            Foo2 = "#"
        ElseIf ChangeTo = "@" Or ChangeTo = "Text" Or Val(ChangeTo) = 3 Then
            Foo2 = "@"
        Else
            Foo2 = ChangeTo
        End If
        Workbooks(WB).Worksheets(Shee).Range(CellAddress).NumberFormat = Foo2
        Rett = ChangeTo
    End If
    ANmaCellFormat = Rett
End Function

CellAddress, Optional ChangeTo = "DoNotChange", Optional Shee = "Active", Optional WB = "This"

Views 713

Downloads 54

CodeID
DB ID