OzGrid's Excel Newsletter

It is published on about the 10^th of each month and always written by myself Dave Hawley.

You are more than welcome to pass on this newsletter to as many people as you wish, all I ask is you pass it on in its entirety. 

Should you wish to no longer receive our newsletter, send an email with the words "action: Unsubscribe" in the body of the email, or click here.

Contained at the bottom of each newsletter is Helpful information on how to get the most from our newsletters. If you think there is something missing, please let me know.

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation

• Microsoft Excel and VBA for Excel Training Information (auto response)
• Order Training Online
• Hire OzGrid For Excel/VBA

Buy any of the OzGrid Excel add-ins from below, or let us design you a custom one, and get the File Size Reducer Free Many Other Excel Add-ins Here Over 40 of them!

• OzGrid Excel Plus
• Formula Ref Changer
• Sheet Index Creator
• Duplication Manager

Read this issue and past issues online here: http://www.ozgrid.com/News/Archive.htm

INDEX

What's new at OzGrid.com

Microsoft Excel tips

Microsoft Excel VBA tips

Helpful information

What's new at OzGrid.com [Top]

Hi all,

Another busy month has passed speedily here at OzGrid, I just don't know where the time goes these days.  Things are very busy at the moment, and with the increasing demand for lecturing work away from the office, we seem to have a never-ending back-log of work to catch up on.  Still, I guess when you have your own business such as we do, it gives the feeling of (a) relief, knowing that you have lots of work and can eat and pay bills and (b) pride in your own business, therefore increasing motivation and enthusiasm.

Winter is just about over here in Australia, with 1 September being the first day of spring.  As I sit here and type this on a Sunday afternoon, I am wondering if I will feel so enthusiastic and motivated when it is 38 degrees outside, and we can walk to the beach from here!!!!

Seriously, the business is increasing at such a rate, that Dave will be reducing the time that he spends programming for clients.  We will still keep our existing clients, but will only be taking on new jobs as time permits.  There are so many growth areas in our business, such as our Training lessons and Add-Ins, that we feel to be fair to all our clients, we need to spend time and effort in other areas as well as the programming side of things.

Training Lessons Special [Top]

As a special, for this month, are course prices as as shown below:

• $99.00 per course*
• $150.00 for any two courses*
• $250 for any three courses*
• $55 for any extra course*

*$55.00 for each additional participant

All Online Courses can then be accessed by going to Excel Training - http://www.ozgrid.com/Training/default.htm and clicking the Log In button

  Microsoft Excel Add-ins  [Top]

Since the launch of our Excel add-ins :

• OzGrid Excel Plus
• Formula Ref Changer
• Sheet Index Creator
• Duplication Manager

The feedback has been so huge that Dave has had lots of requests for tailor-made Add-Ins to automate tasks.  He has written quite a few Add-ins for clients on an "as required" basis, proving to us that there is indeed a niche market for this sort of thing.  We are extremely excited that the Add-ins have proved to be such a huge hit, so much so that we will be devoting a more time to developing and expanding this area in the future.

 Microsoft Excel tips [Top]

In last months issue we looked at how we could use some of Excels Lookup Functions to return information from a table of data. We also looked at some guidelines for setting out a Table and List in an Excel spreadsheet. If you were not subscribed last month, part 1 (Issue 15) can be read online here: Newsletter - Archives -  http://www.ozgrid.com/News/Archive.htm

There is also a Workbook download to go with this lesson, and last months here: 

 Download the workbook here - http://www.ozgrid.com/download/AugNewsletter.zip

Where August Examples is the name of the Worksheet and +6 is because our list starts in Row 6. This range (Names) should now expand and contract as we add/removes names. 

Do the same for Dept and Title Changing references accordingly, ie

=INDIRECT("'August Examples'!$D$7:"&ADDRESS(MATCH("*",'August Examples'!$D$7:$D$500,-1)+6,4))

=INDIRECT("'August Examples'!$E$7:"&ADDRESS(MATCH("*",'August Examples'!$E$7:$E$500,-1)+6,5))

For our Age Column we need a slight variation, as we are dealing with Numeric data and not Text:

=INDIRECT("'August Examples'!$C$7:"&ADDRESS(MATCH(-9E+306,'August Examples'!$C$7:$C$500,-1)+6,3))

Note the use of the ridiculously low number -9E+306 This ensures MATCH always finds the last age in the Column.  All Dynamic Named ranges will also accommodate blank rows. Note also we have only gone down as far as row 500.  Resist the temptation (and bad practice) of referencing entire Columns.

Lets now name the entire Table DataTable.  Before we do though let's make the assumption that the table, at any one time, may contain rows that only contain some of the information. This adds the complication that each of the dynamic named ranges may, at any one time, have a different number of rows! Our entire Table range DataTable must always end at the longest column. Here is how we can easily accommodate this.

1. Go to Insert>Name>Define and type the name MaxRow
2. In the Refers to: box place the Formula:

=MAX(ROWS(Names),ROWS(Age),ROWS(Dept),ROWS(Title))

And Click Add

    3.   Now type the name DataTable and Refer it to:

=OFFSET('August Examples'!$B$7,0,0,MaxRow,COUNTA('August Examples'!$B$7:$J$7))

We now have created all the named ranges we will need and rest assured we have allowed for blank rows and for any extra Columns being added, up to Column J.  Naturally if more Columns are added to the Table we may need to also give them Dynamic Named Ranges and include them in the MaxRow name. This means our entire table is now very flexible and we can virtually return data based on any criteria we wish! What we will do is create a very simple Lookup table that will be based on the Name Column. 

Now make a copy of our headings in row 6 in row 1 starting from B1. To do this, simply place =B6 in B1 and copy across to E1. Now select cell B2 and go to Data>Validation and select List from the allow box, then put =Names in the Source: box and Click OK. Now name this cell EmployeeName by selecting cell B1 and left clicking in the Name Box (left of Formula Bar) and typing the word EmployeeName then push Enter.

Now in cell C2  put the formula:

=IF(EmployeeName="","",VLOOKUP(EmployeeName,DataTable,2,FALSE))

In D2:

=IF(EmployeeName="","",VLOOKUP(EmployeeName,DataTable,3,FALSE))

And in E2:

=IF(EmployeeName="","",VLOOKUP(EmployeeName,DataTable,4,FALSE))

All we need to do now is select any employee name from our list and the relevant information will be returned.

Next month we will look at how we can use Excel Lookup functions to retrieve information when we have more than one occurrence of specific data and we want to nominate which occurrence we want. We will also look at how to use Lookups to match 2 criteria.

Quick Excel Tips  [Top]

Sort by more than 3 Columns: Excels sort feature only allows to nominate up to 3 columns to sort by, here is how to get around this. The key to this is sorting by the last key first and working back to the first key. Say you data is in Column A:E and you want to sort by A, B, C , D then E

1. Select all of Columns A:E
2. Go to Data>Sort> Sort by C then by D then by E
3. Click Sort
4. Now again with Columns A:E selected
5. Go to Data>Sort> sort by A then by B
6. Click Sort
   

Turn a List Upside-Down:

1. Copy the list to another location using Copy, Edit>Paste Special>Value.
2. Now select all data in the list, go to Tools>Options>Custom Lists.
3. Ensure the list address is in the "Import list from cells:" and click "Import".
4. Now go back to the column next to your list and in the top cell place the LAST entry from your list.
5. Now in the cell below, place the second last entry.
6. Select both cells and double click on the Fill Handle (small black square bottom right).

The list should now be turned up-side-down. You can now also sort you original list using Data>Sort>Options, nominate your Custom List list then sort!

How to copy and transpose formulas without the reference changing:
In cell A1 of sheet 2 put: =Sheet1!A1 now copy this down a max of 255 rows. Now with the formulas selected go to Edit>Replace and Replace = with #. Now copy, then select cell B1, go to Edit>Paste special and choose Transpose. Delete Column "A" and with Row 1 selected go to Edit>Replace and Replace # with =

Microsoft Excel VBA tips [Top]

 This month I thought we would look at CommandBars and how we can use VBA to create Custom ones and/or manipulate existing ones. First thing we need to know about CommandBars, is that they are part of the CommandBars Collection. This simply means we must tell Excel which CommandBar from the CommandBars Collection we are referring to. We must also understand that each CommandBar has it's own set of Controls, from the Controls Collection. Again this means when we reference particular a Control we must should tell Excel which CommandBar the Control resides on. The easiest way to do this is by knowing the ID's of all Excel Controls. Once we know this we can use the FindControl Method to locate our Control.

Remarks From Excel Help

If the CommandBars collection contains two or more controls that fit the search criteria, FindControl returns the first control that's found. If no control that fits the criteria is found, FindControl returns Nothing.

Rather than have to write some lengthy code to get a list of ID numbers for Excel Built in Command Bar Controls, go here: XL2000: List of ID Numbers for Built-In CommandBar Controls -  http://support.microsoft.com/default.aspx?scid=kb;[LN];Q213552 and Print out the list. It consists of about 16 printed pages. That gives you some idea of just how many there are! I have this list by my PC for easy reference and can tell you that it is well worth taking the time to print it.

If you wish to get the Face Id for Command Bars and Toolbars, go here: Downloads 2 - http://www.ozgrid.com/Services/ExternalFree.htm and download Face Id for Command Bars and Toolbars

Ok, lets start by seeing how we could disable a particular Control in a Workbook. Let's assume we do not want the user to have the ability to apply Excel Auto Filters while on a specific sheet. To do this, first select the sheet, then right click on the sheet name tab and select "View Code" and paste in this code:

Dim cComm As CommandBar
Private Sub Worksheet_Activate()
On Error Resume Next
    Set cComm = Application.CommandBars("Worksheet Menu Bar")
    cComm.Controls("&Data").Controls("&Filter").Controls("Auto&Filter").Enabled = False
On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
On Error Resume Next
    Set cComm = Application.CommandBars("Worksheet Menu Bar")
    cComm.Controls("&Data").Controls("&Filter").Controls("Auto&Filter").Enabled = True
On Error GoTo 0
End Sub

This will now disable the AutoFilter Option whenever the sheet housing this code is activated and enable it whenever it's deactivated.

Lets now use VBA to create a custom command bar button on the Worksheet Menu bar. The button will run a macro we have already written, and is housed within the Workbook, called "MyMacro". As this macro is only for a nominated Workbook, we need to ensure it is only available while the Workbook is Active. With the needed Workbook open, right click on the Excel icon, top left next to "File" and select "View Code" and paste in the code below.

Private Sub Workbook_Activate()
Dim cCont As CommandBarButton

    On Error Resume Next
        ' Just in case it's still there.
        Application.CommandBars(1).Controls("Do It").Delete

    Set cCont = Application.CommandBars(1).Controls.Add()
        With cCont
            .Caption = "Do It"
            .Style = msoButtonIconAndCaption
            .FaceId = 387
            .OnAction = "MyMacro"
        End With
    On Error GoTo 0
End Sub


Private Sub Workbook_Deactivate()
    On Error Resume Next
        Application.CommandBars(1).Controls("Do It").Delete
    On Error GoTo 0
End Sub

This of course requires you to have a Procedure called "MyMacro" in a Standard Module of the same Workbook. If the Procedure resides in another Workbook (eg Personal.xls) use:

.OnAction = "Personal.xls!MyMacro"

If you wanted to have you very own Custom CommandBar created at Run time via VBA we could use some code like this:

Dim bClosed As Boolean

Private Sub Workbook_Activate()
    On Error Resume Next
        Application.CommandBars("MyCustomBar").Enabled = True
    On Error GoTo 0
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)
    bClosed = Not Cancel
End Sub

Private Sub Workbook_Deactivate()
    On Error Resume Next
        If bClosed = False Then 'Only decativating
            Application.CommandBars("MyCustomBar").Enabled = False
        Else 'Workbook closing
            Application.CommandBars("MyCustomBar").Delete
        End If
    On Error GoTo 0
End Sub

Private Sub Workbook_Open()
Dim cContPop As CommandBarPopup
Dim cCont As CommandBarButton
Dim cComm As CommandBar

    On Error Resume Next
        Application.CommandBars("MyCustomBar").Delete
        Set cComm = Application.CommandBars.Add

        With cComm
            .Name = "MyCustomBar"
            .Position = msoBarTop
            .RowIndex = 1
            .Visible = True
            .Protection = msoBarNoCustomize
        Set cContPop = .Controls.Add(Type:=msoControlPopup)
        End With

        With cContPop
            .Caption = "MyMacros"
        Set cCont = .Controls.Add()
        End With

        With cCont
            .Caption = "Do It"
            .Style = msoButtonIconAndCaption
            .FaceId = 387
            .OnAction = "MyMacro"
        End With
    On Error GoTo 0
End Sub

Some important points to note are:

1. We only run the code to create the Custom Toolbar and any buttons once, ie when the Workbook is first opened.
2. We use the Enabled Property of the Custom Toolbar to hide/show it whenever the Workbook is deactivated/activated.
3. We use the Deactivate Event of the Workbook to Delete the Custom toolbar and the BeforeClose Event only as a Flag to determine if the user chose to Cancel if they are asked to Save Changes before closing. It's a common mistake to use the BeforeClose Event directly, which can leave the user without the Custom Toolbar should they opt to Cancel when asked to Save Changes.
4. We have used: .Protection = msoBarNoCustomize to ensure nobody can try to manually Customize our Toolbar.

To see some more sample code on CommandBars download: AddingCustomMenus.zip

VBA Quick Tips  [Top]

Stop users from Right clicking, or going to View>Toolbars and Customizing any Toolbars, run this code

Sub NoCustomize()
    Application.CommandBars("Toolbar List").Enabled = False
End Sub

Protect a Worksheet but still allow the use of Excel AutoFilter.  Assumes AutoFilters are on! To easily access the Private module of a Worksheet Object, while in Excel, right click on the Worksheet name tab and select "View Code".

Private Sub Worksheet_Activate()
    Me.Protect Password:="Secret", UserInterFaceOnly:=True
    Me.EnableAutoFilter = True
End Sub

Prompt for a password to allow the viewing of a specified sheet. To easily access the Private module of a Worksheet Object, while in Excel, right click on the Worksheet name tab and select "View Code".

Private Sub Worksheet_Activate()
Dim strPassword As String
On Error Resume Next
    Me.Protect Password:="Secret"
    Me.Columns.Hidden = True

    strPassword = InputBox("Password Please!", "OzGrid.com")

        If strPassword = "" Then
            Me.Previous.Select
            Exit Sub
        ElseIf strPassword <> "Secret" Then
            MsgBox "Incorrect Password","OzGrid.com"
            Me.Previous.Select
            Exit Sub
        Else
            Me.Unprotect Password:="Secret"
            Me.Columns.Hidden = False
        End If

On Error GoTo 0
End Sub

Private Sub Worksheet_Deactivate()
    On Error Resume Next
        Me.Columns.Hidden = True
    On Error GoTo 0
End Sub

OK, that's it for this month - until next month - keep Excelling!

Kind regards

Dave Hawley

DavidH@OzGrid.com

You are more than welcome to pass on this newsletter to as many people as you wish, all I ask is you pass it on in its entirety. 

Buy any of the OzGrid Excel add-ins from below, or let us design you a custom one, and get the File Size Reducer Free Many Other Excel Add-ins Here Over 40 of them!

• OzGrid Excel Plus
• Formula Ref Changer
• Sheet Index Creator
• Duplication Manager

Helpful Information [Top]

• Microsoft Excel and VBA for Excel Training Information (auto response)
• Order Training Online
• Hire OzGrid For Excel/VBA

Microsoft and Microsoft Excel are registered trademarks of Microsoft Corporation