Prioritize List on a Worksheet

Posted on by Dick Kusleika

Jed asks:

I’ve got a spreadsheet that is essentially a list of items in priority order. I’d like to be able to add buttons to each row that will enable me to move that row up or down. Is this possible?

Here’s the way I would do it: I would put a SpinButton control on the worksheet and the user would use that to move cells. Assume you have a list in A1:A5 and you want to allow the user to order the list however they want. First, put a SpinButton control (from the Control Toolbox) on the sheet.

Excel range with adjacent spin button

Then put this code in the sheet’s module:

Private Sub SpinButton1_SpinDown()
 
    Dim vTemp As Variant
   
    If ActiveCell.Row < ActiveSheet.Rows.Count Then
        If Not IsEmpty(ActiveCell.Offset(1, 0)) Then
            vTemp = ActiveCell.Offset(1, 0).Formula
            ActiveCell.Offset(1, 0).Formula = ActiveCell.Formula
            ActiveCell.Formula = vTemp
            ActiveCell.Offset(1, 0).Select
        End If
    End If
   
End Sub
 
Private Sub SpinButton1_SpinUp()
   
    Dim vTemp As Variant
   
    If ActiveCell.Row > 1 Then
        If Not IsEmpty(ActiveCell.Offset(-1, 0)) Then
            vTemp = ActiveCell.Offset(-1, 0).Formula
            ActiveCell.Offset(-1, 0).Formula = ActiveCell.Formula
            ActiveCell.Formula = vTemp
            ActiveCell.Offset(-1, 0).Select
        End If
    End If
   
End Sub
 
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 
    If Target.Column = 1 And Not IsEmpty(Target) Then
        With Me.SpinButton1
            .Visible = True
            .Top = Target.Top + (Target.Height / 2) – (.Height / 2)
            .Left = Target.Left + Target.Width + 10
        End With
    Else
        Me.SpinButton1.Visible = False
    End If
   
End Sub

The SelectionChange event hides or shows (and positions) the SpinButton when a non-empty cell in Column A is selected. This way, you only need one control on the worksheet. The SpinUp and SpinDown events trade values with the cell above or below, respectively.

There are a couple of error checks in the SpinUp and SpinDown events. Look at the SpinUp event, for example. If A1 is the ActiveCell, then you can’t spin up, so the code only runs if the row is two or greater. Next, it doesn’t allow a trade with an empty cell. It assumes that empty cells are the borders of the list.

Okay, now the funky part. Select cell A1. Click the Up button of the SpinButton. With the arrow keys, not the mouse, select A2. Did the values from A1 and A2 swap? They did for me. When I put break points in the code, everything works fine.

Posted in Uncategorized

17 thoughts on “Prioritize List on a Worksheet”

  1. Or, you can create a UserForm and create a temporary array in one ListBox and select an item and increase or decrease its item number in the ListBox with an “UP” CommandButton or a “Down” CommandButton.

    I did it that way because I had a table with 52 columns and I wanted the ability to change the order of each row (with the 52 columns.

  3. Brett,

    Did you put the code in the sheet module or in a standard module ? and also, is your SpinButton named ‘SpinButton1’ ?

  4. Juan Pablo,

    It’s in Sheet1 and the list is in sheet1 and the button is called Forms.SpinButton.1 and shows SpinButton1 in the name box. What’s up?

  5. Juan Pablo,

    Thanks for responding so quickly. I’m embarrassed to say what the problem was. Too many sessions of Excel going at once. It works fine when everything is in the same workbook. And thanks, Dick, for the great site.

  6. Thanks, Brett. I’ll say it – you were using the SpinButton from the Forms toolbar. Nothing to be embarrassed about. I’ve done far worse. :)

  8. Perhaps I’m missing something, but why not just Shift-select the cell or row and then drag it up or down and drop it where you want? Maybe not as elegant as the spin button routine, but certainly more straightforward and probably faster.

  9. Vasant –

    More robust, too, but definitely not as sexy. And this is a cool way to build a little user control into a dictator app.

    – Jon

  11. Dick, I’m just not getting it. I tried it and was sure to use the Control Toolbox instead of the Forms Toolbox. My button is called SpinButton1. When I click on the button, the formula bar says =EMBED(“Forms.SpinButton.1?,””) but I swear I used the Control Toolbox to create the spin button. Any suggestions?
    –Ron

  12. Ron: Make sure the code is in the sheet’s module. Right click on the sheet’s tab and choose View code. Then, make sure you are out of “design-mode” by clicking the design button on the control toolbox.

  13. Let’s be more efficient: Write a single sub incorporating the central code from above, passing it (+)1 or -1 from the SpinUp or SpinDown events to use as arguments in the “Offset” lines, i.e.,

    Sub MoveValue(intMoveDir as Integer)
    ‘etc.
    If Not IsEmpty(ActiveCell.Offset(intMoveDir, 0)) Then
    ‘etc.
    End Sub

    -EW

  14. Thanks Dick, I wasn’t out of “Design Mode”. Works great! It would be even better if it moved the whole row instead of just a single cell. Would you modify the offset() property to do this or use a row() function?
    –Ron

  15. HI, aware that the above comments are veery old, however i have a question, how do you extend the code such that the entire row will move up and down with the spin button?

    also there is a bug, in that if you highlight the entire row the spin button disappears, untill you reclick the design button

  16. Hello, I need to edit this great script to the shifting of three active cells A1,B1,C1 multiply (in a define range).

    THX

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.