Can Excel unhide/hide rows just by hovering a mouse over it

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
266
Hi,

I have values in D4 Cell which is sum of D5:D8 and again I have value in D9 cell which is sum of D10:D13 and so on......
now I don't want to use Group/Ungroup function because there are so many items in my excel and not practice to group/ungroup each time.
so what I want, whenever I hovering mouse to D4, excel slowly unhide Row 5 to 8 and once I remove mouse from D4 excel slowly hide those row.

I know this is possible in excel but just know howwwwww.

Thanks in advance
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

dataluver

Board Regular
Joined
Jan 17, 2020
Messages
193
Office Version
365
Platform
Windows
I'm not sure how practical this is, but your post was interesting to the likes of me so I gave it a shot. :)
As far as "slowly" making the rows appear? I don't know about that.
Dump this in a standard module. Don't attempt to edit the code while the timer is running or you will surely crash that app.
Example download: see file --> "RangeFromPoint Hover Unhide.xlsm"

VBA Code:
Option Explicit

Private Type POINTAPI
    x As Long
    y As Long
End Type

Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As Long
Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As Long) As Long
Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (ByRef lpPoint As POINTAPI) As Long

Private pt As POINTAPI
Private Const interval As Long = 250

Sub StartTicking()
    SetTimer Application.hwnd, 1, interval, AddressOf TickTock
End Sub

Public Sub TickTock(ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal idEvent As LongPtr, ByVal dwTime As Long)
    On Error Resume Next
    Static isHidden As Boolean
    GetCursorPos pt
    If ActiveWindow.RangeFromPoint(pt.x, pt.y).Address(0, 0) = "D4" Then
        If Err.Number <> 0 Then Exit Sub
        If isHidden Then
            isHidden = False
            Rows("5:8").EntireRow.Hidden = False
        End If
    Else
        If Err.Number <> 0 Then Exit Sub
        If Not isHidden Then
            isHidden = True
            Rows("5:8").EntireRow.Hidden = True
        End If
    End If
End Sub

Sub StopTicking()
    KillTimer Application.hwnd, 1
End Sub
 

mark hansen

Active Member
Joined
Mar 6, 2006
Messages
487
This is an interesting question, and even more interesting solution. I just popped the code into a workbook to see how it works. I can see this as part of a dashboard to open up rows so the user can see how the value was calculated. I can't use it now, but I can certainly see where this could be useful. I'm saving this for another day.

Thanks for sharing.
Mark
 

atuljadhavnetafim

Active Member
Joined
Apr 7, 2012
Messages
266
Hi dataluver,

Thanks for your help and solution and it is working as I expected., just need more clarity.

what is the role of two black boxes "Start" and "Stop".
and when I copy-paste this code in another file it working when your file opens, once I close your file the code is not working.
secondly, when in open this file I need to click the start button then only this is working.
third, if I need to extend this code for further rows then where I need to change?
 

dataluver

Board Regular
Joined
Jan 17, 2020
Messages
193
Office Version
365
Platform
Windows
The start button starts the timer which simply checks several times per second what range your mouse is hovering over. The stop button kills the timer. Ideally, you might start the timer using an event such as workbook open or sheet activate. Also, you would typically use an event to kill the timer such as workbook close or worksheet deactivate. The example above is most basic and only accounts for a single range on the activesheet. If you want something a little more robust and useful, post some more details about your file. Post the ranges that will fire when hovered over and the resulting action that should take place such as the rows where visibility is toggled.

Thanks for your comments Mark.
 

mark hansen

Active Member
Joined
Mar 6, 2006
Messages
487
Love this idea!! dataluver, you are going to make me look like a wizard!!

I wrote the following that I plan to put in my dashboard page to start the code. The code activates only when on the Dashboard worksheet, and turns off when not on the Dashboard worksheet...

Additionally, if you click on the key cell, the expanded rows will stay open so you don't need to stay on the key cell to view the hidden rows. when you click on any other cell in the Dashboard page, it starts up again and hides the rows.

Would it be easy to look at an object such as a shape, rather than a cell?

VBA Code:
Private Sub Worksheet_Activate()
      Call StartTicking
End Sub

Private Sub Worksheet_Deactivate()
      Call StopTicking
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     Dim rng As Range
     Set rng = Range("D4")   ' Only look at that range

If Intersect(Target, rng) Is Nothing Then
      Call StartTicking
      Exit Sub
Else
      Call StopTicking
      Rows("5:8").EntireRow.Hidden = False
End If
End Sub
 

dataluver

Board Regular
Joined
Jan 17, 2020
Messages
193
Office Version
365
Platform
Windows
Would it be easy to look at an object such as a shape, rather than a cell?
I think that shapes are returned by ActiveWindow.RangeFromPoint. I'll have to see to make sure.
I'm back. Yes, shapes are returned by RangeFromPoint. So, yes.
 

dataluver

Board Regular
Joined
Jan 17, 2020
Messages
193
Office Version
365
Platform
Windows
Atul, this is more flexible. It runs two pseudo events. CellEnter and CellLeave. It also starts the timer on workbook open and kills it on close. So no more mystery buttons. :)

Example download: see file --> "RangeFromPoint 2.xlsm"

Workbook class:
VBA Code:
Private Sub Workbook_Open()
    Dim d As New Dictionary
    d.Add "B5", 0
    d.Add "D10", 0
    d.Add "F15", 0
    'note that we are using the worksheet's code name, not the "tab" name
    StartTicking d, 100, "Sheet1.CellEnter", "Sheet1.CellLeave"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    StopTicking
End Sub
Some static module:
VBA Code:
Option Explicit

Private Type POINTAPI
    x As Long
    y As Long
End Type

Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As Long
Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As Long) As Long
Private Declare PtrSafe Function GetCursorPos Lib "user32.dll" (ByRef lpPoint As POINTAPI) As Long

Private TimerID As LongPtr
Private pt As POINTAPI
Private interval As Long
Private ranges As Dictionary
Private onEnter As String
Private onLeave As String

Sub StartTicking(targets As Dictionary, pollingInterval As Long, onEnterProcedure As String, onLeaveProcedure As String)
    Set ranges = targets
    interval = pollingInterval
    onEnter = onEnterProcedure
    onLeave = onLeaveProcedure
    SetTimer Application.hwnd, 1, interval, AddressOf TickTock
End Sub

Public Sub TickTock(ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal idEvent As LongPtr, ByVal dwTime As Long)
    On Error Resume Next
    Static previousTarget As Range
    Dim currentTarget As Range
    GetCursorPos pt
    Set currentTarget = ActiveWindow.RangeFromPoint(pt.x, pt.y)

    If Err.Number <> 0 Then
        If Not previousTarget Is Nothing Then
            Application.Run onEnter, previousTarget
        End If
    Else
        If Not currentTarget.Address = previousTarget.Address Then
            If Not previousTarget Is Nothing Then
                If ranges.Exists(previousTarget.Address(0, 0)) Then Application.Run onLeave, previousTarget
                If ranges.Exists(currentTarget.Address(0, 0)) Then Application.Run onEnter, currentTarget
            End If
            Set previousTarget = currentTarget
        End If
    End If
End Sub

Sub StopTicking()
    KillTimer Application.hwnd, 1
End Sub
In worksheet: (code name Sheet1)
VBA Code:
Public Sub CellEnter(target As Range)
    [a1] = "Enter " & target.Address
    Select Case target.Address(0, 0)
        Case "B5"
            Rows("6:8").EntireRow.Hidden = False
        Case "D10"
            Rows("11:13").EntireRow.Hidden = False
        Case "F15"
            Rows("16:18").EntireRow.Hidden = False
    End Select
End Sub

Public Sub CellLeave(target As Range)
    [a2] = "Leave " & target.Address
    Select Case target.Address(0, 0)
        Case "B5"
            Rows("6:8").EntireRow.Hidden = True
        Case "D10"
            Rows("11:13").EntireRow.Hidden = True
        Case "F15"
            Rows("16:18").EntireRow.Hidden = True
    End Select
End Sub
The worksheet code is verbose, but descriptive.

Mark, what are you gonna do with the shapes? I suppose that we could tweak this for shapes?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
34,688
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Additionally, if you click on the key cell, the expanded rows will stay open so you don't need to stay on the key cell to view the hidden rows. when you click on any other cell in the Dashboard page, it starts up again and hides the rows.
Why do you need timers for that? You can just use the selectionchange event to hide and unhide the rows.

If you are going to use timers, you might want to look at a UDF called from a HYPERLINK function. That way the timer is only started when you mouse over the cell with the HYPERLINK function (the UDF can take care of unhiding the rows) and the timer proc can cancel the timer once the cursor is elsewhere.
 

mark hansen

Active Member
Joined
Mar 6, 2006
Messages
487
Dataluver, I was think a shape could add a bit more pizzazz to the appearance of the dashboard, that's all.

RoryA, yes, you can just use the selection change event, however, I do like the feature of the hover over for a quick open and close without clicking... While at the same time allowing for a click to open and stay open. This allows a bit more finesse to a dashboard to validate information with hidden rows that can easily show and hide.
 

Forum statistics

Threads
1,089,515
Messages
5,408,740
Members
403,224
Latest member
rholmesa

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top