Have you ever been frustrated about having to scroll through endless rows and columns in an Excel Spreadsheet? I have! With all the amazing touch screen devices flooding the market, having to use the scroll bar seems a bit archaic. I longed for a better solution and transformed that yearning into a fun little project. I drew inspiration from the navigator panes in graphics design suits and strategy games.
So here is how this works. You get a tiny unassuming form with a dark grey area. This area corresponds to the used range in your active spreadsheet. Clicking and dragging anywhere here would take you to the spatially corresponding cell. This comes in handy when you’d like to quickly glance through the entire sheet before sending it to your BOSS. This is a Modeless form: you can interact with your spreadsheet even when the form is active.
I urge you to download, from the section below, and experience the fun yourselves.
If you want to understand how this works, read on. The little grey area is a Label control. Almost all the Visual Basics Form controls have an X/Y coordinates as arguments for all their mouse related events.
- I used the mouse down event to capture the X and Y coordinates. Note that the top-left corner of the label is the origin.
- Then I convert it into a proportion of its width or height respectively.
- After that, I use the proportions to decide which cell in the used range I want to Activate.
- I also threw in a few more lines to make sure excel does not scroll outside the used range.
- Finally, I used a public boolean variable to make sure this works if the user clicks and drags the pointer.
Option Explicit 'Always a good practice to have this going 'Public Boolean Variable to check if the mouse button is down Public booNav As Boolean Private Sub NavPad_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 'Flip the boolean variabe to true, to indicate that the mouse button is down. booNav = True 'Declare variavles for the X/Y coordinates Dim Xpos As Integer Dim Ypos As Integer 'Compute the row and colum number inside the used range Xpos = Round(FloorCap(X * Application.ActiveSheet.UsedRange.Columns.Count / Me.NavPad.Width, 1, Application.ActiveSheet.UsedRange.Columns.Count), 0) Ypos = Round(FloorCap(Y * Application.ActiveSheet.UsedRange.Rows.Count / Me.NavPad.Height, 1, Application.ActiveSheet.UsedRange.Rows.Count), 0) 'Activate the corrosponding cell Application.ActiveSheet.UsedRange.Cells(Ypos, Xpos).Activate End Sub Private Sub NavPad_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 'The exact code from the mouse down event, but it only executes 'if the mouse moves over this control with the mouse button down. If booNav Then Dim Xpos As Integer Dim Ypos As Integer Xpos = Round(FloorCap(X * Application.ActiveSheet.UsedRange.Columns.Count / Me.NavPad.Width, 1, Application.ActiveSheet.UsedRange.Columns.Count), 0) Ypos = Round(FloorCap(Y * Application.ActiveSheet.UsedRange.Rows.Count / Me.NavPad.Height, 1, Application.ActiveSheet.UsedRange.Rows.Count), 0) Application.ActiveSheet.UsedRange.Cells(Ypos, Xpos).Activate End If End Sub Private Sub NavPad_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) 'Let excel know that the mouse button has been released, consequently preventing code in 'the mouse move event from being executed booNav = False End Sub
'==================================================================================================== 'A simple function to Floor and Cap a Value. VBA does not have the MAX and MIN functions 'And I try not to use Spreadsheet Functions in my code '==================================================================================================== Function FloorCap(ByVal WhatValue As Double, ByVal Floor As Double, ByVal Cap As Double) As Double FloorCap = WhatValue If WhatValue > Cap Then FloorCap = Cap If WhatValue < Floor Then FloorCap = Floor End Function
Please note that I have saved it as an Excel 2003 add-in. I intend to create a single Ribbon based add-in in the future, and pack it with all the neat things I create.For now, I have decided to settle with Excel 2003 add-ins for each post.
I have created another Add-in that will let you navigate between sheets and workbooks also. If you liked this post, you should check that out as well: Range Navigator – Smarter Navigation for Excel.