Back to Excel Newsletter Archives

OzGrid Excel and VBA Newsletter

Search Tips

Newsletter Index

EXCEL TIPS AND TRICKS | EXCEL VBA TIPS AND TRICKS

EXCEL TIPS AND TRICKS

1. DOWNLOAD THE FREE OZGRID TOOLBAR . NO SPYWARE, ADWARE OR MALWARE!
2. FREE LEVEL 1 EXCEL TRAINING!
3. THIS MONTHS SPECIAL!
   SQL TESTER : The SQL Tester is an interactive Query Tool (Excel add-in) for retrieving data and displaying it on the screen in an ad hoc basis from almost all kinds of databases. The output can be placed in worksheets and added to workbooks VBA-projects. Currently only $33.00!

Get 25% Christmas Discount Coupon:
CHRS-PR4P (use it during purchasing).
Christmas Coupon is valid from 5th December to 31st December  2005 !!!

Applies to:

1. Trader Excel Package - Technical Analysis in Excel
2. Analyzer Excel
3. Downloader Excel
4. Predictor Excel
5. Classifier Excel
    

RECORDING MACROS IN EXCEL

A macro is a series of key strokes or mouse actions that are recorded and saved as a program.  You can activate your macro at any time you like to repeat your recorded steps.  Macros can be assigned to a shortcut key, an object or even to your toolbar to make them easier to access.

Because of computer viruses, the security levels set for macros in Excel are pretty strict.  As a default they are set to High. When set to High, only digitally signed macros that you have received from trusted sources will run.  Other macros are automatically disabled.  If you wish to enable a macro that has been disabled you will need to change your security setting to Medium. Tools>Macro>Security

There are two types of macros that you can record.  A relative recorded macro and an absolute recorded macro.

ABSOLUTE MACROS

The default for recording a macro is Absolute.  This means that when you record your macro, it will run in/on exactly the same sheets/cells/columns/rows that you specify.

We will record a macro to bold column A.  The first thing we need to do is go to Tools>Macro>Record New Macro.  When you do this the Record Macro dialog box will pop up. The first thing we are asked for is to give our macro a name. We will call it BOLD_A.  (Note that when naming a macro, you cannot use spaces, but you can use an underscore to separate words).  Hit the tab key and you will jump to the Shortcut Key box, this is where you can assign your macro to a shortcut key if you wish.  Tab again and you will be taken to the Store Macro In: section of your dialog box.  It is here that you nominate where you wish your macro to be stored.  The options are:

Personal Macro Workbook - this means your macro will be stored in the Personal.xls workbook and will be available to you every time you Start Excel in any workbook.

New Workbook - Your macro will be created only in a new workbook

This Workbook - Your macro will be created only in the workbook you are
currently in.

We will use the Personal Macro Workbook.

Hit the tab key again and you will jump to the Description area of your dialog box, where you can give our macro a description.  Click OK and your box will disappear and the Stop Recording toolbar will pop up on your screen.  The icon on the right of this toolbar is used to toggle between Absolute (icon not depressed) & Relative (icon depressed) recording.

(Note that if your Stop Recording toolbar (it has been closed via the X) does not appear, go to View>Toolbars>Customize and under the Toolbars tab, check the Stop Recording toolbar from the list and hit Close).

Also notice the word Recording is displayed in your Excel Status Bar. Everything you do from now on is being recorded.  (This includes any errors you make!!!). For this reason, a few dry runs are always worth doing.

Click on the column header for column A (grey background) to highlight the entire column.  Click the bold key on your formatting toolbar.  Now click the Stop Recording tool on your toolbar.  Now, when you exit Excel, you will be prompted to save the changes you have made to your Personal Macro Workbook.

If you are not aware, the Personal Macro Workbook is ONLY created once you have recorded a macro to it. Once created, it will open (as a hidden Workbook) anytime you open Excel on the same PC. This means any macros stored within it are available anytime you are in any Workbook in Excel.

To see your Personal Macro Workbook (which is a normal Excel Workbook), go to Window>Unhide.

RELATIVE MACROS

Relative macros can appear tricky. However, in reality they are not. All you need to remember, when recoding a Relative macro, is that all your movements are *relative* to the active cell at the time your Start recording. For example, if you were in cell B10, Start ed recoding a relative macro, and selected cell A9 and bolded it, your movements would be relative to B10. If we then played back the macro, with C10 active, cell B9 would be selected and bolded. If were in cell A1 and played the macro, it would bug out! This is because it cannot select a cell 1 column to the left and 1 row up.

RUNNING YOUR MACRO

Other then running our macro via a shortcut key, we can also run them via the Macro dialog box. Tools>Macro>Macros (Alt+F8). Note also we can assign a shortcut key to any existing macros.

Next month we will continue with recording macros and show you how we can assign them to Objects, Menu items and even your own custom toolbar!

While we can stop users cutting/copying cells via Worksheet and/or Workbook protection, this is often at the expense of losing other Excel features that you may still want Excel users to access. The code below can be used to stop users being able to Cut or Copy any cells in the Workbook. However, you must realise that it will only work if the user elects to Enable Macros. No, one cannot force the enabling of macros via VBA

The code below must be placed in the Private Module of the Workbook Object (ThisWorkbook). To get there easily, right click on the Excel icon, top left next to File and choose View Code. In here paste the code below, close & save and then re-open.

Private Sub Workbook_Activate()Dim oCtrl As Office.CommandBarControl 'Disable all Cut menus     For Each oCtrl In Application.CommandBars.FindControls(ID:=21)            oCtrl.Enabled = False     Next oCtrl    'Disable all Copy menus     For Each oCtrl In Application.CommandBars.FindControls(ID:=19)            oCtrl.Enabled = False     Next oCtrl               Application.CellDragAndDrop = FalseEnd SubPrivate Sub Workbook_Deactivate()Dim oCtrl As Office.CommandBarControl 'Enable all Cut menus     For Each oCtrl In Application.CommandBars.FindControls(ID:=21)            oCtrl.Enabled = True     Next oCtrl    'Enable all Copy menus     For Each oCtrl In Application.CommandBars.FindControls(ID:=19)            oCtrl.Enabled = True     Next oCtrl               Application.CellDragAndDrop = TrueEnd SubPrivate Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)    With Application        .CellDragAndDrop = False        .CutCopyMode = False 'Clear clipboard    End WithEnd Sub

Note the use of the FindControls Method, in particular the use of the optional ID argument. One can determine the ID of any Control via some simple code like shown below.

Sub Copy_Id()    MsgBox CommandBars("Worksheet Menu Bar") _        .Controls("Edit").Controls("Copy").IDEnd Sub

List of Excel 2000 CommandBar Button IDs applies to excel 97 onwards.