Tuesday, June 17, 2014

How to comment and uncomment blocks of code in the Office VBA Editor

Good to know.


One way to quickly comment and uncomment blocks of code is to add the Comment Block and Uncomment Block buttons to your toolbar.

Go to: View -> Toolbars -> Customize

Next, go to the Commands tab and then the Edit category:

Select the Comment Block and drag to the toolbar:

Do the same for the Uncomment Block.

Friday, June 6, 2014

Change Default Font and Size for Text Boxes in Excel 2010

I had someone ask me if I knew how to change the default text font and size for inserting a text box.  Of course it wasn't too straightforward, but with a little Googling I was able to find this!


Here is one way to change the default font. It does not allow to you to change the default size!
No it's by design rather than a bug. Unfortunately when you copy and paste within a text box, Excel by default formats the pasted text using 'body font' from the Fonts Theme set for the workbook, and the standard font size that is set in Excel options, rather than keeping the format of the source, which is annoying. Calibri is body font in the default built-in Fonts theme called 'Office'. The easist way to change this behaviour, assuming you only want all text boxes to use Arial, is to create a special theme with the font you want. Do this from the Page Layout ribbon, select Fonts under Themes, and 'Create New Theme Fonts'. Edit the Body font, then name and save the Theme. Now select any of text box and apply the customised Font theme. The font in all text boxes should adjust to the theme font. Copying and pasting within the text boxes will also use this font. In regard to the font size, this is based on the default font size set in Excel Options. If you want to use this customised theme for all new workbooks, then you can create a customised template with the your customised theme pre-selected: Open a blank workbook, apply your custom Fonts theme and then save is as a template called Book.xltx, in your XLStart folder: Save As, Select Save As Type - Excel Template (*.xltx) type, change the name to Book, and locate it in the XLStart folder. In my PC (Vista) this is:C:\Users\UserName\AppData\Roaming\Microsoft\Excel\XLSTART\ All new workbooks created will then be based on this template.

This is the trick you must use to change both! You simply create an initial text box the with the font and size you wish. Then right-click on the text box and choose the "Set as Default Text Box." This should allow future text boxes to be set to this new default. Found it here from this nice instructional video on YouTube!!