Auto Sort Columns in Database

[newqueen]

Hi All,

Please I need help with a vba code that will auto sort two columns in my database when entries are made into the database using the inbuilt Excel Userform.

I have a database with names, addresses and other details. The columns are twelve in number currently, but may increase in future. I make entries into the database using the Excel 2007 built-in User Form.

Columns 2 and 4 contain the first names and last names, respectively. And I want the database to be AUTOMATICALLY sorted based on those two columns. Column 4 to be sorted first (i.e. the last name) and column 2 second (i.e. the first name) in case there are people with the same last name. I want this automatic sorting to occur the moment the details appear in the database from the userform.

Going through the forum threads, I came across the following code (see below). Unfortunately the code below works ONLY when the workbook is opened, in addition to other areas of my need that it does not meet.

Please Login or Register  to view this content.

When I tried the code above, I realised that, although it works, it sorts only ONE column - i.e. column 4. I would need it to sort both columns 4 and 2 - in that order. Secondly, using the code above, the automatic sorting occurs ONLY when the workbook is opened. I require the automatic sorting to occur when the details appear in the database from the userform. That is to say, the moment I finish entering each record in the userform and it is transferred onto the database, the database is automatically sorted in ascending order based on columns 4 and 2, respectively.

Also, I don't know whether it is best to place the vba code in a Module or in the specific sheet where the database is.

I should be very grateful, please, for help with a vba code that meets my needs as explained above.

Thanks in anticipation.

Newqueen

[mehmetcik]

Paste the code into the sheet code module area.

[ Right click on the sheet name at the bottom of excel and select view code ]

The sort code will run whenever the sheet is changed.

You need to be careful to ensure that both column B and Column D are updated at the same time.

So you should use an array to do this.


The test macro shows an example of this it change B2 to Michael and D2 to Doe.

Please Login or Register  to view this content.

[newqueen]

Dear Mehmetcik,

Thanks for your kind response. The code that I included in my Post #1 works for one column sort. One of the problems that I wanted help on is the question of getting it to sort one additional column i.e. column 2.

However, the second and my most important issue with it is that the code in my Post #1 activates (or the sort code runs) ONLY when the sheet is opened. And with your alternative vba code, I can see that the sort code ALSO runs ONLY when the sheet is changed (or closed and opened).

I was thinking if it is possible to get a code or tweak the one in my Post #1 so that the moment each record is transferred from the Excel Userform to the database, the automatic sorting of the two columns will take place - without having to wait for the sheet to be changed or closed and opened.

I hope it is possible to achieve that.

Thanks again for your willingness to help.

newqueen

[mehmetcik]

Hi

Your code can only run when the sheet is opened.

My one will run automatically whenever the sheet is changed.

my test macro simulates your userform

the test macro changes an entry and that kicks off the sort routine, which is what you need.

If your text box changes one cell at a time then that will corrupt your spread sheet which is why I used an array.

did you try my solution?

[newqueen]

Dear Mehmetcik,

I have tried your solution. I get your point. I should be glad if you would kindly clarify a few things.

1. May I know the purpose of the Range (A2:G5). Select. I observe that when the sheet is opened, there is a triangle highlighting A2 to G5. Is that line of the code necessary? Is it possible to do away with the highlighting of the triangle? I tried changing it to A2:J5, and realised that A2 to J5 is highlited. Could you please clarify this to me.

2. I also observe from the sample file that you sent that in addition to your code (Post #2) being in Sheet 1, there are also codes on Module 1 - one of them similar to the code in Sheet 1. Should I simply copy and paste your code just in my Sheet1 or do I also have to create Module 1 and paste the same codes that you have as in the file you attached?

3. My database has 12 columns (i.e. A to L), and the columns might be increased in future if the need arises. Please, how does the code take care of this?

4. Finally, the end part of your code has the following:

Please Login or Register  to view this content.

Should I also include it in my live sheet?

Thanks for your kind help.

newqueen

[newqueen]

Dear Mehmetcik,

Thanks for your solution. You've been very magnanimous. I am very grateful to you for all your help.

newqueen