Solved

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

Posted on 2010-09-14
11
1,681 Views
Last Modified: 2012-08-13
We just came upon a very interesting issue.  We need the Excel sheet to have a series of rows hidden (thats easy part) when opening the workbook.  However, when the mouse hovers over those rows, we want it to display them.  When, the mouse, moves away from those rows, it should hide again.

We included a workbook with 2 worksheet.  The worksheet "Sheet with Rows hidden", is when it supposed to be hidden.  The worksheet "Sheet with Rows Visible", is when is visible (when the mouse moves over these 3 rows).
Hidden-Unhidden-EE-.xlsx
0
Comment
Question by:rayluvs
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
11 Comments
 
LVL 50
ID: 33679180
Ramante, as far as I'm aware, Excel does not have such a hover event. The only thing that appears upon mouse hover are cell comments. Other than that, the mouse pointer position does not trigger any events that could be used to start a macro.

cheers, teylyn
0
 
LVL 50

Accepted Solution

by:
Dave Brett earned 250 total points
ID: 33679242
In theory in can be done by adding three images from the control box to the areas, one from say rows 7.5 to 10.5 with the unhide code, one above row 7.5 with hide code, one below row 10.5 with hide code

This gives you acces to the MouseOver event, so Image1 for unhide would look like this.

The issue is that the code will keep on running continuously as the mouse moves over the image, so it will be disconcerting for the viewer. Using plain Grouping buttons would be easier

Cheers

Dave

Private Sub Image1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Rows("7:10").Hidden = False
End Sub

Private Sub Image2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Rows("7:10").Hidden = True
End Sub


Private Sub Image3_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
Rows("7:10").Hidden = True
End Sub

Open in new window

0
 
LVL 50

Assisted Solution

by:Dave Brett
Dave Brett earned 250 total points
ID: 33679287

Actually this works

If you mouse over the two rectangles from E7:I12 (borders left on so you can see them), you will see the top one unhides the rows, the bottom hides the rows

The Boolean variable below stops the redundant re-hide or hide, ie the screen flickering is killed

Cheers

Dave

'normal module

Public bHidden As Boolean
'worksheet code

Private Sub Image1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If bHidden Then
        Rows("8:10").Hidden = False
        bHidden = False
    End If
End Sub


Private Sub Image2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    If Not bHidden Then
        Rows("8:10").Hidden = True
        bHidden = True
    End If
End Sub

Open in new window

hide.xls
0
Office 365 Training for Admins - 7 Day Trial

Learn how to provision tenants, synchronize on-premise Active Directory, implement Single Sign-On, customize Office deployment, and protect your organization with eDiscovery and DLP policies.  Only from Platform Scholar.

 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 125 total points
ID: 33680718
Wouldn't it be easier to just test the hidden property instead of using a public variable?



Private Sub Image1_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    With Rows("8:10")
        If .Hidden Then .Hidden = False
    End With
End Sub

Private Sub Image2_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    With Rows("8:10")
        If Not .Hidden Then .Hidden = True
    End With
End Sub

Open in new window

0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33681091
Well it does save one line of code :)
0
 

Author Comment

by:rayluvs
ID: 33681191
Hey worked great!  ...but what if I want to modify those rows?
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33682234
No probs
> Hey worked great!  ...but what if I want to modify those rows?
I'm not clear?

if you want to run this on different rows then chnage

 Rows("8:10").Hidden = False
toi
 Rows("X:Z").Hidden = False
where X:Z is your row range, and resize the images
 

Cheers

Dave

0
 

Author Comment

by:rayluvs
ID: 33683122
No I meant if I wanted to modify those cells.  If we need to change a calculation or add more columns, etc.  I tried to modify and won't let me.
0
 
LVL 6

Assisted Solution

by:sijpie
sijpie earned 125 total points
ID: 33727621
ramante, Yes it will, but don't click on the image with your mouse: unhide the rows by hovering over, and then move left or right to click on a cell not underneath the image, and /or use the cursor keys to move the cursor into the cell to change.

Brettdj, excellent little 'workaround' you thought of here. I was also going to post about checking the hidden property instead of a public variable, saw that rorya beat me to it...
0
 
LVL 50

Expert Comment

by:Dave Brett
ID: 33729895
>  I meant if I wanted to modify those cells.  If we need to change a  calculation or add more columns, etc.  I tried to modify and won't let  me

I'd missed your reply.

If the images are preventing you from editing the cells then you can drag the images away from the cells, then edit the cells and drag the images back to the position

Was that your issue?

Cheers

Dave
0
 

Author Comment

by:rayluvs
ID: 33733110
sijpie, it worked.   So in conclusion, when changing the "hidden" rows, I just hover over to display the rows, the with the cursor, move to the cells and change any info needed.

Thanx
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question