Show/Hide a Secret Cell
 

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.

Show/Hide a Secret Cell
 

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



"Hotbird" wrote in message
...
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.

Show/Hide a Secret Cell
 

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 Pearson" wrote in message
...
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



"Hotbird" wrote in message
...
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.

Show/Hide a Secret Cell
 

Chip didn't have:
Target.Font.Color.Index
he had
Target.Font.ColorIndex

(no dot in colorindex--for both parts)



Hotbird wrote:

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 Pearson" wrote in message
...
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



"Hotbird" wrote in message
...
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.





--

Dave Peterson

Show/Hide a Secret Cell
 

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


"Hotbird" wrote in message
...
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 Pearson" wrote in message
...
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



"Hotbird" wrote in message
...
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.

Show/Hide a Secret Cell
 

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.



Hotbird wrote:

Mny thanks to both of you: Dave and Chip, for pointing out my foolish
mistake. I now am using the following code, and have Cells A1 to I1 hidden
until they are selected. Just a last point - is there an alternative way to
change the FONT.COLORINDEX by moving the mouse position, rather than
selecting the cells? This is because the selected cell has already been
carefully chosen for the next data entry in my spreadsheet?

----- Original Message -----
From: "Dave Peterson"
Newsgroups: microsoft.public.excel.programming
Sent: Sunday, August 10, 2003 8:19 PM
Subject: Show/Hide a Secret Cell

Chip didn't have:
Target.Font.Color.Index
he had
Target.Font.ColorIndex

(no dot in colorindex--for both parts)

Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC

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.

www.cpearson.com

--

Dave Peterson

Show/Hide a Secret Cell
 

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)

"Dave Peterson" wrote in message
...
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.



www.cpearson.com

--

Dave Peterson

Show/Hide a Secret Cell
 

Have got some code running based upon your suggestion....

"Dave Peterson" wrote in message
...

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


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