Navigate in Style! – An Excel VBA UserForm to help get around


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 and experience the fun yourselves. Download Excel Nav V1.01.xla from Dropbox. Please note that I have saved it as an Excel 2003 add-in, because it is easier to distribute. I intend to create a single Ribbon based add-in in the future, and pack it with all the neat things I create, and host it in Dropbox. For now, I have decided to settle with Excel 2003 add-ins  for each post.

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

Will you Navigate in Style?

Advertisements

5 thoughts on “Navigate in Style! – An Excel VBA UserForm to help get around

  1. 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) &lt; 9 Then
        lngHwnd = FindWindow(&quot;ThunderXFrame&quot;, Me.Caption)  &#039;XL97
    Else
        lngHwnd = FindWindow(&quot;ThunderDFrame&quot;, Me.Caption)  &#039;XL2000, XP, 2003?
    End If
    
    &#039;Set Windows style to add minimize &amp; maximize buttons
    lngCurrentStyle = GetWindowLong(lngHwnd, -16)
    lngNewStyle = lngCurrentStyle Or &amp;H20000 &#039;Or &amp;H10000       &#039;Minimize e Maximize
    lngNewStyle = lngNewStyle And Not &amp;H10000000 And Not &amp;H80000000
    SetWindowLong lngHwnd, -16, lngNewStyle
    
    &#039;Set extended style to show userform on taskbar
    lngCurrentStyle = GetWindowLong(lngHwnd, -20)
    lngNewStyle = lngCurrentStyle Or &amp;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.

    Like

  2. Pingback: Range Navigator – Smarter Navigation for Excel | Struggling To Excel

    • 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.

      Like

If you liked it, let me know. If you didn't make sure you let me know!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s