OzGrid's Excel Newsletter

It ispublished on about the 10^th of each month and always written bymyself Dave Hawley.

Youare 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 ournewsletter, send an email with the words "action: Unsubscribe"in the body of the email, or clickhere.

Contained at the bottom of eachnewsletter is Helpful information on how to get the most from ournewsletters. If you think there is something missing, please let meknow.

Microsoft and Microsoft Excel are registered trademarks of MicrosoftCorporation

• MicrosoftExcel and VBA for Excel Training Information (auto response)
• OrderTraining Online
• HireOzGrid For Excel/VBA

Buyany of the OzGrid Excel add-ins from below, or let us design you a custom one,and get the File Size Reducer Free ManyOther Excel Add-ins HereOver 40 of them!

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

Readthis 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.

TrainingLessons Special [Top]

As aspecial, 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 ExcelTraining - http://www.ozgrid.com/Training/default.htmand 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 howwe could use some of Excels Lookup Functions to return information from a tableof data. We also looked at some guidelines for setting out a Table and List inan 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 downloadto go with this lesson, and last months here: 

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

Where August Examples is thename of the Worksheet and +6 is because our list starts in Row 6. Thisrange (Names) should now expand and contract as we add/removesnames. 

Do the same for Dept and Title Changingreferences accordingly, ie

=INDIRECT("'AugustExamples'!$D$7:"&ADDRESS(MATCH("*",'AugustExamples'!$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 ridiculouslylow number -9E+306 This ensures MATCH always finds the last age inthe Column.  All Dynamic Named ranges will also accommodate blank rows. Note alsowe have only gone down as far as row 500.  Resist the temptation (and badpractice) 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 complicationthat each of the dynamic named ranges may, at any one time, have a differentnumber of rows! Our entire Table range DataTable must always end at thelongest 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.   Nowtype 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 namedranges we will need and rest assured we have allowed for blank rows and for anyextra Columns being added, up to Column J.  Naturally if more Columns areadded to the Table we may need to also give them Dynamic Named Ranges andinclude them in the MaxRow name. This means our entire table is now veryflexible and we can virtually return data based on any criteria we wish! What wewill do is create a very simple Lookup table that will be based on the NameColumn. 

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

Now in cell C2  put theformula:

=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 anyemployee name from our list and the relevant information will be returned.

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

QuickExcel Tips  [Top]

Sortby more than 3 Columns: Excels sort feature only allows to nominateup to 3 columns to sort by, here is how to get around this. The key to this issorting by the last key first and working back to the first key. Say you data isin 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
   

Turna 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 turnedup-side-down. You can now also sort you original list using Data>Sort>Options,nominate your Custom List list then sort!

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

Microsoft Excel VBA tips [Top]

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

Remarks From Excel Help

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

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

If you wish to get theFace Id for Command Bars and Toolbars, go here: Downloads2 - http://www.ozgrid.com/Services/ExternalFree.htmand download Face Id for Command Bars andToolbars

Ok, lets start by seeinghow we could disable a particular Control in a Workbook. Let's assume we do notwant the user to have the ability to apply Excel Auto Filters while on aspecific sheet. To do this, first select the sheet, then right click on thesheet 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 AutoFilterOption whenever the sheet housing this code is activated and enable it wheneverit's deactivated.

Lets now use VBA to create a customcommand bar button on the Worksheet Menu bar. The button will run a macro wehave 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 onlyavailable while the Workbook is Active. With the needed Workbook open, rightclick 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 aProcedure 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 ownCustom 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 onCommandBars download: AddingCustomMenus.zip

VBAQuick Tips  [Top]

Stop users from Right clicking, orgoing 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 allowthe 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 theviewing 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

[email protected]

Youare 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. 

Buyany of the OzGrid Excel add-ins from below, or let us design you a custom one,and get the File Size Reducer Free ManyOther Excel Add-ins HereOver 40 of them!

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

HelpfulInformation [Top]

• MicrosoftExcel and VBA for Excel Training Information (auto response)
• OrderTraining Online
• HireOzGrid For Excel/VBA

Microsoft and Microsoft Excel are registered trademarks of MicrosoftCorporation