macro to white out (to change a font in selected cells)

[Guest]

On my form on the first sheet of the workbook, I want to put a button that,
when clicked, will change the formatting on another sheet (a selection of
cells, actually) from the generic font to white - basically blanking it out
from the page.

I also want another button to add that information back in.

I played around in a blank sheet and was able to change the font color of
cell text, but when I tried to apply it to my actual form, it doesn't work.

Here's what I recorded:
Sheets("Option").Select
Range("B26:E44").Select
Selection.Font.ColorIndex = 4
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone

"Options" contains the information I want to white out, if necessary. The
button is placed on another sheet called "Enter Info."

 

[Markus Scheible]

Hi Abi,

-----Original Message-----
On my form on the first sheet of the workbook, I want to put a button that,
when clicked, will change the formatting on another sheet (a selection of
cells, actually) from the generic font to white - basically blanking it out
from the page.

I think

Sheets("Options").Range("B26:E44").Font.ColorIndex = 2

should work better... if the cells you want to "white out"
are in range B26 to E44... if you want to white the
complete worksheet "Options" simply use

Sheets("Options").Cells.Font.ColorIndex = 2

Best

Markus

 

[Tom Ogilvy]

Just use 1 button:

Sub SwitchColor()
Dim rng As Range
Set rng = Sheets("Option").Range("B26:E44")
If rng(1).Font.ColorIndex <> 2 Then
rng.Font.ColorIndex = 2
Else
rng.Font.ColorIndex = xlAutomatic
End If
End Sub

If you really want to process the borders, post back.

 

[Guest]

That works great -thanks, but I have an addendum to this. What about the
formatting of a cell? I mean, I have used some lines for the cells and I
want those to go also.) I'm trying to look through the help files, since I'm
assuming it follows the same format, but...I'm slow!

Thanks for the help!

 

[Guest]

I definitely want to remove and add the borders. I've been looking at the
"Borders" command in the help files, but can't seem to get how it functions.

 

[Tom Ogilvy]

I will assume all the cells in that range have borders around them:

Sub SwitchColor()
Dim rng As Range
Set rng = Sheets("Option").Range("B26:E44")
If rng(1).Font.ColorIndex <> 2 Then
rng.Font.ColorIndex = 2
rng.Borders.ColorIndex = xlNone
Else
rng.Font.ColorIndex = xlAutomatic
rng.Borders.ColorIndex = xlAutomatic
End If
End Sub

 

[Guest]

I'm not sure how I apply this to my button. I have CommandButton9 to use for
this, but I don't know how to get it to function with click()

????? sorry! I'm a bit thick today, I guess!

 

[Tom Ogilvy]

Right click on the sheet tab containing the button and select View Code

In the resulting module, at the top, in the left dropdown, select
CommandButton9
in the right dropdown select click

make it look like this

Private Sub CommandButton9()
ActiveCell.Activate
Dim rng As Range
Set rng = Sheets("Option").Range("B26:E44")
If rng(1).Font.ColorIndex <> 2 Then
rng.Font.ColorIndex = 2
rng.Borders.ColorIndex = xlNone
Else
rng.Font.ColorIndex = xlAutomatic
rng.Borders.ColorIndex = xlAutomatic
End If
End Sub


Then go back to your worksheet. Make sure you are not in design mode (if
you click the button and it gets selected, then you are in design mode. In
the control toolbox toolbar the top left button would then appear depressed.
Click it so it does not appear depressed).

So clicking the button should toggle back and forth between showing the text
and not showing the text.

 

[Guest]

The functionality isn't working. It's not "not" working - I mean, I'm not
running into any script errors, however it isn't negating the information on
the Option sheet. I'm stumped.

 

[Tom Ogilvy]

My bad - typo on the name of the sub - should be:

Private Sub CommandButton9_Click()
ActiveCell.Activate
Dim rng As Range
Set rng = Sheets("Option").Range("B26:E44")
If rng(1).Font.ColorIndex <> 2 Then
rng.Font.ColorIndex = 2
rng.Borders.ColorIndex = xlNone
Else
rng.Font.ColorIndex = xlAutomatic
rng.Borders.ColorIndex = xlAutomatic
End If
End Sub

I left the _Click part off.

tested and worked fine for me.

 

[Guest]

GENIUS!!!!! Thanks so much!!! I'm learning a lot around here...