Hide text in a cell.

[RyanH]

I have a CheckBox on a worksheet. I use this checkbox to display or "hide"
text. This code works fine until the worksheet is printed or saved as a PDF.
For example, if the Checkbox = False I can't see it on the worksheet, but I
can see it when it is printed out, why?


Private Sub chkShowTotal_Click()

Dim myTotal As Range
Dim EndRow As Long


'applies theSub Total on the QUOTE sheet
Set myTotal = Sheets("QUOTE").Columns("G:G").Find(What:="TOTAL", _
After:=Cells(10, 7), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)

ActiveSheet.Unprotect "AdTech"

'shows or hides Total and Sub Total
If chkShowTotal = True Then
myTotal.Offset(-3, 1).Font.ColorIndex = 0 'Sub Total
myTotal.Offset(0, 1).Font.ColorIndex = 0 'Total
Else
myTotal.Offset(-3, 1).Font.ColorIndex = 2 'Sub Total
myTotal.Offset(0, 1).Font.ColorIndex = 2 'Total
End If

ActiveSheet.Protect "AdTech"

End Sub

 

[Rick Rothstein \(MVP - VB\)]

What about instead of toggling the ColorIndex to hide the cell contents, if
you toggled the cells content (formula) into and out of a Comment for the
cell? Try replacing your If..Then block to this...

'shows or hides Total and Sub Total
If chkShowTotal = True Then
myTotal.Offset(-3, 1).Formula = _
myTotal.Offset(-3, 1).Comment.Text
myTotal.Offset(-3, 1).Comment.Delete
myTotal.Offset(0, 1).Formula = _
myTotal.Offset(0, 1).Comment.Text
myTotal.Offset(0, 1).Comment.Delete
Else
myTotal.Offset(-3, 1).AddComment _
myTotal.Offset(-3, 1).Formula
myTotal.Offset(-3, 1).Value = ""
myTotal.Offset(0, 1).AddComment _
CStr(myTotal.Offset(0, 1).Formula)
myTotal.Offset(0, 1).Value = ""
End If

Note: The CheckBox must have a checkmark in it before implementing this
code.

Rick

 

[RyanH]

Clever idea! Works beautifully. Thanks

--
Cheers,
Ryan

What about instead of toggling the ColorIndex to hide the cell contents, if
you toggled the cells content (formula) into and out of a Comment for the
cell? Try replacing your If..Then block to this...

'shows or hides Total and Sub Total
If chkShowTotal = True Then
myTotal.Offset(-3, 1).Formula = _
myTotal.Offset(-3, 1).Comment.Text
myTotal.Offset(-3, 1).Comment.Delete
myTotal.Offset(0, 1).Formula = _
myTotal.Offset(0, 1).Comment.Text
myTotal.Offset(0, 1).Comment.Delete
Else
myTotal.Offset(-3, 1).AddComment _
myTotal.Offset(-3, 1).Formula
myTotal.Offset(-3, 1).Value = ""
myTotal.Offset(0, 1).AddComment _
CStr(myTotal.Offset(0, 1).Formula)
myTotal.Offset(0, 1).Value = ""
End If

Note: The CheckBox must have a checkmark in it before implementing this
code.

Rick