I’m working on an Excel workbook that’s a database dictionary. It has a data-validation list of all the tables in the database. Picking a table in the list updates a query that returns info about that selection. I’d like to keep a history of tables I’ve already looked at and get back to them quickly by clicking buttons or picking them from a history list. So I came up with a history navigation system.

The simple example for this post lets you quickly return to years of National League home run stats that you’ve already viewed.

What Kind of History do I want?

Two familiar examples of navigation history are those in browsers and file explorers. I’m in an out of Windows File Explorer all day long and use the history a lot. I like the way it works, but it seems silly that it includes the same folder multiple times.

Firefox history doesn’t do that and just lists each visited page once. The thing that I don’t necessarily like is that if you hit the back button and then do a new search you lose the page that you “backed” from.

My Philosophy of History

After thinking about the above patterns for at least five minutes, I decided how I want history to work:

• All previous choices made are included in the history for as long as the workbook is open.
• Each previous choice is only included once.
• If it’s already in the list, it’s moved to the most recent position when it’s chosen again.

The Code

It’s pretty simple, so I’m not going to go into it. You can see it by downloading the sample below. I used Form buttons so that I could assign the same subroutine to both of them without having to use WithEvents. Most of the logic has to do with updating the data validation list that has the history and updating the Prev and Next buttons. I’ve implemented this code in my data dictionary and it’s working well.

Download

Here’s a sample workbook with the setup and code. Let me know what you think!