If you cannot read HTML in your email please go here: http://www.ozgrid.com/News/Archive.htm

OzGrid Excel and VBA Newsletter

Search Tips

You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!To no longer receive our newsletter, send an NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .Microsoft and Microsoft Excel are registered trademarks ofMicrosoft Corporation Read this issue and past issuesonline here :

PLEASE DO NOT REPLY TO THIS ADDRESS

MAIN INDEX

What's New at OzGrid | Excel Tips and Tricks | Excel VBA Tips and Tricks

What's New at OzGrid

I can't believe it is September already.  This year has just flown by and still our feet have not touched the ground!  Things don't look like they will be changing much over the next few months as we move towards Christmas and the summer here in Australia, and the ever-increasing workload that OzGrid has! 

One reason for the ever-increasing workload is that we are about to embark on a huge project - writing a book.  We have been approached by Agent for O'Reilly Publishing to write a book on Excel Hacks.  The book will feature clever and non-obvious solutions to challenging problems and will be aimed at intermediate-level power users.  The book will contain 100 article-length topics that are new and interesting. Each will provide a detailed example of how to do something.   I don't think we will get rich from the book, but we are certainly thrilled at being approached and feel that it will give our business real credibility.  What remains to be seen now is if Dave ("the Brains") and I ("the organiser/typist") can work together without too many problems!!!  We hope to receive the contract this week and will let you know when the book will be published when we find out.  I have suggested to Dave many times over the years that he put his thoughts and ideas down on paper, that is really how the newsletter evolved, so this is really a huge opportunity for him, and us as a business.

Dave has also spent some time this month constructing an "easier to browse" index for the following most popular areas:

• Templates Excel Templates for all your needs. Some real bundled bargains!
• Financial Software For Personal Budgets, Business Accounting and much more.
• Excel Add-Ins Over 100 to choose from.
• Financial Calculators for calculations of Appreciation, Assets, Cash Flow, Home Mortgage rates, Future Values of Savings, Loan Spreads and more.
• Construction and Estimating Software Ideal for any sort of construction estimating.
• Charting Software and Add-ins For many type of Charts. 3D Charts, Waterfall charts and Cascade Charts and more.
• Trading Software For financial data and stock market reports. There is software for futures trading, commodity trading, pricing analysis

That's it for this month, enjoy the newsletter and remember to check out any past issues here :

Excel Tips and Tricks

This month we will look at customizing Excel Toolbars and attaching Macros (recorded or written) to Them. When you have recorded, or written a Macro in Excel you need some way of being able to run it. One way is via a short-cut key and this can be done very easily in the following way.

1. Go to Tools>Macro>Marcos. Or simply push Alft+F8
2. Select the macro name from the Macro Names. Note you may need to choose another option from the Marcos inoptions at the bottom.
3. Click Options and choose a key(s) to go with Ctrl
4. Click OK then Cancel.

Just remember that any short-cut keys you assign WILL override any existing operation that uses the same short-cut key.The one problem with only using this method is that others that use the Workbook will need to know the short-cut key(s) to run the macro(s). A better way could be to create your own button on any one of Excels' existing toolbars. Two things to keep in mind when using this method are:

1. Another user may NOT have the toolbar chosen visible.
2. It will make the macro available on any Worksheet and Workbook. More on this soon.

To cover point one, simply place your custom menu item on the Worksheet Menu Bar. This toolbar can only be hidden via code or when you activate a Chart sheet. Point 2 can be VERY important for recorded macros as they often rely on a specific Worksheet to be the active Worksheet when the macro is run. If this is the case, you have 2 easy choices.

1. Attach your macro to a Command Button or Drawing Object.
2. Place some simple code in the Worksheet Module.

Number one simply means you go to View>Toolbars and check Forms and/or Drawing. Then choose the  Command Button from the Forms toolbar, or a Drawing object from the Drawing toolbar and place it on the Worksheet. Then Right click on the Object and choose Assign Macro. Once you have done this the macro will only be able to run from the Worksheet housing the Object.

Let's now look at using our own custom menu item. I will then show you how to make it only run while on a specified Worksheet.

1. Right click on any Toolbar and select Customize.
2. Select the Commands page tab.
3. Choose any existing Command button by changing the Categories. Or scroll through the Categories until you see Macros and select either the smiley face or the blank Custom Menu Item.
4. Now, holding down the left mouse button, drag the chosen menu item to any toolbar and release. Note that holding the menu item over any drop-down menu list will enable you to drop it onto the list.
5. Now right click on your new menu item and you will see lots of cool options.

Here is what these option do.Taken from the Excel help.

1. Reset: Restores the selected button or menu command to its original name and button image.
2. Delete: Deletes the selected toolbar button or menu command.
3. Name: Type a new name, and then press ENTER.
4. Copy Button Image: Copies the button face of the selected button to the Windows Clipboard. Right-click the button on which you want to paste the button face, and then click Paste Button Image.
5. Paste Button Image: Pastes the button face you copied using the Copy Button Image command onto the selected button.
6. Reset Button Image: Restores the selected button face to its original button image.
7. Edit Button Image: Opens the selected button face in the Button Editor, where you can change the button image.
8. Change Button Image: Displays categories of commands, organized by menu name or by type. Click a category to change the list of commands in the Commands box. The Built In Menus category provides options for changing menus.
9. Default Style: Displays the selected toolbar button or menu command in the default style. For toolbar buttons, displays only the button image. For menu commands, displays the command name and any button image next to it.
10. Text Only (Always): Displays only the text name with no button image for the selected toolbar button or menu command.
11. Text Only (In menus): Displays categories of commands, organized by menu name or by type. Click a category to change the list of commands in the Commands box. The Built In Menus category provides options for changing menus.
12. Image and Text:  Displays both the text name and the button image for the selected toolbar button or menu command.
13. Begin a Group: Displays categories of commands, organized by menu name or by type. Click a category to change the list of commands in the Commands box. The Built In Menus category provides options for changing menus.
14. Assign Hyperlink: Displays categories of commands, organized by menu name or by type. Click a category to change the list of commands in the Commands box. The Built In Menus category provides options for changing menus.
15. Assign Macro: Assigns a macro to the selected object. This command is available only when a graphic object or a control is selected, or when the Customize dialog box is open and a tool is selected.

You can have a lot of fun trying to drawing your own pictures via Edit Button Image, but it's not as easy as it looks :o) If you are like me, and cannot draw, go to our free downloads page here and scroll down to FaceId.zip at the bottom, orhere and download the Face Id Explorer. If you would like an easier to use more advanced one, see the Macro Button Assistant for Excel

Ok, you may or may not have also noted the New Menu option under Categories. This can be dragged and dropped in the same manner. Then you can drop your own menu items onto the drop-down menu.

As mentioned earlier, our custom menu items are going to be available to all Workbooks and Worksheets. This can be an issue when the macro is intended for a specific Worksheet in a specific Workbook. All you need to know to overcome this problem is the name of the Toolbar you have placed your custom menu item on. As mentioned above, the Worksheet Menu Bar is a safe bet.

1. Activate the sheet that your macro needs active to run correctly.
2. Right click on the sheet name tab and select View Code and place in the code as shown below:

Private Sub Worksheet_Activate()
  Application.CommandBars("Worksheet Menu Bar").Controls _
   ("My Macro").Enabled = True
End Sub

Private Sub Worksheet_Deactivate()
  Application.CommandBars("Worksheet Menu Bar").Controls _
   ("My Macro").Enabled = False
End Sub

Simply change the name of My Macro to the name of your menu item. If anyone has any problem with the code make sure you visit our Excel Forum to obtain help from some truly remarkable people.Next month we will look at creating a Custom Toolbar to keep our custom menu item on.

Excel VBA Tips and Tricks

This month we will look at using AutoFilter in Excel VBA. Sadly, this Method is often overlooked, or forgotten about when working in the VBE. You will find however that it will make code much faster, more efficient and generally easier. Let's look at the AutoFilter Method. Before we do so though we should know about:

1. AutoFilterMode Property: True if the AutoFilter drop-down arrows are currently displayed on the sheet. This property is independent of theFilterMode property. Read/write Boolean.
2. FilterMode Property: True if the worksheet is in filter mode. Read-only Boolean.
3. AutoFilter Method: Filters a list using the AutoFilter. Variant.

Let's use a table of data exactly as shown below for all examples. We will call the sheet this table resides on PetShop and a Sheet CodeName of Sheet1

If the Worksheet housing this table had the AutoFilters already applied, using code like:

Sub ToggleAutoFilter()
   Sheet1.Range("A1:C1").AutoFilter
End Sub

...it would in turn apply the AutoFilters if they were not already applied, and remove the AutoFilters if they were already applied. If your table was filtered down to show only specified items and we used the above code, the table would become un-filteredand AutoFilters would be removed.

For this reason we should always determine the AutoFilter state before using such code. There are 2 ways this can be done.

Sub TurnOnAutoFilters()
 If Sheet1.AutoFilterMode = False Then
    Sheet1.Range("A1:C1").AutoFilter
  End If
End Sub

Sub TurnOnAutoFiltersBetterWay()
 On Error Resume Next
   Sheet1.AutoFilterMode = False
   Sheet1.Range("A1:C1").AutoFilter
 On Error GoTo 0
End Sub

I consider the second example to be a far better for one simple reason. The AutoFilter Mode only tells us that the Worksheet has the AutoFilters on, it does not mean that they are applied to the correct range though. The second method simply ensures the AutoFilters are removed and then applied to the needed range. If they are not applied already our code would bug-out when it tried to remove them, hence the use of On Error Resume Next to prevent this.

Sometimes we only need AutoFilters to apply to one column of data, as opposed to all heading on the top row. You may think that code like:

Sub TurnOnAutoFiltersBetterWay()
 On Error Resume Next
   Sheet1.AutoFilterMode = False
   Sheet1.Range("A1").AutoFilter
 On Error GoTo 0
End Sub

Would do this, but in fact it would still apply the AutoFilters to the range A1:C1. This is Excel trying to make an educated guess that you really want all the heading to have AutoFilters. The way we can force the AutoFilter to cell A1 is like below

Sub TurnOnAutoFiltersBetterWay()
 On Error Resume Next
   Sheet1.AutoFilterMode = False
   Sheet1.Range("A1:A2").AutoFilter
 On Error GoTo 0
End Sub

Ok, if we then wanted to show only rows that have the text "Bird" in Column A we would use:

Sub TurnOnAutoFiltersBetterWay()
 On Error Resume Next
    With Sheet1
      .AutoFilterMode = False
      .Range("A1:A2").AutoFilter
      .Range("A1").AutoFilter Field:=1, Criteria1:="Bird"
    End With
 On Error GoTo 0
End Sub

In this case we have only used one criteria and a simple one at that. We can however have two criteria that is also slightly more complicated.

Sub TurnOnAutoFiltersBetterWay()
 On Error Resume Next
    With Sheet1
     .AutoFilterMode = False
     .Range("A1:A2").AutoFilter
     .Range("A1").AutoFilter Field:=1, Criteria1:="=*t", _
      Operator:=xlOr, Criteria2:="=d*"
    End With
 On Error GoTo 0
End Sub

This code would filter our list, by Column A, to show only those pets that end with the letter "t" OR begin with the letter "D". If we wanted this criteria for Column A and then specify a criteria for Column "B" (Cost) we could use:

Sub TurnOnAutoFiltersBetterWay()
 On Error Resume Next
    With Sheet1
     .AutoFilterMode = False
     .Range("A1:B1").AutoFilter
     .Range("A1:B1").AutoFilter Field:=1, Criteria1:="=*t", _
      Operator:=xlOr, Criteria2:="=d*"
     .Range("A1:B1").AutoFilter Field:=2, Criteria1:="<60"
    End With
 On Error GoTo 0
End Sub

This time we are still showing pets in Column A that met our original criteria, but we have also told Excel to hide any rows in Column B (Cost) that is less than $60.00 What is important to note here is

1. We have applied the AutoFilter to range A1:B1
2. We have used Field:=2 for Column B (Cost). This is because the left most filter on a table is always Field1 while the second in Field2 etc.
3. Although we are working with numeric data, our Criteria is always expressed as a String.

By far the easiest way to get a really good Start for any code with multiple criteria is to use the Marco Recorder.

Once you have filtered your table you will no doubt need to do some others things. for example, we can now easily tell how many pets meet the criteria as shown above.

Sub CountCriteria()
Dim lCount As Long
 On Error Resume Next
   With Sheet1
     .AutoFilterMode = False
     .Range("A1:B1").AutoFilter
     .Range("A1:B1").AutoFilter Field:=1, Criteria1:="=*t", _
      Operator:=xlOr, Criteria2:="=d*"
     .Range("A1:B1").AutoFilter Field:=2, Criteria1:="<60"
   lCount = Range("A2", Range("A65536").End(xlUp)).SpecialCells _
        (xlCellTypeVisible).Count
  End With
MsgBox "There are " & lCount & " pets that meet that criteria", vbInformation
On Error GoTo 0
End Sub

Just be careful that you are not storing data in Column A outside of the table, else Range("A2", Range("A65536").End(xlUp)) will return an incorrect range and your count will be wrong. Also remember to account for your headings, which is why I Start ed with A2 and not A1.

We can often use the AutoFilter with the SpecialCells Method to restrict a For Each loop to only those cells that meet the criteria.

Sub RestrictForEachLoop()
Dim rRange As Range
Dim rCell As Range
 On Error Resume Next

    With Sheet1
      .AutoFilterMode = False
      .Range("A1:B1").AutoFilter
      .Range("A1:B1").AutoFilter Field:=1, Criteria1:="=*t", _
     Operator:=xlOr, Criteria2:="=d*"
      .Range("A1:B1").AutoFilter Field:=2, Criteria1:="<60"
     Set rRange = Range("A2", Range("A65536").End(xlUp) _
        ).SpecialCells(xlCellTypeVisible)
    End With

    For Each rCell In rRange
     'Code Goes Here
    Next rCell

On Error GoTo 0
End Sub

Once you have done all you need to only the visible cells, we can turn off the AutoFilters completely like:

Sheet1.AutoFilterMode = False

Try and get used to using the AutoFilter Method as it really is a great time saver and combined with the SpecialCells Method you can do all sorts of handy and interesting things.

Until next month, keep Excelling!

Office Ready Professional 3.0 |High Impact e-Mail 2.0 |Office-Ready Business Plans |Office-Ready Marketing Plans |e-Marketing Suite |Office Policy Manual |Ultimate Everyday Calculator |Ultimate Financial Calculator |Ultimate Marketing Calculator |Template Zone Home Page |Office Ready Stuff It |Ultimate Loan Calculator

You are more than welcome to pass on this newsletter to as many people as you wish. Or even upload it (as is) to your Web site!
To no longer receive our newsletter, send a NEW email with the exact words "action: Unsubscribe" in the body of the email, or click here .
Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation
Read this issue and past issues online here :