Excel VBA Spin Buttons

Follow @empiremagazine

Forum Home

Register for Free!

Log In

Moderator Tickets

FAQ

Users Online

Excel VBA Spin Buttons

Logged in as: Guest

Printable Version

All Forums >> [Film Forums] >> Knowledge Base >> Excel VBA Spin Buttons

Page: [1]

Message

<< Older Topic Newer Topic >>

Excel VBA Spin Buttons - 21/2/2012 4:24:04 PM

Y2Neildotcom

Posts: 902
Joined: 29/9/2006
From: Worcester

Hi folks,

I have a very large spreadsheet that lists a number of jobs to be sent to a printer.

I want the user to be able to order these jobs as they like by using a simple spin button up and down.

(although if there is a better method I'm open to hearing it)

Basically I have rows of data and I just want to be able to select a cell in that row and move it up or down one row depending on the spin button pushed.

I was toying around with the following but it doesn't want to work.

Any ideas?

    'Move Up
    Dim NextRow As Integer
    NextRow = ActiveCell.Select.EntireRow - 1
    ActiveCell.Select.EntireRow
    Selection.Cut
    Rows(NextRow).Select
    Selection.Insert Shift:=xlDown
    range("A1").Select

Many thanks,

_____________________________

| www.y2neil.com |

"You kill or you die. Or you die then you kill".

"Your B.F's about to get F'd in the B!"

Post #: 1

RE: Excel VBA Spin Buttons - 21/2/2012 6:18:13 PM

Captain Black

Posts: 6328
Joined: 30/9/2005

This uses the SpinUp() and SpinDown() events, give it a try. (I promise nothing!

)


Private Sub cmdSpinner_SpinDown()
Dim TargetRow As Integer
Dim ThisRow As Integer
ThisRow = ActiveCell.Row
TargetRow = ThisRow - 1
Rows(ThisRow).Select
Selection.Cut
Rows(TargetRow).Select
Selection.Insert Shift:=xlDown
Selection.Cut
Rows(ThisRow).Select
Selection.Insert Shift:=xlDown
End Sub

Private Sub cmdSpinner_SpinUp()
Dim TargetRow As Integer
Dim ThisRow As Integer
ThisRow = ActiveCell.Row
TargetRow = ThisRow + 1
Rows(ThisRow).Select
Selection.Cut
Rows(TargetRow).Select
Selection.Insert Shift:=xlDown
Selection.Cut
Rows(ThisRow).Select
Selection.Insert Shift:=xlDown
End Sub

_____________________________

Invisiotext:

[color=#F1F1F1]text[/color]

(in reply to Y2Neildotcom)

Post #: 2

RE: Excel VBA Spin Buttons - 22/2/2012 9:13:05 AM

Y2Neildotcom

Posts: 902
Joined: 29/9/2006
From: Worcester

That is cracking! Works a treat!

(although the spin up is moving it down and vice versa, simple change)

Thanks very much!!

_____________________________

| www.y2neil.com |

"You kill or you die. Or you die then you kill".

"Your B.F's about to get F'd in the B!"

(in reply to Captain Black)

Post #: 3