How to fix VBA macro for format cycle

Question

I am trying to create a macro that cycles through different formats by simply pressing its corresponding shortcut. The code I created is the following:

Option Explicit

Sub FormatCycle()
'
' FormatCycle Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'

If Selection.NumberFormat = "General" Then
    Selection.NumberFormat = "#,##0.00_);(#,##0.00)"
ElseIf Selection.NumberFormat = "#,##0.00_);(#,##0.00)" Then
    Selection.NumberFormat = "0.00%_);(0.00%)"
ElseIf Selection.NumberFormat = "0.00%_);(0.00%)" Then
    Selection.NumberFormat = "#,##0.00""x"";(#,##0.00""x"")"
ElseIf Selection.NumberFormat = "#,##0.00""x"";(#,##0.00""x"")" Then
    Selection.NumberFormat = "General"
Else
    Selection.NumberFormat = "General"
End If

End Sub

Everything works fine except for the second type of format with negative values (i.e. (#,##0.00)). When I use this format on its own, e.g. by custom formatting a generic cell, it does its job and displays the negative number in brackets. However, within the macro, when I make it run it shows the number with the "general" format, e.g. -12.00 instead of (12.00).

What I am getting wrong?

For negative numbers to display in parenthesis, try wrapping in quotation marks, like this NumberFormat = "0;(""$0,000"")" — Jenn
– Jenn, Commented Jul 7, 2019 at 14:56

Jenn · Accepted Answer · 2019-07-07 14:57:48Z

0

For negative numbers to display in parenthesis, try wrapping in quotation marks, like this NumberFormat = "0;(""$0,000"")"

answered Jul 7, 2019 at 14:57

Jenn

6621 gold badge4 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

albfas Over a year ago

Hi Jenn, thanks for your reply. Unfortunately, this does not work: by using the quotation marks as you suggested, Excel recognises the content in the quotation mark as a string and the output is then simply (#,##0.00).

chillin · Accepted Answer · 2019-07-07 16:21:58Z

0

I too was seeing -12.00 with Selection.NumberFormat, but began seeing (12.00) once I switched to Selection.NumberFormatLocal. I'd therefore suggest replacing Selection.NumberFormat in your code with Selection.NumberFormatLocal.

Something like:

Option Explicit

Sub FormatCycle()
'
' FormatCycle Macro
'
' Keyboard Shortcut: Ctrl+Shift+E
'
    Dim currentCell As Range
    On Error Resume Next
    Set currentCell = Selection
    On Error GoTo 0

    If currentCell Is Nothing Then Exit Sub ' Could probably check if TypeName is a range

    With currentCell
        Select Case .NumberFormatLocal
            Case "General"
                .NumberFormatLocal = "#,##0.00_);(#,##0.00)"

            Case "#,##0.00_);(#,##0.00)"
                .NumberFormatLocal = "0.00%_);(0.00%)"

            Case "0.00%_);(0.00%)"
                .NumberFormatLocal = "#,##0.00""x"";(#,##0.00""x"")"

            Case "#,##0.00""x"";(#,##0.00""x"")"
                .NumberFormatLocal = "General"

            Case Else
                .NumberFormatLocal = "General"
        End Select
    End With

End Sub

The other thing is that you are effectively looping through a predefined list of number formats (each branch in your IF statement is what was assigned in the previous branch). Therefore, it might be better to store all number formats that you want to loop through in an array, then look up the position of the current number format (in the array) and return the number format which follows it. That way if you want to introduce more number formats into the cycle, instead of manually adding more If Else or Case branches, you'd only need to add the new entry(s) to the array.

On the other hand, if your current approach is fine and this is just a quick macro, then no need to fix what's not broken.

answered Jul 7, 2019 at 16:21

chillin

4,4861 gold badge11 silver badges8 bronze badges

5 Comments

albfas Over a year ago

Hi Chillin, thanks for your reply. I tried your code, but unfortunately nothing happens (literally). As for your suggestion to use arrays, do you mean something of this kind? ``` Sub NewFormat() ' ' NewFormat Macro ' ' Keyboard Shortcut: Ctrl+Shift+T Dim x, myFormats myFormats = Array("#,##0.00;(#,##0.00)", "0.00%;(0.00%)", "0.00""x"";(0.00""x"")") x = Application.Match(Selection.NumberFormat, myFormats, False) If IsError(x) Then x = 0 Selection.NumberFormat = myFormats((x Mod (UBound(myFormats) + 1))) End Sub ```

chillin Over a year ago

@albfas, I tested it on my end before submitting and it was working for me (the selected cell's number formatting was changing every time the code was executed). I recommend selecting the cell you want to change, then step through each line of the code with F8 key -- and see why it's doing nothing.

chillin Over a year ago

Also, the code in your comment is roughly what I meant, yes.

albfas Over a year ago

I get this error message: "run-time error '1004': unable to set the NumberFormatLocal property of the Range class". The debug tool highlights the following line of code " .NumberFormatLocal = "General"". P.S. I am a VBA beginner, can't really figure out what's going wrong.

albfas Over a year ago

Update: I used your code, with the following changes and it worked so thanks a lot! 1) didn't use "on error" functions and "if current cell is nothing..." line 2) I used .NumberFormat everywhere, except for the format where I was having trouble, for which I used .NumberFormatLocal.

Collectives™ on Stack Overflow

How to fix VBA macro for format cycle

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related