highlight cells

Microsoft Excel 2003 (full product)
February 17, 2010 at 11:25:23
Specs: xp tablet, 1gig
I would like to have a spreed sheet so as I scroll over the cell the contents show in a pop up bubble

See More: highlight cells

Report •

#1
February 17, 2010 at 14:08:11
You could probably do something similar to what you want using cell Comments

Simply insert the contents of the cell into the comments field, but you will also need someone more versed in VBA then me to do it.

MIKE

http://www.skeptic.com/


Report •

#2
February 17, 2010 at 14:13:13
Hi,

I am not aware of a way of creating pop-ups as you scroll across a spreadsheet - but I would be happy to be proved wrong.

As an alternative, I created a Text Box and using the Selection change event, the text in each cell is shown in the text box.

Create a new Text box, and check that it is named "Text Box 1", if not, change the name in the code to match.

Set the text box font to a larger size to 'magnify' the contents.

The following code displays the cell's text or concatenates contents if more than one cell is selected.

Here is the code which goes in the Worksheet object - Right-click the worksheet name and select View Code.
Paste this into the code window.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngCell As Range
Dim strDisp As String

With ActiveSheet.Shapes("Text Box 1")
    If Target.Cells.Count = 1 Then
    .DrawingObject.Text = Target.Text
    Else
    For Each rngCell In Target.Cells()
        strDisp = strDisp & " " & rngCell.Text
    Next rngCell
    .DrawingObject.Text = strDisp
    End If
End With
End Sub

Regards


Report •

#3
February 17, 2010 at 15:11:32
Humar,

There not exactly pop-ups, but if you add a Comment to a cell, when you scroll across the cell, the comment pops up.

So if you can take the contents of the cell and insert them in the Comment you have something roughly like what the OP is looking for.

Not being well versed in VBA I don't know where to start.

MIKE

http://www.skeptic.com/


Report •

Related Solutions

#4
February 17, 2010 at 15:13:24
Hi,

Thanks to Mike for the suggestion to use Comments.

Here is a quick bit of code to put the text into Comments.

I haven't tried it extensively so it may be a bit buggy.

It goes into the Worksheet object.
Note that there is variable defined before the sub starts.

Dim rngLastCell As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rngCell As Range
Dim strDisp As String

On Error GoTo ErrHnd

'clear last cell comments (if there are any)
If Not rngLastCell Is Nothing Then
    rngLastCell.Comment.Delete
End If

'keep last cell address or top left cell in range
Set rngLastCell = Target.Range("A1")

    If Target.Cells.Count = 1 Then
        If Not Target.Comment Is Nothing Then
            Target.Comment.Delete
        End If
        Target.AddComment Target.Text
        Else
        For Each rngCell In Target.Cells()
            strDisp = strDisp & " " & rngCell.Text
        Next rngCell
            If Not Target.Range("A1").Comment Is Nothing Then
                Target.Range("A1").Comment.Delete
            End If
        Target.Range("A1").AddComment strDisp
    End If
Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub

Regards


Report •

#5
February 17, 2010 at 16:23:30
Humar,

Works like a charm.....

One thing that could be improved, the font size on my system is set at 8, is there a way that could be increased so as to 'magnify' the contents?

MIKE

http://www.skeptic.com/


Report •

#6
February 18, 2010 at 04:31:35
Hi Mike,

In response to your suggestion about font size I have added three 'features'
Font size
Transparency and
Autosize

The comment now autosizes to the amount of text
The font size has to be changed in two places in the code - the following uses 12 point.

Transparency (0 opaque to 1 transparent) has to be changed in two places in the code. - the following uses 0.125, for a hint of transparency.

(I have included more comments in the code)

Dim rngLastCell As Range
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Target2 As Range
Dim rngCell As Range
Dim strDisp As String

On Error GoTo ErrHnd

'delete last selected cell's Comments (if there was one)
If Not rngLastCell Is Nothing Then
    rngLastCell.Comment.Delete
End If

'keep last cell
Set rngLastCell = Target.Range("A1")
    
'test if one or more than 1 cell selected
If Target.Cells.Count = 1 Then
    'only 1 cell
    'delete existing Comment Box for this cell
    'if there was one
    If Not Target.Comment Is Nothing Then
        Target.Comment.Delete
    End If
    'add Comment box with cell text
    Target.AddComment Target.Text
    'set Comment box features
    Target.Comment.Shape.Fill.Transparency = 0.125
    Target.Comment.Shape.DrawingObject.Font.Size = 12
    Target.Comment.Shape.DrawingObject.AutoSize = True
    Else
    'more than 1 cell selected
    'work with the top left cell in the selected range
    Set Target2 = Target.Range("A1")
    'get the text from all the selected cells
    For Each rngCell In Target.Cells()
        strDisp = strDisp & " " & rngCell.Text
    Next rngCell
    'delete existing Comment Box for this cell
    'if there was one
    If Not Target2.Comment Is Nothing Then
        Target2.Comment.Delete
    End If
    'add Comment box with combined cell text
    Target2.AddComment strDisp
    'set Comment box features
    Target2.Comment.Shape.Fill.Transparency = 0.125
    Target2.Comment.Shape.DrawingObject.Font.Size = 12
    Target2.Comment.Shape.DrawingObject.AutoSize = True
End If
Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub

Regards

Humar


Report •

#7
February 18, 2010 at 06:48:49
Excellent.

Hope the OP likes it.

MIKE

http://www.skeptic.com/


Report •

#8
February 18, 2010 at 07:16:56
Hi,

This contains two improvements and some minor modifications.
1. The text in the comment cell is wrapped if it exceeds a certain number of characters (tested after each cell's contents added) - currently set at 30 characters
2. Commenting can be flip-flopped on or off.
Select the whole worksheet using the square between the column and row headings. This turns off the comment pop-ups. Select the whole worksheet again and it turns them on.
(This is actually set at any selection of 1000 or more cells).

Other mods:
1. For multiple cell selection, the maximum displayed is the contents of 500 cells. This can be changed in the code as required.
2. For multiple cell selections, contents of consecutive cells are separated by a comma

Dim rngLastCell As Range
Dim blnComsOn As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Target2 As Range
Dim rngCell As Range
Dim strDisp As String
Dim intLen As Integer

On Error Resume Next

'delete last selected cell's Comments (if there was one)
If Not rngLastCell Is Nothing Then
    rngLastCell.Comment.Delete
End If

On Error GoTo ErrHnd

'flip-flop Comments On/Off if >1000 cells selected
If Target.Cells.Count > 1000 And blnComsOn = True Then
    'Comments Off
    blnComsOn = False
    ElseIf Target.Cells.Count > 1000 Then
    'Comments on
    blnComsOn = True
End If

'only apply comments if Comments are On
If blnComsOn = True And Target.Cells.Count < 500 Then
    'keep last cell
    Set rngLastCell = Target.Range("A1")
        
    'test if one or more than 1 cell selected
    If Target.Cells.Count = 1 Then
        'only 1 cell
        'delete existing Comment Box for this cell
        'if there was one
        If Not Target.Comment Is Nothing Then
            Target.Comment.Delete
        End If
        'add Comment box with cell text
        Target.AddComment Target.Text
        'set Comment box features
        Target.Comment.Shape.Fill.Transparency = 0.125
        Target.Comment.Shape.DrawingObject.Font.Size = 12
        Target.Comment.Shape.DrawingObject.AutoSize = True
        Else
        'more than 1 cell selected
        'work with the top left cell in the selected range
        Set Target2 = Target.Range("A1")
        'get the text from all the selected cells
        For Each rngCell In Target.Cells()
            If rngCell.Text <> "" Then
                strDisp = strDisp & rngCell.Text & ", "
                intLen = intLen + Len(rngCell.Text)
                If intLen > 20 Then
                    strDisp = strDisp & vbCrLf
                    intLen = 0
                End If
            End If
        Next rngCell
        If Len(strDisp) > 1 Then
            strDisp = Left(strDisp, Len(strDisp) - 2)
        End If
        'Must have some text for Comment box
        If strDisp = "" Then strDisp = " "
        'delete existing Comment Box for this cell
        'if there was one
        If Not Target2.Comment Is Nothing Then
            Target2.Comment.Delete
        End If
        'add Comment box with combined cell text
        Target2.AddComment strDisp
        'set Comment box features
        Target2.Comment.Shape.Fill.Transparency = 0.125
        Target2.Comment.Shape.DrawingObject.Font.Size = 12
        Target2.Comment.Shape.DrawingObject.AutoSize = True
    End If
End If
Exit Sub

'error handler
ErrHnd:
Err.Clear
End Sub

Regards


Report •

Ask Question