Show/Hide a Secret Cell

  • Thread starter Thread starter Hotbird
  • Start date Start date
H

Hotbird

I am wondering if it would be possible to implement a feature, whereby a
cell which contains information hidden as grey text on a grey background,
could automatically change to black on grey in response to mouse movement
over the cell in question. There do not seem to be standard spreadsheet
events to trigger the 2 necessary macros.
 
Try something like the following:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$C$3" Then
Target.Font.ColorIndex = 1
Else
Range("$C$3").Font.ColorIndex = 15
End If
End Sub

Put this code in the sheet module for the appropriate sheet.
Change $C$3 to the cell in question and change the 15 to the
correct colorindex value.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Many thanks for your suggestion Chip

I am using a very simple test spreadsheet - one page only called "Sheet1",
with text "11" in cell A1 with both font and background colour set grey.
There is no Module, but Sheet1 has the following code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Address = "$A$1" Then
MsgBox "This Code is active 1"
Target.Font.Color.Index = 1 ' black text
Else
MsgBox "This Code is active 2"
Range("$A$1").Font.Color.Index = 15 'grey text
End If
End Sub

I click on cell A1, but unfortunately, get a run-time error '424' saying
"Object Required" and the Debug window highlights in yellow the line:
Target.Font.Color.Index=1. Have I missed something obvious?

Thank you again for taking the time to help.
 
Chip didn't have:
Target.Font.Color.Index
he had
Target.Font.ColorIndex

(no dot in colorindex--for both parts)
 
Change both occurrences of
Color.Index
to
ColorIndex

ColorIndex is all one word.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)
 
Have you thought of putting your secret stuff in the comment? Then when you
move your mouse over that cell, the comment will appear (assuming you're showing
comments).

But there is no mouseover event for a cell.

But you could do something like putting two images from from the control toolbox
toolbar over the cells.

Make one kind of smaller and one a little bigger. Right click on each and
select properties. Make the backstyle transparent and make the borderstyle none
(so that they don't look like they're there.)

Position them over your range (A1:I1)

Make it so the little one is contained by the larger one and the larger one has
a little "border" showing. Then right click on the larger one and choose Order
and then Send to back. (Now the little one is on top of the larger one.)

+-------------------+
| |
| +------------+ |
| | | |
| +------------+ |
| |
+-------------------+

Call the little one image1 and the big one image2.

Now you can can check for movement of the mouse over those images.

Option Explicit
Private Sub Image1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)

Range("a1:i1").Font.ColorIndex = 1
End Sub

Private Sub Image2_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)

Range("a1:i1").Font.ColorIndex = 15
End Sub

This goes under the worksheet module.

When you move the mouse over the small one, the font gets changed. When you
move off the cell, you'll (hopefully) move across the larger image and that'll
hide characters.
 
I haven't used comments because the secret information consists of
concatenated index functions. Using pairs of images is a great idea Dave. I
think it will keep me entertained for several days ....
(You are spoiling me with this attention)
 
Have got some code running based upon your suggestion....

But you could do something like putting two images from from the control toolbox
toolbar over the cells.
Click to expand...

Images 2 to 10 are the smaller ones which are contained within Image1 - as
per my code:

Two points arise:

(1) For neatness, is there a method whereby the code for buttons 2 to 10
can be simplified? Perhaps a button array?
(2) The functionality is exactly as required - hidden text pops up when the
mouse passes acros the respective image - but if the user clicks the mouse
over the hidden cell - far from selecting the cell - the event seems to
trigger an endless loop associated with the images until the mouse is moved
away. Can this be disabled?

This is what I am running:

Option Explicit

Private Sub Image1_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
' Reset to Grey text
Range("A4:I4").Font.ColorIndex = 15
End Sub

Private Sub Image2_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
Range("A4").Font.ColorIndex = 1
End Sub
Private Sub Image3_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
Range("B4").Font.ColorIndex = 1
End Sub
Private Sub Image4_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
Range("C4").Font.ColorIndex = 1
End Sub
Private Sub Image5_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
Range("D4").Font.ColorIndex = 1
End Sub
Private Sub Image6_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
Range("E4").Font.ColorIndex = 1
End Sub
Private Sub Image7_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
Range("F4").Font.ColorIndex = 1
End Sub
Private Sub Image8_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
Range("G4").Font.ColorIndex = 1
End Sub
Private Sub Image9_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
Range("H4").Font.ColorIndex = 1
End Sub
Private Sub Image10_MouseMove(ByVal Button As Integer, _
ByVal Shift As Integer, ByVal X As Single, _
ByVal Y As Single)
Range("I4").Font.ColorIndex = 1
End Sub
 
Back
Top