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

As most of you will notice we have changed the layout and format of our newsletter, hope you like it.

OurExcel add-ins list continues to grow and grow and we back all Excel Add-ins with full 30 day money back guarantee.

The BIG price drop on ourdownloadable Excel training has been a bigger success than we ever imagined. Because of this we have decided to keep the discount for a while longer before putting the prices back-up. Many companies are buying the lessons with a multi user licence (even bigger discount) and using the training for it's employees by either sending them all a copy, or uploading them to their company Intranet. Combine that with our totally free question and answer forum and you wont get quality Excel training at this price anywhere else. Remember we use and develop Excel all day every day! If you want training with full question support from OzGrid, look no further than our Online or E-mail training!TELL ME MORE

OurQuestion and Answer Forum now has over 2800 members with about 20+ members joining each day. You will find the members are from all walks of life and Countries and have skill levels ranging from the total Excel new comer through to the Excel guru. Our board moderators and administrators: Jack in the UK, Richie(UK), royUK, WillR and (XlDennis ex moderator) are extremely helpful and are very special kinds of people. I simply cannot thank them enough for their HUGE contributions to the board. They are also many others that frequent the board that are so willing to share their knowledge. I wont mention any names as I am bound to leave a few out and offend someone. You guys and girls know who you are, so from myself and all of OzGrid THANKS!

Excel Tips and Tricks

One request that pops up again and again from Excel users is, "How can I create a list of data that is dependent on what is chosen from another?" The short answer is to use Excel VBA, or better still, use Data>Validation, with the 'List' option along with some Dynamic Named Ranges that have been manipulated to suit. The Excel Functions that will be needed for this are the ADDRESS, INDIRECT, MATCH and COUNTIF.

First thing we must do is create 3 lists on a Worksheet named "Lists". So, in cell A1 enter the heading "Objects", in B1 enter the heading "Corresponding List". Leave cell C1 blank for now.

Now in cell A2 enter the word "Can", use the Fill Handle (small black square bottom right of selection) to drag this down to A5. In cell A6 enter the word "Sofa", use the Fill Handle to drag this down to A9. In cell A10 enter the word "Shower", use the Fill Handle to drag this down to A13. In cell A14 enter the word "Car", use the Fill Handle to drag this down to A17.

Now we need to enter some corresponding data in cells B2:B17. Here is what my limited imagination came up with. Cells B2:B5: Tin, Steel, Opener, Lid. Cells B6:B9: Bed, Seat, Lounge, Cushion. Cells B10:B13: Rain, Hot, Cold, Warm. Cells B13:B17: Trip, Journey, Bonnet, Boot.

Next select cells A1:A17 and go to Data>Filter>Advanced Filter. Opt to 'Copy the list to another location. Ensure the 'List Range' is A1:A17. Leave 'Criteria range' blank. Enter C1 in 'Copy to'. Now check 'Unique records only' and click Ok. Now just change C1 from "Objects" to "Validation List".

Now add another sheet to the Workbook and call it "Sheet1". We next need to create 5 named ranges. 2 of which will be dynamic and the other 3 standard static named ranges. To create these named ranges, go to Insert>Name>Define and enter the names as show below, clicking 'Add' as you do each one:

Now select cell D6 on Sheet1 and go to Data>Validation-Settings. Choose 'List' from the 'Allow:' box and in the 'Source:' box type: =ValList. You can if you wish, click on the 'Input Message' and 'Error Alert' page tabs and set these up.

Now select cell E6 on Sheet1 and go to Data>Validation-Settings. Choose 'List' from the 'Allow:' box and in the 'Source:' box type: =CorrespondingList. Again, you can if you wish, click on the 'Input Message' and 'Error Alert' page tabs and set these up.

Now you are all set. Choose any option from the list in cell D6 and you will get the list associated with your choice in E6. You can see a fully setup and working version of this on this page: http://www.ozgrid.com/download/default.htm Just scroll down to "MatchingLists.zip".

Adertizements

Microsoft Excel Time Sheet Template

Need an simple and very easy to use Time Sheet, see this page:http://www.ozgrid.com/Services/ExcelTimeSheetScreen.htm

Ace Money

Ace Money is a stand-alone application that helps people organize and manage their personal finances quickly and easily. Ace Money is available in English, Spanish, Russian, Italian, Dutch, French, Portuguese and Sweden languages. You wont believe the price!http://www.ozgrid.com/Services/ExcelAdd-insFinancial.htm

New and Very Well Priced

Use this Excel add-in toolkit to empower yourself. It removes duplicates - you can dedupe by matching one table against another, or filter, count, purge duplicates by key fields. You can do Access-like query, aggregation, and join. It simplifies pivot table with a one-step summary, no file size inflation, and simple table output and the list goes on:                            http://www.ozgrid.com/Services/ExcelAdd-insProductivity2.htm#DigDB

Free Alexa Toolbar

Kill those annoying Pop-ups. Search via the worlds best search engine (Google) anywhere at anytime and much more. The price is right, Free!http://download.alexa.com/alexa7/Start page.html?p=alexadownload&amzn_id=ozgridbusines-20

Workbench

Do you have more than one Access database to administer, work in more than one version of Access or have more than one security file? If so then you might need Garry Robinson’s latest program. If you also have trouble compacting and editing the databases because they are always in use, then The Access Workbench will help you out. Download your trial version at: http://www.vb123.com/workbench/

Excel VBA Tips and Tricks

In keeping with the most frequent requests, let see how we can use Excel VBA to keep our sheets fully protected, but still allow our macros to run unhindered.

One easy and common way is to use some code like this:

Public wsSheet As Worksheet
Public strPassword As String

Sub AnyOldMacro()
'Set public variable to the sheet we are _
 going to work with
    Set wsSheet = Sheet2
        'Just in case of a Run Time Error.
        On Error GoTo ReProtect

    Run "UnProtectIt"

        'Any code that acts on sheet2 here.

ReProtect:
Run "ProtectIt"
End Sub

Sub UnProtectIt()
'Pass a password to our public string variable _
 based on the sheet Set to our Worksheet variable

'Enure we have Set the Worksheet variable
    If wsSheet Is Nothing Then Exit Sub
        Select Case wsSheet.CodeName
                Case "Sheet1"
                    strPassword = "Carrot"
                Case "Sheet2"
                    strPassword = "Secret"
                Case "Sheet3"
                    strPassword = "Password"
        End Select
    wsSheet.Unprotect Password:=strPassword
End Sub

Sub ProtectIt()
    wsSheet.Protect Password:=strPassword
        'Destroy public variable and release memory
            Set wsSheet = Nothing
            strPassword = vbNullString
End Sub

This code will account for any Worksheet protection. It does however rely on you using the Set Statement for the Worksheet your macro will be working with. It is important to note the line of code: On Error GoTo ReProtect in the "AnyOldMacro" Procedure. This ensures that the Worksheet in question will not be left unprotected should your macro have any problems. You would probably want to add some other Error handling in your code that is relevant to your macro.

This method is one that I used all the time in Excel 97 and is possibly the safest for that version of Excel.

The other method (now preferred for Excel 2000+) is the extra Argument we have available to use in the VBA Protect Method. This is the UserInterfaceOnly Argument. The Excel VBA help defines the Argument as:

UserInterfaceOnly  Optional Variant. True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface

By default, if omitted, this is automatically set to False. If you have Excel XP (2002) you will also have many more optional Arguments available to you. Just be very aware that these Arguments are NOT supported in earlier versions. However, the UserInterfaceOnly Argument is. This then begs the question of why we don't use it in Excel 97. Well you can, but I have found on a few occasions the Worksheet is left fully unprotected after running a macro. One Method that caused this problem was the FillAcrossSheets Method. So if you use it in Excel 97 be aware!

Ok, so just how do we use this optional Argument with the Protect Method. Before I show you it's important to read this from the Excel VBA help.

Remarks
If you apply the Protect method with the UserInterfaceOnly argument set to True to a worksheet and then save the workbook, the entire worksheet (not just the interface) will be fully protected when you reopen the workbook. To re-enable the user interface protection after the workbook is opened, you must again apply the Protect method with UserInterfaceOnly set to True.

If changes wanted to be made to a protected worksheet, it is possible to use the Protect method on a protected worksheet if the password is supplied. Also, another method would be to unprotect the worksheet, make the necessary changes, and then protect the worksheet again.

As you can see, as soon as our Workbook is closed, then re-opened the UserInterfaceOnly argument is back to its default of False. Fortunately we can account for this by using the Workbook Open Event place in the Private Module of the Workbook Object, ThisWorkbook. To get to this Private Module right click on the Excel icon, top left next to "File", select "View Code" and paste in this:

Private Sub Workbook_Open()
Dim wsSheet As Worksheet
    For Each wsSheet In Me.Worksheets
            Select Case wsSheet.CodeName
                        Case "Sheet1"
                            strPassword = "Carrot"
                        Case "Sheet2"
                            strPassword = "Secret"
                        Case "Sheet3"
                            strPassword = "Password"
            End Select
                wsSheet.Protect Password:=strPassword, _
                            UserInterFaceOnly:=True
    Next Wsheet
End Sub

You will need to change the passwords and Sheet CodeNames to suit each sheet in your Workbook. Note the it is not necessary to unprotect any Worksheet before setting the UserInterfaceOnly Argument to True.
 

Until next month, keep excelling!

Want to learn Excel VBA that will apply to your needs? We have your answer!OzGrid Excel VBA Training Over 1200 Excel VBA Examples that you can add directly into Excel:The VBA Collection Still our best seller!

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 ofMicrosoft Corporation
Read this issue and past issuesonline here :