CODEWITHSUNDEEP
vbaexcel
Matt
Matt

Reputation: 11

Hiding Characters in Excel, I get the error: Unable to set the NumberFormat property of the Range class

I just want my cells to have only the last 100 characters showing.

Here is my Code:

Sub HideCharacters()
    Dim DQ As String, mesage As String
    On Error GoTo Errorcatch
    DQ = Chr(34)
    For Each r In Selection
        mesage = Right(r.Value, 100)
        mesage = DQ & mesage & DQ
        r.NumberFormat = ";;;" & mesage
    Next r
Errorcatch:
MsgBox Err.Description
End Sub

The code works for a while then as I add more worksheets and apply the macro to them, I start to get the error: "Unable to set the NumberFormat property of the Range class", how can I fix this?

Thank you in advance, -Matt

Upvotes: 1

Views: 111

Answers (1)

Tim Williams
Tim Williams

Reputation: 166735

From:

https://support.office.com/en-my/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3

Number formats in a workbook

Between 200 and 250, depending on the language version of Excel that you have installed

In my Excel 2010 this fails on the 207th format:

Sub Tester()
    Dim c As Range
    For Each c In Range("A1:A1000")
        c.NumberFormat = ";;;""" & c.Address(False, False) & """"
    Next c
End Sub

Upvotes: 1

Related Questions