Wednesday, July 8, 2009

Create custom buttons for your macros in Microsoft Excel




Have you created a macro that you use often? Wouldn't it be nice if there was a button on your toolbars that ran the macro so you didn't have go through all the menus every time you want to use it? Here's how to create a custom button that can be placed on any toolbar for easy access.
  1. Click Tools --> Customize...
  2. Select the Commands tab. From Categories, select Macros. Then click on, hold, and drag the Custom Button to the desired place on one of your toolbars. (The same thing can be done with the Custom Menu.)
  3. With the Customize dialogue box still open, right-click on your button to assign properties, change the icon and style, or give it a name. Click Assign Macro... to enable the button to run your custom macro when you click on it. Select the desired macro and click OK.
  4. Close the Customize dialogue box and test out your new button!
It's as easy as that!

Sort your Excel worksheets alphanumerically

Doesn't it seem like sorting worksheets alphanumerically should be a built-in function of Microsoft Excel? Well, stop looking in Excel for a tool or option to do it, because it doesn't exist. Unfortunately, you have to create a macro. When you create a macro, you are telling Excel to memorize a set of commands and carry them out in sequence. Fortunately, Microsoft has published the commands in programming code that will help us make the macro. "I'm not a programmer!" you say? Hakuna Matata (my kids have been watching the Lion King lately..."it means no worries"), you don't need to be a programmer to make this work. I'll walk you through the process step-by-step.

First, highlight and copy the following code to your clipboard.


Sub Sort_Active_Book()
Dim i As Integer
Dim j As Integer
Dim iAnswer As VbMsgBoxResult
'
' Prompt the user as which direction they wish to
' sort the worksheets.
'
iAnswer = MsgBox("Sort Sheets in Ascending Order?" & Chr(10) _
& "Clicking No will sort in Descending Order", _
vbYesNoCancel + vbQuestion + vbDefaultButton1, "Sort Worksheets")
For i = 1 To Sheets.Count
For j = 1 To Sheets.Count - 1
'
' If the answer is Yes, then sort in ascending order.
'
If iAnswer = vbYes Then
If UCase$(Sheets(j).Name) > UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
'
' If the answer is No, then sort in descending order.
'
ElseIf iAnswer = vbNo Then
If UCase$(Sheets(j).Name) < UCase$(Sheets(j + 1).Name) Then
Sheets(j).Move After:=Sheets(j + 1)
End If
End If
Next j
Next i
End Sub


Next, go to Microsoft Excel and from the menu at the top click Tools --> Macro --> Record New Macro.... Name the macro anything (SortSheets, for example), and tell it to store it in the Personal Macro Workbook and click OK. (We save it to the Personal Workbook to make the macro available in any Excel file you may work with.) The ONLY thing you should do after you have started recording the macro is stop recording the macro. (Just click the stop button that appears on the screen.)



Now that you have created the macro, click on Tools --> Macro --> Macros..., select your macro and click Edit. You should get an error that says you "Cannot edit a macro in a hidden workbook." Click Cancel and then choose Window from the menu and select Unhide.... Choose the PERSONAL workbook and click Unhide. Now go back to your Macro and click Edit. It should open up the Visual Basic Editor.



Once you're in the Visual Basic Editor, delete the text in the window that is open and paste the code you copied earlier in its place. Click File --> Close and Return to Microsoft Excel.


Congratulations! You can now run the macro anytime! Just go to Tools --> Macro --> Macros..., then select your macro and click Run. Your worksheets will be sorted!


If you'd like, you can create a customized button that you can place on your toolbar for easy access. See my article on creating customized buttons in Microsoft Office for more information.

One last thing not to be left undone!!! Before you close Excel, you need to hide the Personal Workbook again. If you don't it, the next time you open Excel, it will open to the Personal Workbook instead of a new workbook. Click Window --> Hide. When you close Excel, be sure to save your Personal Workbook.