Display full cell contents when mouse is over cell

Hi All

I have a sheet that contains quite long codes in individual cells. For convenience, so that all the data can be viewed on one screen, the width of the cells is smaller than the width of the data. Obviously you can see the full cell content in the formula bar, if you click on the cell but, is it possible to have the full content displayed in a separate box adjacent to the cell, or an expanded version of the cell, when the mouse enters the cell?

I know this is what happens when you have a comment in a cell, but some of these cells already use the comments feature.  

Regards

Terry
TerrygordonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

FamousMortimerCommented:
Unfortunately not really...

The closest you could get would have a form show with the full cell contents when the user clicks on a cell.

I'v attached a quick example for you to check out.
ee-test.xlsm
0
Martin LissOlder than dirtCommented:
Have you thought about formatting the cell(s) with "Shrink to fit" or making them a little taller and using "Wrap text"?
0
Martin LissOlder than dirtCommented:
If you are willing to have to click the cell and also willing to have the whole column expand then you could do this.

Put this in a module
Option Explicit

Public Const A1_WIDTH = 9

Open in new window

and then put this in your sheet's code.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(ActiveCell, Range("A1")) Is Nothing Then
    ActiveCell.Columns.AutoFit
Else
    Range("A1").Columns.ColumnWidth = A1_WIDTH
End If
End Sub

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

TerrygordonAuthor Commented:
Hi Guys

I've tried variations of each of the solutions, but nothing really does what I'm after. Guess I will have to admit defeat on this one or go for a standalone VB solution.

Not sure what to do about the points?

Regards

Terry
0
Martin LissOlder than dirtCommented:
If nobody provides a satisfactory solution then I think you can select your own "solution" and assign zero points. If not then you can Request Attention' and ask for help.

Would you consider textboxes residing on the wide data cells? They can be made to respond to the mouse.
0
TerrygordonAuthor Commented:
Hi Martin

Problem is that there are 365 rows (one for each day) and 28 columns, so that would be a lot of text boxes. But I'll have a play over the weekend and, if it works, I will accept this as a solution.

Regards

Terry
0
[ fanpages ]IT Services ConsultantCommented:
Hi Terry,

What you suggested could be possible with an Application Timer event, if...

a) The screen width & screen height were constant in all environments using the workbook, &/or the screen position of the workbook never changed (i.e. it was always at a normal windowstate, the toolbars, menu bars, & other optional bars available to be shown remained in a constant state also)

b) The row height & column width of every row/column remained static

c) The worksheet was never scrolled up, down, left or right; that is, the available view of cells remained exactly the same


I suspect that at least one of these conditions would not be able to be adhered to/would be acceptable at run-time.

...

So, how about adding an Image control to the worksheet, & set the BackStyle property to be 'Transparent'?

You can then use the Image's MoveMove() event code to track the horizontal (X) & vertical (Y) position of the mouse within the worksheet.  From these co-ordinates you can, with some calculations, based on column height & row width, determine the cell beneath the mouse pointer.

I have added some very simple code to the attached workbook to demonstrate this:

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

  On Error Resume Next

  Application.StatusBar = "X:" & CStr(X) & "  Y:" & CStr(Y)
  
End Sub

Open in new window


You will note that I have not completely covered the (single) worksheet with the Image control.  You would, in practice, only cover as much of the worksheet as you needed to "monitor".

If any other "Expert" contributing already (or otherwise) wishes to take this approach & expand on it, please do so.

BFN,

fp.
Q-28237716.xls
0
TerrygordonAuthor Commented:
Hi fp

I love the idea and have tried using your example, but the image control will not show as transparent (i.e. I can't select the cells behind it). The coordinates are showing fine on the status bar, but as soon as I click on a cell inside the control, it changes to opaque.

Would be possible to apply the same MouseMove logic to a transparent shape, e.g. rectangle 1?

Regards

Terry
0
Martin LissOlder than dirtCommented:
If you are willing to have to double click the cell then try this sample which uses one floating textbox to display the complete contents of any non-blank cell. It is a highly modified version of some code from www.contextures.com

---

Option Explicit
' Highly modified code  originally Developed by Contextures Inc.
' www.contextures.com
Private Sub txtFloat_KeyDown(ByVal _
        KeyCode As MSForms.ReturnInteger, _
        ByVal Shift As Integer)
    'Hide textbox and move to next cell on Enter and Tab
    Select Case KeyCode
        Case 9
            ActiveCell.Offset(0, 1).Activate
        Case 13
            ActiveCell.Offset(1, 0).Activate
        Case Else
            'do nothing
    End Select

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ws As Worksheet
Set ws = ActiveSheet
  On Error Resume Next
  With txtFloat
    .ListFillRange = ""
    .LinkedCell = ""
    .Visible = False
  End With
On Error GoTo errHandler
  If Target.Value <> "" Then
   Cancel = True
    Application.EnableEvents = False
    With txtFloat
      .SpecialEffect = fmSpecialEffectFlat
      .Visible = True
      .Left = Target.Left
      .Top = Target.Top
      .AutoSize = True
      .Text = Target.Value
    End With
    txtFloat.Activate
  End If
  
errHandler:
  Application.EnableEvents = True
  Exit Sub

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = False

If Application.CutCopyMode Then
  'allows copying and pasting on the worksheet
  GoTo errHandler
End If

  On Error Resume Next
  With txtFloat
    .Top = 10
    .Left = 10
    .Width = 0
    .Visible = False
    .Text = ""
  End With

errHandler:
  Application.ScreenUpdating = True
  Application.EnableEvents = True
  Exit Sub

End Sub

Open in new window


[15-Sep-2013 16:45 UK time] Code added to comment for convenience of other members ~fp. (Microsoft Excel Topic Advisor)
FloatingTextbox.xlsm
0
[ fanpages ]IT Services ConsultantCommented:
Hi again Terry,

May I suggest a little more code to make the Image control a viable option?

Taken from the attached workbook:

Option Explicit
' --------------------------------------------------------------------------------------------------------------
' [ http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28237716.html ]
'
' Question Channel: Experts Exchange > Software > Office / Productivity > Office Suites > MS Office > MS Excel
'
' ID:               Q_28237716
' Question Title:   Display full cell contents when mouse is over cell
' Question Asker:   Terrygordon                               [ http://www.experts-exchange.com/M_3589701.html ]
' Question Dated:   2013-09-12 at 15:40:27
'
' Expert Comment:   fanpages                                   [ http://www.experts-exchange.com/M_258171.html ]
' Copyright:        (c) 2013 Clearlogic Concepts (UK) Limited                           [ http://NigelLee.info ]
' --------------------------------------------------------------------------------------------------------------

Private objSelected_Cell                                As Range
Private Sub Image1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

  On Error Resume Next
  
  Me.Image1.Visible = False
  
  If Not (objSelected_Cell Is Nothing) Then
     objSelected_Cell.Select
  End If ' If Not (objSelected_Cell Is Nothing) Then
  
  Me.Image1.Visible = True

End Sub
Private Sub Image1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

  Dim strDate                                           As String
  Dim strText                                           As String
  
  On Error Resume Next
  
  Set objSelected_Cell = objConvert_XY_to_Cell(X, Y)
  
  If (objSelected_Cell Is Nothing) Then
     Application.StatusBar = False
  Else
     If objSelected_Cell.Row > 1& Then
        strDate = objSelected_Cell.Text & _
                  " " & _
                  Cells(1&, objSelected_Cell.Column).Text & _
                  " " & _
                  CStr(Year(Now()))
        
        strText = ""
        strText = WorksheetFunction.VLookup(strDate, [UK_Public_Holidays], 2, False)
        
        Application.StatusBar = IIf(Len(Trim$(objSelected_Cell.Text)) > 0, _
                                    IIf(Len(Trim$(strText)) > 0, _
                                        "*** " & strText & " ***", _
                                        strDate), _
                                    "X:" & CStr(X) & _
                                    "  Y:" & CStr(Y))
     Else
        Application.StatusBar = objSelected_Cell.Text
     End If ' If objSelected_Cell.Row > 1& Then
  End If ' If (objSelected_Cell Is Nothing) Then
  
End Sub
Private Sub Worksheet_Activate()

  On Error Resume Next
  
  Me.Image1.Left = 0
  Me.Image1.Top = 0
  Me.Image1.BackStyle = fmBackStyleTransparent
  Me.Image1.Visible = True
  
  Me.ScrollArea = "$A$1:$O$34"
  
  Application.GoTo [A1], True
  
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  On Error Resume Next
  
  Application.StatusBar = Target.Address(RowAbsolute:=False, ColumnAbsolute:=False) & " = " & Target.Text
  
End Sub
Private Function objConvert_XY_to_Cell(ByVal sngX As Single, ByVal sngY As Single) As Range

  Dim intColumn                                         As Integer
  Dim lngRow                                            As Long
  Dim objReturn                                         As Range
  
  On Error GoTo Err_objConvert_XY_to_Cell
  
  Set objReturn = Nothing
  
  intColumn = (sngX + Columns(1).ColumnWidth * 2) / (Columns(1).ColumnWidth * 6)
  
  If intColumn < 1 Then
     intColumn = 1
  End If
  
  lngRow = sngY / (Rows(1&).RowHeight + 1.8)
  
  If lngRow < 1& Then
     lngRow = 1&
  End If
  
  Set objReturn = Cells(lngRow, intColumn)
  
Exit_objConvert_XY_to_Cell:

  On Error Resume Next
  
  Set objConvert_XY_to_Cell = objReturn
  
  Exit Function
  
Err_objConvert_XY_to_Cell:

  On Error Resume Next
  
  Set objReturn = Nothing
  
  Resume Exit_objConvert_XY_to_Cell
  
End Function

Open in new window



Note: I still have reservations about individual Columns being set to different widths & individual Rows being set to different heights, but if you can ensure this will not happen, then please work on what I have provided here.

You will see a worksheet, [Q_28237716b], that contains month names in row 1 as column headings (starting in column [ B ]), & the individual dates of each respective month for 2013 in the following rows (rows 2 to 32).

The Image control is located from the top left of the worksheet (cell [A1]) to just outside cell [N39].

You will see in the named range [UK_Public_Holidays] I have summarised the UK Public Holidays for 2013.  This named range refers to [P2:Q9].  Cells [P1] & [Q1] are simply column headings for this table.

Now, if you hover in column [A] or column [N], you will see the Application Statusbar display the X & Y co-ordinates of the Image control (as was the case in the workbook I provided yesterday).  This is also the case in rows 33 to 39, & for any blank cells within the Month/Dates table (for example, an invalid date such as "30 February").

However, inside the range [B1:M32] hovering over any cell displays text within the Application Statusbar.  On row 1, this is simply the Month Name ("January" through to "December").  On rows 2 to 32, the text is the actual date that the mouse pointer is over.  If an entry is found for the specific date within the [UK_Public_Holidays] named range, then the "UK Public Holiday" text (column [Q] value) is display instead (prefixed & suffixed with three asterisks "***").

If you click any cell then the cell gains focus (unlike yesterday's workbook), & the Application Statusbar text changes again (to show the cell address, & the cell's value) to show this has happened.

The detection of the row & column from the X & Y co-ordinates may need a little work, but the fundamental principle is there if you wish to expand upon it.

Please consider this workbook somewhat of a "functional prototype".

BFN,

fp.
Q-28237716b.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
Code added to comment for convenience of other members ~fp. (Microsoft Excel Topic Advisor)

@fp: I've never seen a moderator do that before but that's OK, I just want people to know that the textbox that's used is an ActiveX textbox.
0
[ fanpages ]IT Services ConsultantCommented:
Hi Marty,

You may be seeing similar approaches more often since the recent initiative to make the question threads more self-contained; that is, with all the relevant content being within the thread, rather than being viewing either upon external sites, &/or in attached files.

Please see the most recent Newsletter, dated 11 September 2013, for further information (beneath the "Linking to off-site content" heading):

[ http://www.ee-stuff.com/Newsletter-old/091113newsletter.htm ]

We have been discussing this topic within the most recent "Excel Regulars" thread (number 35) if you wish to offer your views in greater detail.

BFN,

fp.
0
Martin LissOlder than dirtCommented:
OK but my code was not a link to an external site and I only mentioned Contextures because I didn't want to give the false impression that the original code was mine, even though I highly modified it.

[15-Sep-2013 18:40 UK time] Discussion continued in a more suitable thread; comments removed from this thread as agreed ~fp. (Community Volunteer: Topic Advisor)
0
Martin LissOlder than dirtCommented:
So are you saying that I should post all the code from any workbook that I post? I hope not because that could be very difficult and confusing in VBA-intensive solutions.

[15-Sep-2013 18:40 UK time] Discussion continued in a more suitable thread; comments removed from this thread as agreed ~fp. (Community Volunteer: Topic Advisor)
0
TerrygordonAuthor Commented:
Hi fp

This looks like it might do the trick with some modification. The principle certainly works. It will be a while before I have time to play with it in detail so, for now, I am awarding the points to you. I suspect that any follow up will most likely require a new question anyway.

Thanks

Terry
0
[ fanpages ]IT Services ConsultantCommented:
OK, Terry, thanks for closing this question.

I did make a few of the regular Microsoft Excel "Experts" aware of this thread earlier today in case they were able to offer any alternate suggestions.

Given we have not seen any additional contributions since then it may mean that nobody has read my comment yet, or that they are unable to think of any additional approaches.

If, however, I do have the time to work further on what I have provided in the coming days/weeks, I will let you know with a further comment here.

This aside, if you could quickly summarise what you feel is required to reach your eventual goal, then I may be able to offer some additional pointers prior to your subsequent question.

If not, then please don't worry.  I will hopefully see your next question related to this topic, & comment if I able to at that time.

Good luck with your project in any respect.

BFN,

fp.

PS. I did note that you graded my "solution" as 'B', but did not give me the opportunity to provide exactly what you asked for in your question:

I have a sheet that contains quite long codes in individual cells. For convenience, so that all the data can be viewed on one screen, the width of the cells is smaller than the width of the data. Obviously you can see the full cell content in the formula bar, if you click on the cell but, is it possible to have the full content displayed in a separate box adjacent to the cell, or an expanded version of the cell, when the mouse enters the cell?

I know this is what happens when you have a comment in a cell, but some of these cells already use the comments feature.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.