Sometimes we just want to have easy access to running a bit of VBA code, and to make it accessible to regular users not really familiar with macros. They are familiar with button, so let’s add a macro button to the “Quick Access Toolbar”.
Activate “Developer” ribbon:
First off, make sure you have “Developer” ribbon activated, by clicking on the “Office Button” and choose “Excel Options”.
Select the “Popular” tab and check “Show Developer Tab in Ribbon”. You now have the button to start Visual Basic Editor. Offcourse we could have used a shortcut, but now we can do both.
Add VBA to workbook:
Click the Developer Ribbon and start Visual Basic editor. You could also “cheat” and just use ALT – F11.
Doubleclick ThisWorkBook in the project explorer to the left and insert the VBA code you wish for the button to run.
I last used this:
Public Sub CleanSelection()
Dim Cell As Range
For Each Cell In Selection
Cell = Replace(Cell, Chr(160), ” “)
Cell = Replace(Cell, Chr(127), “”)
Cell = Replace(Cell, Chr(129), “”)
Cell = Replace(Cell, Chr(141), “”)
Cell = Replace(Cell, Chr(143), “”)
Cell = Replace(Cell, Chr(144), “”)
Cell = Replace(Cell, Chr(157), “”)
Cell = Application.WorksheetFunction.Trim(Cell)
Cell = Application.WorksheetFunction.Clean(Cell)
Next Cell
End Sub
It strips non printable characters from the selected cells. Quite nifty.
Add Macro Button to Quick Access Toolbar
Next click the “Office Button” and “Excel Options” yet again and choose the “Customize” tab this time.
In the “Choose Commands From” dropdownlist select “Macros”. Select your macro from the box below and click “Add >>”.
It is now added to the “Quick Access Toolbar”, but we want it to look a bit better don’t we? 
Click the modify button after selecting the macro in the right box.
Click “OK” and look at your shiny new button in the “Quick Access Toolbar” (default position is right next to the big Office Button)
Related posts:
0 Responses
Stay in touch with the conversation, subscribe to the RSS feed for comments on this post.