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

[atuljadhavnetafim]

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

 

[Legacy 456155]

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"

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]

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]

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?

 

[Legacy 456155]

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]

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?

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

 

[Legacy 456155]

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.

 

[Legacy 456155]

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:

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:

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)

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]

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]

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.