PDA

View Full Version : Solved: a "Sorting" Macro



Bern
10-12-2007, 12:15 PM
I have tried to record a macro, which I think worked...but have no idea how to install a button, and attach the macro!

Therefore...I am starting over here!

I have a sheet of data, about 30 columns wide, by 105 rows long.

I would like to add 2 buttons, the first would would allow the user to sort based on Column B (name), the second to sort based on column I (final score).

THanks

MachaMacha
10-12-2007, 01:37 PM
Bern,

I am assuming you want to sort all the other 29 columns based on column B or I in an ascending order? try this...hope it works/helps


Sub SortByName2()
'
' SortByName2 Macro
'
'
'
Range("A2:AD438").Select
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 427
ActiveWindow.ScrollRow = 426
ActiveWindow.ScrollRow = 424
ActiveWindow.ScrollRow = 422
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 415
ActiveWindow.ScrollRow = 410
ActiveWindow.ScrollRow = 405
ActiveWindow.ScrollRow = 398
ActiveWindow.ScrollRow = 391
ActiveWindow.ScrollRow = 381
ActiveWindow.ScrollRow = 367
ActiveWindow.ScrollRow = 352
ActiveWindow.ScrollRow = 334
ActiveWindow.ScrollRow = 317
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 284
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("D25").Select
ActiveSheet.Buttons.Add(528, 12, 134.25, 71.25).Select
Selection.OnAction = "SortByName2"
Selection.OnAction = "SortByName2"
End Sub




it's the same process to sort by column I Final Score.

lucas
10-12-2007, 03:13 PM
You didn't mention whether you have a header row...I assumed that you do. Create your buttons from the forms toolbar and right click on them to assign a macro...see attached.

Cyberdude
10-14-2007, 12:03 PM
As a button alternative, you might find it handy at times to create an AutoShape (any AutoShape), then assign the macro to the AutoShape by right clicking on it to get the menu that does that. You can make the AutoShape any size you might want and add some text to it, then move it to a convenient location.

Just a thought.

mdmackillop
10-14-2007, 01:08 PM
Have a look at this article (http://vbaexpress.com/forum/showthread.php?t=11142)

Bern
10-15-2007, 11:15 AM
Thanks MM.

I think I have them both up and running!

One bug....each time I click or run the macro...a new button appears on my spreadsheet?

I have buttons all over the place! LOL. Here is the code:



Sub SortByName2()
'
' SortByName2 Macro
'
'
'
Range("A5:AD438").Select
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 427
ActiveWindow.ScrollRow = 426
ActiveWindow.ScrollRow = 424
ActiveWindow.ScrollRow = 422
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 415
ActiveWindow.ScrollRow = 410
ActiveWindow.ScrollRow = 405
ActiveWindow.ScrollRow = 398
ActiveWindow.ScrollRow = 391
ActiveWindow.ScrollRow = 381
ActiveWindow.ScrollRow = 367
ActiveWindow.ScrollRow = 352
ActiveWindow.ScrollRow = 334
ActiveWindow.ScrollRow = 317
ActiveWindow.ScrollRow = 301
ActiveWindow.ScrollRow = 284
ActiveWindow.ScrollRow = 270
ActiveWindow.ScrollRow = 253
ActiveWindow.ScrollRow = 238
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 213
ActiveWindow.ScrollRow = 200
ActiveWindow.ScrollRow = 189
ActiveWindow.ScrollRow = 178
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 164
ActiveWindow.ScrollRow = 158
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 149
ActiveWindow.ScrollRow = 145
ActiveWindow.ScrollRow = 140
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 128
ActiveWindow.ScrollRow = 124
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 114
ActiveWindow.ScrollRow = 110
ActiveWindow.ScrollRow = 106
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 100
ActiveWindow.ScrollRow = 96
ActiveWindow.ScrollRow = 94
ActiveWindow.ScrollRow = 92
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 85
ActiveWindow.ScrollRow = 84
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 79
ActiveWindow.ScrollRow = 78
ActiveWindow.ScrollRow = 77
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 74
ActiveWindow.ScrollRow = 72
ActiveWindow.ScrollRow = 71
ActiveWindow.ScrollRow = 70
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 66
ActiveWindow.ScrollRow = 64
ActiveWindow.ScrollRow = 63
ActiveWindow.ScrollRow = 61
ActiveWindow.ScrollRow = 58
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 54
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 47
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 41
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 29
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 16
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("D25").Select
ActiveSheet.Buttons.Add(528, 12, 134.25, 71.25).Select
Selection.OnAction = "SortByName2"
Selection.OnAction = "SortByName2"
End Sub

Bern
10-15-2007, 11:43 AM
You didn't mention whether you have a header row...I assumed that you do. Create your buttons from the forms toolbar and right click on them to assign a macro...see attached.

Your sorting macro looks simple enough!

The rows I need to sort are B6 to B105 for name sort
and H6 to H105 for score sort.

The data part of the sheet runs A6:AM105

lucas
10-15-2007, 11:59 AM
You still don't say whether you have a header row or not and which row it is on if it exists.....

try using the macro recorder...that is how I got the code I posted.

jtrowbridge
10-15-2007, 12:44 PM
I cleaned up your code a bit. You dont need all that scroll row junk. This ought to work:


Sub SortByName2()
'
' SortByName2 Macro
Range("A5:AD438").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub

This line was cloning the button each time:

ActiveSheet.Buttons.Add(528, 12, 134.25, 71.25).Select


To make a button use the control toolbox. If you dont have it up click on View -> ToolBars -> Control Toolbox.

Click on the "Command Button" icon (looks like a grey rectangle) and then youll need to plop it on your spreadsheet. Just left click and hold and drag out a box.

Once you make the command box, right click on the box and select "View Code".

The VB editor will pop up and youll see some code like this:

Private Sub CommandButton1_Click()

End Sub


Stick your macro in there. The end result will look like this:

Private Sub CommandButton1_Click()

'
' SortByName2 Macro

Range("A5:AD438").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub



or alternatvely you can to this as long as the sorting macro is saved in the workbook and not in your personal macro workbook:

Private Sub CommandButton1_Click()

Call SortByName2

End Sub

Bern
10-15-2007, 01:38 PM
Thank you...works like a charm.

As always, that brings up a new question:

I had intended to lock the sheet so user dont screw up the calculators, and I notice I loose the use of the commandbutonns when the sheet is protected (debug error)

The first commandbutton works on cells B5:B104....which are not locked cells. The second commandbutton works on cells H5:H104...which is a locked cell.

Other cells are also locked on the sheet.

Can you still run the sort on a password protected sheet?

Tks

lucas
10-16-2007, 06:34 AM
Unprotect it before you run the code and then re-protect it after the code runs:
Sub SortB()
ActiveSheet.Unprotect
Cells.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
ActiveSheet.Protect
End Sub

Don't use buttons on the sheet...they are ugly anyway and always get in the way. Use a custom menu.

Bern
10-16-2007, 08:23 AM
Unprotect it before you run the code and then re-protect it after the code runs:
Sub SortB()
ActiveSheet.Unprotect
Cells.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("A1").Select
ActiveSheet.Protect
End Sub

Don't use buttons on the sheet...they are ugly anyway and always get in the way. Use a custom menu.


Thanks

Custom Menu?

Ok...so far so good. Can i use a password on protection and still run the code?

lucas
10-16-2007, 08:28 AM
Worksheets("MySheet").Unprotect password:="drowssap"
'your code here
Worksheets("MySheet").Protect password:="drowssap"
Be sure to protect your macro code to hide the sheet password.

Bern
10-16-2007, 10:52 AM
Worksheets("MySheet").Unprotect password:="drowssap"
'your code here
Worksheets("MySheet").Protect password:="drowssap"
Be sure to protect your macro code to hide the sheet password.

Thank you, it works great.

How do you protect macro code?

The macros do not appear in the macro list, but it does appears when i do "tool > macros > visual basic editor".....password and all!

I did not create a macro, i created the command buttons and copied and pasted code in the "view code" window.

The following is the code.


Private Sub Sortbyname_Click()
'
' SortByName2 Macro
ActiveSheet.Unprotect Password:="PASSWORD"
Range("A5:AM104").Select
Application.CutCopyMode = False
Selection.Sort Key1:=Range("B5"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
ActiveSheet.Protect Password:="PASSWORD"
End Sub

lucas
10-16-2007, 11:08 AM
How do you protect macro code?


in the vbe right click on your project in the project explorer on the left and select vba project properties...select the protection tab and check the lock for viewing checkbox and insert your password

Bern
10-16-2007, 11:24 AM
Thank you very much for your help. Sheet looks good and works well.

I really appreciate it.

Bern

:thumb

lucas
10-16-2007, 01:36 PM
Bern,
Please mark your thread solved using the thread tools at the top of the page...