Navigate in Style! – An Excel VBA Add-in to help get around in a sheet

Excel Navigation
Click the Image to download the Add-in

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.

Excel Navigation

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.

  1. 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.
  2. Then I convert it into a proportion of its width or height respectively.
  3. After that, I use the proportions to decide which cell in the used range I want to Activate.
  4. I also threw in a few more lines to make sure excel does not scroll outside the used range.
  5. 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

Download

Download

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.


Update

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.

4 Comments

    • Hi Petros,

      That is a great idea. I would have never thought of that. It is great because excel scroll bars are already coded to scroll within the UsedRange (not the arrow buttons, but the rectangular bar). Once you start working with VBA, the mind sort of lapses on the more simple solutions.

      Anyway, This little device is capable of scrolling along both the axes simultaneously, which I think is cool. I got the idea from a game that my dad loves very much – Command and Conquer – Red Alert 2. This also features in graphics designing softwares like Photoshop and Illustrator.

      I do a lot of graphics designing in my free time. I post some of my designs here, http://designecdotes.blogspot.in/

      Do check it out when you get the time.

  1. Akira

    Goob job!
    Try add this on form’s code. This will add Minimize botton e will show form’s icon on taskbar:

    Private Declare Function GetWindowLong Lib “user32” Alias “GetWindowLongA” (ByVal hWnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function ShowWindow Lib “user32” (ByVal hWnd As Long, ByVal nCmdShow As Long) As Long
    Private Declare Function FindWindow Lib “user32” Alias “FindWindowA” (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Private Declare Function SetWindowLong Lib “user32” Alias “SetWindowLongA” (ByVal hWnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long

    Private Sub UserForm_Activate()
    Dim lngHwnd As Long
    Dim lngCurrentStyle As Long, lngNewStyle As Long

    If Val(Application.Version) < 9 Then
        lngHwnd = FindWindow("ThunderXFrame", Me.Caption)  'XL97
    Else
        lngHwnd = FindWindow("ThunderDFrame", Me.Caption)  'XL2000, XP, 2003?
    End If
    
    'Set Windows style to add minimize & maximize buttons
    lngCurrentStyle = GetWindowLong(lngHwnd, -16)
    lngNewStyle = lngCurrentStyle Or &H20000 'Or &H10000       'Minimize e Maximize
    lngNewStyle = lngNewStyle And Not &H10000000 And Not &H80000000
    SetWindowLong lngHwnd, -16, lngNewStyle
    
    'Set extended style to show userform on taskbar
    lngCurrentStyle = GetWindowLong(lngHwnd, -20)
    lngNewStyle = lngCurrentStyle Or &H40000
    SetWindowLong lngHwnd, -20, lngNewStyle
    ShowWindow lngHwnd, 5
    
    DoEvents
    

    End Sub

    With those additional functions you won't need to close the addin every time you want to see the whole sheet.

    • This is a great idea. Thanks for the code.
      I will add it to my form and let you know how it goes.

Comments are closed