ExcelBanter - View Single Post

Hank Scorpio

On 25 Jan 2004 00:25:53 -0800, (Stephen) wrote:

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

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

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.

---------------------------------------------------------
Hank Scorpio
- Yes, yes, I know I've been missing for a while. Long story. And don't
ask what the size of my inbox is at the moment either... 8^
scorpionet who hates spam is at iprimus.com.au (You know what to do.)