Auto sorting as data is entered

**drgogo** · 04-21-2009, 03:38 PM

I have asked for help on this question many times but no answer, So i came up with another idea, could this be done using VBA code.

The problem,

I have a sheet for entering data, that needs to be sorted, but there are some columns that are locked and the sheet is protected. At the moment i know that you cant sort across locked cells, so some data is not sorting.

So i thought that cos the data that needs sorting has a ref number that is could be sorted as the data is entered, by a set critiria.

I have attached a dummy sheet showing before and after, unfortunatley due to goverment policy, i can not post the actual sheet onto a public forum.
there is no formula within the sheet, it just shows how the data (top of sheet) is entered at first and the second table (bottom sheet) shows how it looks once sorted, This is what im trying to achieve automatically if possible.

it is sorted by service number first then destination. but if poss has to sort the comments cells as well, but these as you will see are the other side of the locked cells.

I hope i have explained it enuff for an answer.

Hope someone can help. Oh btw its Excel 2003 im using

Gareth

**slx** · 04-22-2009, 01:26 PM

So are you trying to sort the remaining data that is unlocked?

I think a quick solution would be just to have a script that copies all values in protected sheet moves it all to a new sheet that's unprotected and autosort there once you are done with all the data entry.

**JBeaucaire** · 04-22-2009, 04:36 PM

Actually, you can sort your data with locked cells, you just have to set that flag properly when you protect your sheet.

Click on TOOLS > PROTECTION > Protect Sheet and make sure you click on the SORT tab.

**drgogo** · 04-24-2009, 07:06 AM

I have tried that, but if you have locked cells and protected sheet, this will not work. What im trying to do is have the user select what parts they want to sort, so not a standard sort. Its a user defined sort.

So your solution does not work as i have tried it a few times.

Thanks anyway for the help

**JBeaucaire** · 04-24-2009, 11:48 AM

Alright, a different tack then. If you want to sort select sections of a protected sheet, use a macro to do it since a macro can operate on a protected sheet is the UserInterfaceOnly flag is set on the protection.

Add a "sort" command button to your worksheet and paste in this code:

Please Login or Register  to view this content.

If the sheet is PASSWORD protected, you can add in the password, too.

Please Login or Register  to view this content.

NOTE: a sheet protected this way usually CAN'T be unprotected from the worksheet menus at all, you will need to insure there is another macro you can activate to unprotect the sheet.

**drgogo** · 04-27-2009, 04:25 AM

Thanks for that code

I have enetred it in as a control button in a menu on the tool bar.
For some reason a tick keeps appearing with the menu and i get an error on

order1:=x1ascending

A couple of question, when i get this working, can i define the columns on which to sort by, as i need them to be sorted by one column first then another.

Also will this allow the user to sort locked cells within the protected sheet as well..??

As the main area to be sorted includes a couple of locked columns

G

**JBeaucaire** · 04-27-2009, 09:04 AM

There a NUMEROUS examples on the forum of autosorting, just search for autosort, sort... or Google "excel vba sort" and you'll be inundated wit usable examples.

If you look at the code I offered you'll spot the KEY1 reference which is the first sort key. You can have a Key2 and Key3.

Also, notice the line with "Userinterfaceonly:=True" in the code above? That line flags the sheet as "macros can manipulate the sheet, users cannot".

**drgogo** · 04-28-2009, 03:41 AM

Whats wrong with this code, its the one supplied earlier, but i keep getting a variable not defined, the private sub part gets highlighted in yellow and the
"x1ascending" is highlighted in blue

Please Login or Register  to view this content.

**JBeaucaire** · 04-28-2009, 08:01 AM

Not sure why, but there is definitely something wrong with your version as it wouldn't work for me either, though this does. By making it a PRIVATE sub you have to call it from another macro, which I did:

Please Login or Register  to view this content.

Works good. The only thing that even hints that there is something wrong with your sort code is that the text doesn't auto-correct...the "A" in xlascending didn't capitalize in your version, and in mine it does when you make it a small "a"...it reverts. Not sure what the issue you're having may be.

Post a sheet if you can't resolve it.

**drgogo** · 04-28-2009, 10:52 AM

That works, but unfortuately, not working how i thought it would.

The original idea was to select multipy blocks of data, which include different columns and then sort them using first one column and then another.

I have been trying to just sort the whole page by one column and then another, but the code i have been getting does not seem to work. Then i thought if i could do that then work out a way to exclude certain rows if the match a certain criteria, and hopefully keep the data in order but sorted, if you get what i mean

Thanks for the help for far

Gareth