PDA

View Full Version : Solved: VBA Sort



hobbiton73
02-01-2013, 09:34 AM
Hi, I wonder whether someone may be able to help me please.

I'm using this https://www.box.com/s/etxi9okhhdqs2sh258og spreadsheet to record staff resource. This is just a test template but I can replicate the same problem I'm experiencing with the full version.

In the 'This Workbook' module of the spreadsheet, I use the following code to autosort the "Input" sheet.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableCancelKey = xlDisabled
With Sheets("Input")
If .Range("A5").Value = "" Then Exit Sub
.Range("A5:AG19").Sort Key1:=Range("$A$1"), _
Order1:=xlAscending, _
Header:=xlGuess, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End With
End Sub
You'll see from the example that there are three names which were correctly sorted on closing the workbook.

The problem I have is that if I go back into the file and enter anything on any of the remaining sheets within the workbook I receive the follwoiung error:


The sort reference is not valid. Make sure that it’s within the data you want to sort, and the first Sort By box isn’t the same or blank.

I've been working on this for a few weeks now, and tried a multitude of example code I've found through research I've done, but I just can't solve the problem.

I just wondered whether someone may be able to look at this please and let me know where I'm going wrong.

Many thanks and kind regards

Chris

GTO
02-01-2013, 01:27 PM
Hi there,

Try:

Option Explicit

Private Sub Workbook_BeforeClose(Cancel As Boolean)

With ThisWorkbook.Worksheets("Input")

If .Range("A5").Value = "" Then Exit Sub

.Range("A5:AG19").Sort Key1:=.Range("A5"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom
End With
End Sub


I believe Key1 would be the issue (although I did not get an error for some reason), as one issue (for sure) is that without the dot in front of it, if the sheet "Input" is not selected when the WB is closed, the code will error. In short, you want to qualify it as belonging to this specific sheet. Also - I think the Key should be in the range to be sorted, and at the top row (or left column if sorting "sideways").

Mark

PS - I took out the last argument, DataObject1, only as I'm using Excel 2000 at the moment, and this argument did not exist in that version. You can of course put it right back in.

hobbiton73
02-02-2013, 07:27 AM
Hi @GTO, thnak you very much for taking the time to reply to my post and for the solution. It works perfectly!

Thank you also for the very helpful additional information you provided.

If I may though, could I just ask another question please?

To help users identify the next row in which they should be entering their information into, I set a predfined text of "Enter your name " in column B.

Could perhaps tell me please, would there be a way in which I can force this text value to the end of the completed records when the sorting takes place.

Many thanks and kind regards

Chris

Paul_Hossler
02-02-2013, 07:15 PM
Could perhaps tell me please, would there be a way in which I can force this text value to the end of the completed records when the sorting takes place.

It would seem that if the sortable data range can/will grow then hard-coding in ranges like .Range("A5:AG19") would not include row 20 data if someone added it

There are ways to dynamically handle that situation, but the link to the sample in #1 seems to be dead

Paul

hobbiton73
02-03-2013, 06:07 AM
Hi @Paul Hossler, thank you for taking the time to reply to my post and my apologies for the inactive link, my mistake.

Please find the revised link herehttps://www.box.com/s/0jr7j3419s8lwplev2z5

However, since I made the original post, my requirements have changed slightly and it would be unfair and detract from the orioginal post thread, if I included these here. I'm going to try and work through these, and see where I go from here.

Many thanks and kind regards

Chris