Rank with VBA
With the Google presentation and a video below, I demonstrate how to change the rank of an item in a table. This is particularly useful if you have a lookup table and you want to offer your user the ability to move an item up or down the list. To add this feature you need to add a new field which stores a value you can change. Sort the list ascending so that the preferred option selected by the user comes to the top. See the presentation and video together for further information.
I developed this code for the Nifty Switchboard Builder. The page data is controlled by an index which prevents duplicate values. This causes a problem when you want to move one of the switchboard page rows up or down. Let’s say you want to move record number 5 to record number 4, you can’t because you can’t change 5 to 4 because this will trigger the duplication prevention provided by the unique index. The solution is to move it to a row number not within the set of row numbers. You could invent an arbitrary figure saying 1000, however you must be sure that this 1000 will not be used by some other process.
Rank with VBA
Rank with VBA
Video 1 (7:06)
This 1st video follows along from the presentation below. The presentation goes over the basic idea, then the video goes into details about how to construct the queries required to effect the ranking. In this video I show the next step, which is to create the queries which move people up and down in a list with VBA… In the second video of (not published yet) I will show how to convert the queries into SQL string and run them with VBA.
… …