Prioritize List on a Worksheet

In a previous Prioritize List on a Worksheet, I showed how to use a spin button to move values up or down. This code does the same thing, but moves the whole row rather than just one value. I tried to incorporate Eric’s comment, but it wasn’t any move efficient because I was either cutting the active row or the destination row.

Also, I lost the spin button probably because I had “Move and Resize with Cells” set. Rather than change that, I just forced the width of the spin button when I made it visible.

Private Sub SpinButton1_SpinDown()
   
    Dim rDest As Range
   
    ‘Make sure not on the last row
   If ActiveCell.Row < ActiveSheet.Rows.Count Then
       
        ‘set the destination cell
       Set rDest = ActiveCell.Offset(1, 0)
       
        ‘don’t move off the list
       If Not IsEmpty(rDest.Value) Then
       
            ‘cut the below cell and insert into activecell
           rDest.EntireRow.Cut
            ActiveCell.EntireRow.Insert xlShiftDown
        End If
    End If
   
End Sub
 
Private Sub SpinButton1_SpinUp()
   
    Dim rDest As Range
   
    If ActiveCell.Row > 1 Then
        Set rDest = ActiveCell.Offset(-1, 0)
        If Not IsEmpty(rDest.Value) Then
       
            ‘cut the activerow and insert above
           ActiveCell.EntireRow.Cut
            rDest.EntireRow.Insert xlShiftDown
        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
            .Width = 15
        End With
    Else
        Me.SpinButton1.Visible = False
    End If
   
End Sub
Posted in Uncategorized

2 thoughts on “Prioritize List on a Worksheet

  1. Awesome!
    I used it without Target.Column = 1 in Worksheet_SelectionChange, thanx for sharing!


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

Leave a Reply

Your email address will not be published.