formatting macro with a toggle button

S

Stephen

Hi -

I am trying to change the formatting of a group of cells with a toggle
button, but the cells to not format to accounting when the toggle
button is clicked. Is there something I can add to this?

Here is what I have so far -
Cells M62:AJ64 are the cells that I would like to have the formatting
changed. Cell CC60 is the linked cell of the toggle button that
changes from TRUE to FALSE. I can't seem to get the formatting to
change when the value is TRUE.

Range("M62:AJ64").Select
With Selection
If .Cells(cc60) = False Then
Selection.NumberFormat = "0.00%"
Else
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)"
End If
End With

Thanks.
STephen
 
H

Hank Scorpio

Hi -

I am trying to change the formatting of a group of cells with a toggle
button, but the cells to not format to accounting when the toggle
button is clicked. Is there something I can add to this?

Here is what I have so far -
Cells M62:AJ64 are the cells that I would like to have the formatting
changed. Cell CC60 is the linked cell of the toggle button that
changes from TRUE to FALSE. I can't seem to get the formatting to
change when the value is TRUE.

Range("M62:AJ64").Select
With Selection
Click to expand...

There are three problems on the next line. First, CC60 isn't part of
the "Selection" and therefore shouldn't have a dot operator in front
of it.

Second, cc60 is going to be interpreted as a variable. Third, the
arguments for Cells should be row and column indexes.

I also encountered a problem with your second number format.
If .Cells(cc60) = False Then
Selection.NumberFormat = "0.00%"
Else
Selection.NumberFormat = "_($* #,##0.00_);_($* (#,##0.00);_($*
""-""??_);_(@_)"
End If
End With
Click to expand...

This, however, worked for me:

Private Sub ToggleButton1_Click()

Range("M62:AJ64").Select

With Selection

'Use Range instead of Cells.
If Range("CC60") = False Then

Selection.NumberFormat = "0.00%"

Else

Selection.NumberFormat = _
"_($* #,##0.00_);_($* (#,##0.00);_($*-??_);_(@_)"

End If
End With

End Sub

Note: Strictly speaking there is no need to select the range before
applying the formatting, but I assumed that you want the user to see
it change.
 
H

Henry

Stephen,

Cell CC60 is not within your selection so you don't need the full stop
before it.
Also, it's the Value of the toggle button that you need.

Try
If Cells(CC60).Value = False Then

HTH
Henry
 
Top