Format toggle issue

[kkonen]

I am tryin g to write code that will toggle through number formats. I have tried using if-else and case select methods. They behave differently, but neither work. If-else toggles between the first format and General. The Case method always sets to the percentage type and doesn't toggle.

Both codes are below, with the if-else commented out.

Thanks!

Public Sub Numberformat_ToggleDo()
Dim WorkRng As Range

On Error Resume Next

Set WorkRng = Application.Selection

Select Case WorkRng.Cells(1, 1).NumberFormat
    Case "General": WorkRng.NumberFormat = "#,##0.0_);(#,##0.0)"
    Case "#,##0.0_);(#,##0.0)": WorkRng.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* " - "?_);_(@_)"
    Case "_($* #,##0.0_);_($* (#,##0.0);_($* " - "?_);_(@_)": WorkRng.NumberFormat = "#,##0.0%_);(#,##0.0%)"
    Case "#,##0.0%_);(#,##0.0%)": WorkRng.NumberFormat = "#,##0.0x"
    Case Else: WorkRng.NumberFormat = "General"
End Select

'If WorkRng.Cells(1, 1).NumberFormat = "#,##0.0_);(#,##0.0)" Then
'    WorkRng.NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* " - "?_);_(@_)"
'ElseIf WorkRng.Cells(1, 1).NumberFormat = "_($* #,##0.0_);_($* (#,##0.0);_($* " - "?_);_(@_)" Then
'    WorkRng.NumberFormat = "#,##0.0%_);(#,##0.0%)"
'ElseIf WorkRng.Cells(1, 1).NumberFormat = "#,##0.0%_);(#,##0.0%)" Then
'    WorkRng.NumberFormat = "#,##0.0x"
'ElseIf WorkRng.Cells(1, 1).NumberFormat = "#,##0.0x" Then
'    WorkRng.NumberFormat = "General"
'Else
'    WorkRng.NumberFormat = "#,##0.0_);(#,##0.0)"
'End If
On Error GoTo 0
End Sub

 

[afonsomira]

Hello, check this:

Public Sub Numberformat_ToggleDo()
Dim WorkRng As Range

On Error Resume Next

Set WorkRng = Application.Selection

Select Case WorkRng.Cells(1, 1).NumberFormat
    Case "General"
    WorkRng.NumberFormat = "#,##0.0_);(#,##0.0)"
    
    Case "#,##0.0_);(#,##0.0)"
    WorkRng.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""?_);_(@_)"
    
    Case "_($* #,##0.00_);_($* (#,##0.00);_($* ""-""?_);_(@_)"
    WorkRng.NumberFormat = "#,##0.0%_);(#,##0.0%)"
    
    Case "#,##0.0%_);(#,##0.0%)"
    WorkRng.NumberFormat = "#,##0.0x"
    
    Case Else
    WorkRng.NumberFormat = "General"
End Select
On Error GoTo 0
End Sub