Including sort function in command button code

**pleiadeez7** · 10-07-2008, 02:05 PM

I am new to VBA so I will express my thanks in advance to all of the experienced users who take the time to help people like ME!

I am working on a simple employee list with names, phone numbers, employee ID numbers, etc. I have created a VBA form to make new entries or change existing entries. The form has 'next', 'previous', and 'close' command buttons that work well, but I would like to have my data sort by employee name after any of the buttons are clicked. My data is in worksheet "Employees" with employee names in column A beginning on row 2. Here is what I have entered for the 'Next' command button:

Please Login or Register  to view this content.

I'm sure the sort must be something simple to do, but darned if I can figure out how!?!

Any assistance is greatly appreciated.

**Leith Ross** · 10-07-2008, 03:56 PM

Hello pleiadeez7,

Based on your name it seems you're an astronomy fan. There are 7 stars, but only 6 can be seen unaided. I have amended your macro code to sort the range. You will have to add the correct variables to point to your cells.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

**pleiadeez7** · 10-07-2008, 04:36 PM

Hi Leith -

I'm more of a Greek mythology fan. In mythology the seven stars of The Pleiades represent the seven daughters of Atlas who were changed into stars when they were pursued by Orion.

I have a question about your suggested change. If my spreadsheet continuously grows, will the range in the code adjust itself accordingly? In other words, if the range is set to A1:D15 and then a user clicks the 'add' button and enters what will be row A16, will row A16 sort? In the same vein, if I increase my range to some ridiculously high number of rows, will the sort give me a gazillion blanks?

**Leith Ross** · 10-07-2008, 05:27 PM

Hello pleiadeez7,

No, it won't but it can be made to. I simply would need to know which columns your interested in sorting and which row will always be the first row. For example, you gave "A1:D15". If the data is always in columns A through D and starts at row 1, the macro can calculate the last row. Let me know where to start and I'll revise the macro.

On a side note, the word for Pleiades in Japanese is Subaru. Ever notice the emblem?

Sincerly,
Leith Ross

**pleiadeez7** · 10-08-2008, 09:14 AM

YOU, sir, are a plethora of information! If you tell me that you know the names of the seven sisters of The Pleiades, I may have to marry you! LOL

The data is always in columns A through T, starts at row 2, and should sort on column A in ascending order. Being new to VBA I defer to whatever method you suggest, but I was wondering if the UsedRange function could be used in this instance?

On another note, could you suggest a particular training or beginners VBA book?

Many thanks!

**Leith Ross** · 10-08-2008, 01:38 PM

Hello pleiadeez7,

Being a student of both mythology and astronomy, I do know the names of the seven stars, also known as the seven sisters. Their names were Electra, Maia, Taygete, Alcyone, Celaeno, Sterope, and Merope. All still being pursued by Orion. The astronomy designation is M45.

Here is a macro to sort the range start at "A2" and ending at "Tx", where "x" is whatever the last row is.

Please Login or Register  to view this content.

Adding the Macro
1. Copy the macro above pressing the keys CTRL+C
2. Open your workbook
3. Press the keys ALT+F11 to open the Visual Basic Editor
4. Press the keys ALT+I to activate the Insert menu
5. Press M to insert a Standard Module
6. Paste the code by pressing the keys CTRL+V
7. Make any custom changes to the macro if needed at this time.
8. Save the Macro by pressing the keys CTRL+S
9. Press the keys ALT+Q to exit the Editor, and return to Excel.

To Run the Macro...
To run the macro from Excel, open the workbook, and press ALT+F8 to display the Run Macro Dialog. Double Click the macro's name to Run it.

Sincerely,
Leith Ross