Event Macro - On Cell Activate - Change Cell Format
 

I'm looking for some code to make any cell change to toggle from
normal background / black background when that cell is clicked.

The idea is to hide / reveal the text in a cell with each click.

Thank you for any ideas.

Event Macro - On Cell Activate - Change Cell Format
 

This goes in the worksheet code area:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Interior.ColorIndex = 56 Then
Target.Interior.ColorIndex = xlNone
Exit Sub
End If
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 56
End If
End Sub

--
Gary''s Student - gsnu2007a


" wrote:

I'm looking for some code to make any cell change to toggle from
normal background / black background when that cell is clicked.

The idea is to hide / reveal the text in a cell with each click.

Thank you for any ideas.

Event Macro - On Cell Activate - Change Cell Format
 

I'm looking for some code to make any cell change to toggle from
normal background / black background when that cell is clicked.

The idea is to hide / reveal the text in a cell with each click.

Gary''s Student gave you a method using the SelectionChange event; but,
personally, I think that interface is somewhat awkward (you have to click
away from a cell in order to click back in it to reverse the change; but
then the color of the cell you clicked away to was changed). If you don't
mind changing the activation keystroke, I think this works a little better.
It involves using the right-click instead of the left click and you can
click within a selected cell to change it back again (assuming it was
clicked by mistake). See if Gary''s Student's, placed in the
BeforeRightClick event code (with an extra statement to cancel the popup
menu) works for you....

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
If Target.Interior.ColorIndex = 56 Then
Target.Interior.ColorIndex = xlNone
Exit Sub
End If
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 56
End If
End Sub

Remember... right clicking toggles the color.

Rick

Event Macro - On Cell Activate - Change Cell Format
 

On Nov 3, 3:55 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
I'm looking for some code to make any cell change to toggle from
normal background / black background when that cell is clicked.

The idea is to hide / reveal the text in a cell with each click.

Gary''s Student gave you a method using the SelectionChange event; but,
personally, I think that interface is somewhat awkward (you have to click
away from a cell in order to click back in it to reverse the change; but
then the color of the cell you clicked away to was changed). If you don't
mind changing the activation keystroke, I think this works a little better.
It involves using the right-click instead of the left click and you can
click within a selected cell to change it back again (assuming it was
clicked by mistake). See if Gary''s Student's, placed in the
BeforeRightClick event code (with an extra statement to cancel the popup
menu) works for you....

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
If Target.Interior.ColorIndex = 56 Then
Target.Interior.ColorIndex = xlNone
Exit Sub
End If
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 56
End If
End Sub

Remember... right clicking toggles the color.

Rick

Thanks Rick and Gary.

I loaded Rick's code but it doesn't seem to work when I right click on
a cell.

Any ideas on what I've done incorrectly?

Thank you.

Event Macro - On Cell Activate - Change Cell Format
 

Right-click on the worksheet tab and select View Code from the pop-up menu.
Is the code there? If not, put it there.

Mike F
wrote in message
ups.com...
On Nov 3, 3:55 pm, "Rick Rothstein \(MVP - VB\)"
wrote:
I'm looking for some code to make any cell change to toggle from
normal background / black background when that cell is clicked.

The idea is to hide / reveal the text in a cell with each click.

Gary''s Student gave you a method using the SelectionChange event; but,
personally, I think that interface is somewhat awkward (you have to click
away from a cell in order to click back in it to reverse the change; but
then the color of the cell you clicked away to was changed). If you don't
mind changing the activation keystroke, I think this works a little
better.
It involves using the right-click instead of the left click and you can
click within a selected cell to change it back again (assuming it was
clicked by mistake). See if Gary''s Student's, placed in the
BeforeRightClick event code (with an extra statement to cancel the popup
menu) works for you....

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
If Target.Interior.ColorIndex = 56 Then
Target.Interior.ColorIndex = xlNone
Exit Sub
End If
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 56
End If
End Sub

Remember... right clicking toggles the color.

Rick

Thanks Rick and Gary.

I loaded Rick's code but it doesn't seem to work when I right click on
a cell.

Any ideas on what I've done incorrectly?

Thank you.

Event Macro - On Cell Activate - Change Cell Format
 

Slightly more efficient.

Cancel = True
With Target
If .Interior.ColorIndex = 56 Then
.Interior.ColorIndex = xlNone
Else
.Interior.ColorIndex = 56
End If
End With

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein (MVP - VB)" wrote in
message ...
I'm looking for some code to make any cell change to toggle from
normal background / black background when that cell is clicked.

The idea is to hide / reveal the text in a cell with each click.

Gary''s Student gave you a method using the SelectionChange event; but,
personally, I think that interface is somewhat awkward (you have to click
away from a cell in order to click back in it to reverse the change; but
then the color of the cell you clicked away to was changed). If you don't
mind changing the activation keystroke, I think this works a little
better. It involves using the right-click instead of the left click and
you can click within a selected cell to change it back again (assuming it
was clicked by mistake). See if Gary''s Student's, placed in the
BeforeRightClick event code (with an extra statement to cancel the popup
menu) works for you....

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
If Target.Interior.ColorIndex = 56 Then
Target.Interior.ColorIndex = xlNone
Exit Sub
End If
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 56
End If
End Sub

Remember... right clicking toggles the color.

Rick

Event Macro - On Cell Activate - Change Cell Format
 

Thanks Mike and Don - That works great!

Event Macro - On Cell Activate - Change Cell Format
 

Maybe another idea...

With Target.Interior
If .ColorIndex = 56 Then
.ColorIndex = xlNone
Else
.ColorIndex = 56
End If
End With

--
Dana DeLouis


"Don Guillett" wrote in message
...
Slightly more efficient.

Cancel = True
With Target
If .Interior.ColorIndex = 56 Then
.Interior.ColorIndex = xlNone
Else
.Interior.ColorIndex = 56
End If
End With

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Rick Rothstein (MVP - VB)" wrote in
message ...
I'm looking for some code to make any cell change to toggle from
normal background / black background when that cell is clicked.

The idea is to hide / reveal the text in a cell with each click.

Gary''s Student gave you a method using the SelectionChange event; but,
personally, I think that interface is somewhat awkward (you have to click
away from a cell in order to click back in it to reverse the change; but
then the color of the cell you clicked away to was changed). If you don't
mind changing the activation keystroke, I think this works a little
better. It involves using the right-click instead of the left click and
you can click within a selected cell to change it back again (assuming it
was clicked by mistake). See if Gary''s Student's, placed in the
BeforeRightClick event code (with an extra statement to cancel the popup
menu) works for you....

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, _
Cancel As Boolean)
Cancel = True
If Target.Interior.ColorIndex = 56 Then
Target.Interior.ColorIndex = xlNone
Exit Sub
End If
If Target.Interior.ColorIndex = xlNone Then
Target.Interior.ColorIndex = 56
End If
End Sub

Remember... right clicking toggles the color.

Rick