Tuesday, October 26, 2010

The MsgBox prompt IF…THEN…ELSE Access macro

I don’t like Microsoft Access macros as much as I like VBA – I do a lot of work in VBA and know it well – although Access macros are useful in limited, simple tasks, anything that requires VBA expressions are not fun because you have to enter a VBA expression in a non VBA IDE.

MSGBOXmacro

Notice also, the editing environment is not conducive to using VBA Enumerations in the MSGBOX expression.

Note that Microsoft Access 2010 has enhanced the Macro Designer and added more Macro commands – you can create a macro in Access 2010 that will not survive running the same file under Access 2007. If you used VBA, you will probably need to re-compile to ensure VBA binary compatibility and keep on going.

Dynamic Named Ranges in Excel the classic way...

In Excel 2007 forwards, lists that expand and contract needed OFFSET calculation. Now with the new Tables feature, we don't have to use this old method.

in reference to: Dynamic Named Ranges That Expand and Contract Automatically in Your Excel Spreadsheet (view on Google Sidewiki)

Wednesday, October 20, 2010

Watermark and Background in Excel worksheets

Funny thing about Excel backgrounds – You’d think that

Page Layout > Background

would apply a background image to the worksheet. It does and it doesn’t. It does display the graphic on-screen but not when you print. I think.

If you want a washed out watermark, what you need to do is to

  1. go to Page Header (or Page Footer?) section boxes.
  2. Insert Picture
  3. Format Picture > Picture > (Image Control) > Color > Washout

Done.