Prioritize List on a Worksheet
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.
Then put this code in the sheet's module:
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.
Murray Shactman:
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.
13 June 2005, 11:07 pmBrett Hamilton:
Nothing happens. Do you have to link the control and the code?
14 June 2005, 7:28 amJuan Pablo González:
Brett,
Did you put the code in the sheet module or in a standard module ? and also, is your SpinButton named 'SpinButton1' ?
14 June 2005, 7:41 amBrett Hamilton:
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?
14 June 2005, 8:06 amBrett Hamilton:
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.
14 June 2005, 8:19 amDick Kusleika:
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.
14 June 2005, 8:22 amFred B.:
I think this is a useful little trick. Thanks.
15 June 2005, 2:01 pmVasant:
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.
15 June 2005, 6:15 pmJon Peltier:
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
16 June 2005, 5:45 amVasant:
Correction - when I said Shift-select; I meant select and then Shift-drag.
17 June 2005, 6:04 amRon:
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?
17 June 2005, 10:57 am--Ron
Dick Kusleika:
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.
17 June 2005, 2:49 pmEric White:
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
20 June 2005, 12:56 pmRon:
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?
22 June 2005, 4:10 pm--Ron