OzGrid Excel Newsletter

Contained at the bottom of eachnewsletter is Helpful Information on how to get the most from ournewsletters.

Should you wish to no longer receive ournewsletter, send an email with the words "action: Unsubscribe"in the body of the email, or clickhere.

Youare more than welcome to pass this on to as many people as you wish.

SPECIAL! Get all the OzGrid Add-ins, together and Save! $54.95

INDEX

What's New at OzGrid.com| New/Updated Pages | Microsoft Excel tips

Microsoft Excel VBA tips | Helpful Information |50% Off Special!

Check Out These Great Links:

• Global Software SearchSearch a database of thousands!
• OzGrid Excel Add-in Collection All the OzGrid add-ins together with Free file size reducer.
• Over 150 Excel Add-ins and Software! Possibly the largest collections in the world and getting bigger each week!
• Excel Duplication Manager. New features added! with Free file size reducer.
• OzGrid Excel Plus Add-in. Eight Add-ins in one! with Free file size reducer.
• Business Plans and Financial Statements Free from Microsoft
• Budget Excel Templates
   

What's new at OzGrid.com [Top]

Hi all and welcome to the year 2003!

Here we are 10 days into the New Year already.  We hope all subscribers have a happy, healthy and prosperous 2003.  2002 was a great year for OzGrid, but 2003 did not get off to a good start (don't you hate that!!).  Just before Christmas we were forced to switch web hosts to a US based UNIX system, this in itself was a pretty good move, but unfortunately we are experiencing a few teething problems.  UNIX is case sensitive to hyperlinks. This alone has forced us to go back through some 2000 + links and make changes where needed. Just to add to our New Year workload, many people have HTML training lessons with the incorrect case links. We are now on top of things and believe we have now addressed this problem and look forward to the rest of the coming year (and a holiday in February)!

Another problem that our new server has highlighted is that are having a lot of bandwidth being stolen from us. We never knew this with our old host (I guess ignorance really is bliss, but it is also one of the reasons that we needed to change hosts!).  We have a international team of experts working on this. (OK!  It's only one and he's not international, but he is an expert).  We hope to both find the culprit and take appropriate the action.

You can rest assured though, that now we are through our teething problems, OzGrid will go on to bigger and better things in 2003, and course you will be kept informed of them via this newsletter.

50% Off Perpetual Special...... [Top]

This months half price special is our Excel levels 1, 2 and 3 Downloadable Training. Normally the cost for these 30 lessons, 15 Workbook examples and 3 courses is $99.00. This already gets you a 45% discount off buying the courses individually, so by taking advantage of our special you get a further 50% off!

How To Get Them For Half Price!

To take advantage of this special (only available to newsletter subscribers) you must purchase by the 20th of January 2003 and be a newsletter subscriber. When you are ready simply click the Buy Now button below.

Excel level 1, 2 and 3  - 50% off special. Valid until 20th January 2003

Check Out These New/Updated Pages and Affiliate Sites: [Top]

Budget Excel Templates | Financial Calculators | Business Software | Intelligent Converters

Construction Software | Finance, Scheduling & Task Management | Software Sites

• 33+ Workbook Examples NEW!
• Coming Soon. Enhanced Excel Solvers and VBA Encryption Software.

Microsoft Excel tips[Top]

 Super Excel Special! Only $7.50Click here

This month (and next month) I thought we would look at using some handy ways to apply the Validation feature found under Data on the Worksheet Menu Bar. Validation can be used to prevent users from entering data into a range that does not meet a criteria. For example let's start with a really easy one

1. Select cells A1:A10 on any Worksheet
2. Go to Data>Validation and ensure you are on the Settings page tab.
3. From the Allow box select Whole number then Between from the Data box
4. Enter 1 in the Minimum box and 10 in the Maximum box
5. Click the Input page tab, ensure Show input message when cell is selected is checked.
6. Under Heading type: ONLY WHOLE NUMBERS
7. Under Input message type: Only whole numbers between 1 and 10 please.
8. Click Error Alert page tab and ensure the Show error alert after invalid data is entered check box is checked.
9. Under Style select Stop
10. Under Heading type: ONLY WHOLE NUMBERS
11. Under Input message type: You have typed a number NOT between 1 and 10.
12. Click OK

Now whenever you type a number in the range A1:A10 is must be between 1 and 10.

One very important point with Validation is that it will NOT stop a user from pasting invalid data into Validated cells. In fact, it will actually paste over the Validation as well and remove it, unless the copied cell also had Validation applied. Play about with the Style on the Error Alert page to see how it reacts to invalid data being entered.

You would have also noticed that there were many different options under Allow on the Settings page tab and most of these are self explanatory. Let's look at the List option as this one can be very handy! The List option allows us to either, type in a list of valid entries (separated by commas) or reference a single column, or row, range of valid entries. Most often the referencing of a range is the best way as it is easier to update the list when needed.

1. In cells B1:B5 type five names (anything will do)
2. Select cells A1:A10 on any Worksheet
3. Go to Data>Validation and ensure you are on the Settings page tab.
4. Select List from the Allow box.
5. Ensure the Ignore Blanks andIn-cell dropdown are checked.
6. Click anywhere within the Source box, then highlight cells B1:B10 (not B1:B5)
7. Remove the Input message and Error message it you wish, or replace them.
8. Click OK

Now when you select an cell in A1:A10 you should have a nice dropdown list containing your valid entries. Note also that there are no blanks in the list, even though we used range B1:B10. This is because we checked the Ignore Blanks checkbox. Now go back and enter some more names to the list so it extends down to B10. Go back to any cell in A1:A10 and your list will have expanded.

One problem you will most likely encounter, is that the List source must reside on the same sheet as the Validated cell, according to Excel that is. There are 2 ways we can get around this problem.

1. Name the range then use =MyRangein the source box for the List
2. Use =INDIRECT("'Sheet2'!D5:D8") in the source box for the List

I would opt for the named range method myself. Note in =INDIRECT("'Sheet2'!D5:D8") I have included the single apostrophe immediately before and after Sheet2. Normally this is only needed if the sheet name contains a space, but it is good practice to use it anyway as it work just fine.

One very handy trick that I have come up with and used to great success in the past, is the use of the List option in Validation to have the list of one validated cell change according to the item selected from another list. Do do this takes a good understanding ofDynamic Named ranges.You can find a Workbook example of this trickHere underMatchingLists.zip BTW, the OzGrid Excel Plus Found Here, can automatically write eight different types of dynamic named ranges and gives you the option to expand down rows or across columns as well as allowing you to nominate the column, or row, that will determine its expansion/contraction. Of course, it also has many other features that are often needed in Excel.

Let's assume you have Validated cells spread all over the one sheet (all using the same validation settings) and you need to make a change to them all. The last thing we want to do is hunt all over the Worksheet and locate them all and change each individually. Fortunately Excel will find them for us and make the change to them all. All we need to do is locate one of the cells, select it and go to Data>Validation. Make the needed changes, then click the Apply these setting to all other cell using the same settings checkbox before clicking OK. What this will do is select all cells that have the same validation before applying the change. The same thing can be done manually by first selecting a Validated cell then go Edit>Go to (or push F5) clicking Special then checking Data validation then Same and clicking OK.

Super Excel Special! Only $7.50Click here

TIPS

• If you have a spreadsheet that needs a small help system you can use the Input message feature of Validation to display simple help messages. All the user needs to do is click the cell!
• Sometimes you want to protect against accidental overtyping of you data, eg formulas. You can protect the sheet, but this can restrict many other Excel features you want available (Excel 2002 overcomes this). Try this.  Push F5, click Special then specify the type of cells to select and click OK. Now go to Data>Validation and select the Settings tab, choose Text Length then equal to and then type 0.  You can go to the Input message page and even type in a friendly text message, eg "Get out of this cell.....NOW!!!!"

33+ Workbook Examples NEW!

Would you like over1200 VBA examples?

Microsoft Excel VBA tips [Top]

I thought I would start this months VBA section with some handy code that tells you how many pages will be printed. The code uses the both the Vertical page break and Horizontal page break Collections to determine the number of page breaks, and hence number of page(s) that will print.

Sub HowManyPagesBreaks()
Dim iHpBreaks As Integer, iVBreaks As Integer
Dim iTotPages As Integer

    iHpBreaks = ActiveSheet.HPageBreaks.Count + 1
    iVBreaks = ActiveSheet.VPageBreaks.Count + 1

        iTotPages = iHpBreaks * iVBreaks
            MsgBox "This sheet will require " & iTotPages & _
                    " page(s) to print", vbInformation, "OzGrid.com"
End Sub

This is a handy on to store in your Personal.xls. The simple ones are often the best.

Excel Add-ins

I am often asked by users what is the best way to distribute their macros? My answer, is without doubt via an Excel Add-in. After all this is what Add-ins are for. For those that rea not sure, an Excel Add-in is nothing more than an Excel Workbook that has been saved as an Add-in, File>Save as \ Microsoft Excel Add-in (*.xla). Once saved and re-opened then Workbook will be hidden and can only be seen in the "Project Explorer" via the Visual Basic Editor. It is NOT hidden in the same way as the Personal.xls as this can be seen (and made visible) via Windows>Unhide. An Add-in is very hidden, much like a Sheet can be xlVeryHidden.

Once completed users can easily install your Add-in like below

1. Save the *.xla to your hard drive. This will be a spare, so keep it in a safe place!
   
    
2. Again save a copy to C:\WINDOWS\Application Data\Microsoft\AddIns\ .If not any location, just take note of it for step 4.
   
    
3. Open any Workbook.
   
    
4. On the Tools menu, point to Add-Ins and click Browse. Locate your add-in, select it and then click OK.
   
    
5. Ensure your add-in is in the Add-ins available: box and it is checked.
   
    
6. Now click OK and your add-in is installed.

Most code can be be saved to an Excel Add-in without too many changes. Some things to be aware of are

1. ThisWorkbook will always refer to the Add-in, not the users Workbook. Use ActiveWorkbook instead.
2. We cannot refer to sheets in the ActiveWorkbook via CodeNames.
3. We should always put things back to how the user had them originally. There is nothing worse than an Add-in that changes all your Excel settings without your knowledge.
4. Always include some sort of error handling (yes, most add-ins will error at some time).
5. Be very aware that the user may have many sorts of Protection on. NEVER use code to uprotect any part of the users Workbook. Simply display a message asking them to unprotect.
6. Make full and good use of the Sheet (sometimes more) you have in the Add-in. I use the sheet(s) to store user setting like toolbars etc.
7. Holding down the Shift key will NOT prevent any Add-in Workbook events running like it can in a normal .xls
8. Standard Excel warning messages like you get before deleting a Worksheet will NOT show if the code is running via an Add-in.
9. If you need to see the Add-in Workbook again, eg updates modifications etc. Go into the VBE while the Add-In is installed and from the Properties Window select the IsAddin property and set it to False. Saving the Workbook as an Add-in sets this Property to True.
10. Apply protection to the Modules of you Add-in, via Tools>VBAProject Properties-Protection.

Ok, once you have created your Add-in you will need to make the macros within it easy for the user to run. This is best achieved by making full use of both the Workbook_AddinInstall and the Workbook_AddinUnInstall Events in the Private Module of the ThisWorkbook Object. Let's look at a simple example of this.

Option Explicit

Dim cControl As CommandBarButton
Private Sub Workbook_AddinInstall()

On Error Resume Next 'Just in case
    'Delete any existing menu item that may have been left.
     Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete
    'Add the new menu item and Set a CommandBarButton Variable to it
     Set cControl = Application.CommandBars("Worksheet Menu Bar").Controls.Add
        'Work with the Variable
         With cControl
            .Caption = "Super Code"
            .Style = msoButtonCaption
            .OnAction = "MyGreatMacro"'Macro stored in a Standard Module
         End With
On Error GoTo 0
End Sub

Private Sub Workbook_AddinUninstall()
    On Error Resume Next 'In case it has already gone.
    Application.CommandBars("Worksheet Menu Bar").Controls("Super Code).Delete
    On Error GoTo 0
End Sub

This code will be all you need to add a single menu item to the existing Worksheet Menu Bar as soon as the Add-in is installed by the user via Tools>Add-ins. As mentioned earlier it MUST be placed in the Private Module of ThisWorkbook for the Add-in. As we have not told it otherwise, the code will add the new menu item to the end of the Worksheet Menu Bar. This is the easiest and perhaps safest way to add a menu item. if you want it added, say before the Format menu item you could use some code like this.

Option Explicit

Dim cControl As CommandBarButton

Private Sub Workbook_AddinInstall()
Dim iContIndex As Integer
    On Error Resume Next 'Just in case
        'Delete any existing menu item that may have been left.
         Application.CommandBars("Worksheet Menu Bar").Controls("Super Code").Delete

        'Pass the Index of the "Format" menu item number to a Variable.
        'Use the FindControl Method to find it's Index number. ID number _
         is used in case of Customization
        iContIndex = Application.CommandBars.FindControl(ID:=30006).Index

    'Add the new menu item and Set a CommandBarButton Variable to it.
    'Use the number passed to our Integer Variable to position it.
     Set cControl = Application.CommandBars("Worksheet Menu Bar") _
                                .Controls.Add(Before:=iContIndex)

            'Work with the Variable
             With cControl
                .Caption = "Super Code"
                .Style = msoButtonCaption
                .OnAction = "MyGreatMacro" 'Macro stored in a Standard Module
             End With
    On Error GoTo 0
End Sub

There would be no need to change the Workbook_AddinUninstall() code in this case. We have covered ID numbers while working with CommandBars etc in a Prior Issue The link to the Microsoft site that has a BIG list of all the ID numbers for working with CommandBars can be Found Here

The above examples actually have the menu item code in the Workbook_AddinInstall and Workbook_AddinUnInstall Not a problem when the code is only adding one menu item. If however you will be adding many and even Sub menus you should place it in a Procedure (or 2+) in standard Module. The use some code as shown below

Private Sub Workbook_AddinInstall()
    Run "AddMenus"
End Sub

Private Sub Workbook_AddinUninstall()
    Run "DeleteMenu"
End Sub

Then in the standard module put some code perhaps like this

Sub AddMenus()
Dim cMenu1 As CommandBarControl
Dim cbMainMenuBar As CommandBar
Dim iHelpMenu As Integer
Dim cbcCutomMenu As CommandBarControl

    '(1)Delete any existing one. We must use On Error Resume next _
      in case it does not exist.
        On Error Resume Next
        Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
 

    '(2)Set a CommandBar variable to Worksheet menu bar
        Set cbMainMenuBar = _
            Application.CommandBars("Worksheet Menu Bar")

    '(3)Return the Index number of the Help menu. We can then use _
        this to place a custom menu before.
        iHelpMenu = _
        cbMainMenuBar.Controls("Help").Index

    '(4)Add a Control to the "Worksheet Menu Bar" before Help.
    'Set a CommandBarControl variable to it
        Set cbcCutomMenu = _
            cbMainMenuBar.Controls.Add(Type:=msoControlPopup, _
                                            Before:=iHelpMenu)

    '(5)Give the control a caption
      cbcCutomMenu.Caption = "&New Menu"

    '(6)Working with our new Control, add a sub control and _
      give it a Caption and tell it which macro to run (OnAction).
        With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
            .Caption = "Menu 1"
            .OnAction = "MyMacro1"
        End With
    '(6a)Add another sub control give it a Caption _
      and tell it which macro to run (OnAction)
        With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
            .Caption = "Menu 2"
            .OnAction = "MyMacro2"
        End With
    'Repeat step "6a" for each menu item you want to add.


    'Add another menu that will lead off to another menu
    'Set a CommandBarControl variable to it
     Set cbcCutomMenu = cbcCutomMenu.Controls.Add(Type:=msoControlPopup)
    ' Give the control a caption
     cbcCutomMenu.Caption = "Ne&xt Menu"

    'Add a control to the sub menu, just created above
        With cbcCutomMenu.Controls.Add(Type:=msoControlButton)
            .Caption = "&Charts"
            .FaceId = 420
            .OnAction = "MyMacro2"
        End With


  On Error GoTo 0
End Sub

Sub DeleteMenu()
    On Error Resume Next
        Application.CommandBars("Worksheet Menu Bar").Controls("&New Menu").Delete
    On Error GoTo 0
End Sub

Youare more than welcome to pass this on to as many people as you wish.

SPECIAL! Get all the OzGrid Add-ins, together and Save! $54.95

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

Helpful Information [Top]

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

Microsoft and Microsoft Excel are registered trademarks of MicrosoftCorporation