use a command button to run a data sort in Excel

[livvyt]

I'm using the VB controls in Excel to make a form; the answers on Sheet 1 generate a list of 'Action Points' on Sheet 2 using Excel formulae (mostly the 'IF' function). The Action Points are only generated if the user chooses 'No' on Sheet 1, which means that there can be big gaps in the Action Points list.

I'd like to find a way of running a Data Sort to remove the blank lines (so the user has a neat list of points to follow); I know this can be done from the main toolbar (under Data), but is there a way of triggering the Sort when a specific Command Button/ Hyperlink/ Cell is clicked on? I don't really have any programming experience so any help would really be appreciated.

 

[DaleWatson123321]

livvyt,

An even "cleaner", more effective solution would be to extract the non-blank rows to a separate sheet.

This involves use of Excel's Data - Filter - Advanced Filter. This is a powerful component of Excel that you will find extremely useful in this and other situations.

Most Excel users have not been using this powerful utility, because Microsoft has not provided adequate information on its use. But once you understand how effective and powerful this component is, you'll be VERY impressed.

If you'd like help with a "jump start", the best approach would be for you to email me your file. I'll then insert the proper functionality to provide the results you specified, and return the file. If you happen to have any sensitive data, replace it with fictitious data that still reflects the type of data you're working with.

I hope this helps.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[Zathras]

You should use the macro recorder to record the code for the actual sorting.

Then you can drop a button on the worksheet and link the macro code to it. (Or you can add a button to a tool bar, but that is a little more complicated and I generally stay away from doing that.):

1. With the Excel menu bar: Click View/Toolbars/Control ToolBox.

2. Click the little icon that looks like a button ("Command Button" is displayed when the mouse hovers over it.

3. Click somewhere on the worksheet where you would like the button to appear.

4. Double-click on the new button to open the code page.

5. Type the name of your sort macro on the line between Sub and End Sub. (Hopefully you named it something other than the default "Macro1" but that's ok if you did.)

6. Click the icon in the Control Toolbox that looks like a t-square, triangle and pencil (upper-left hand corner = "Exit Design Mode&quot

7. The button is now active and should execute the sort whenever you push it.

8. To change the caption on the button from "CommandButton1" to something more user-friendly, click the icon in the Control Toolbox that looks like a hand pointing to a document ("Properties&quot, then click on your button. You should see the properties sheet. Change the entry next to "Caption" to "Sort Results" or whatever you think best, then exit design mode. (I.e., go back to step 6 (above).)

9. Finally, close the Control Toolbox and save the workbook.