EXCEL: Create a PullDown Menu [Quick Tip]

In this mini-tutorial I am using Excel 2010, but the steps are pretty much the same no matter which Office Version you use. The main difference is where you find the different tools, and where possible I will try to mention where things differ.

Creating a usable Pull-down Menu

If you use Excel on a day-to-day basis filling out forms and information, you might find yourself typing in the same information over and over. Either by typing it in or using copy and paste. When filling in forms you will normally use the same cells over and over (this is where we can use Pull-down Menus). Pull-down Menus can also control your Excel document in different ways like retrieve information or navigate between Sheets and Documents.

Activate the Developer Toolbar (Office 2010 Users Only)

To access the Form tools we have to activate the Developer Toolbar.

1. Click on the Green File Tab
2. Choose Options
3. In the Options Dialogue, click “Customize Ribbon”
4. In the List Window to the right, there is a list of all the available Ribbons.
   Locate the one Called: “Developer” and Check the box next to it
5. Click OK to Save

Creating a Settings Sheet.

First thing I do (when starting a new Excel Project) is to create a Sheet called Settings. This is to keep everything as clean and neat as possible. For this tutorial we will benefit from having a “Settings Sheet” as we get a place to keep the options for our Pull-Down Menu. You should always Index the Options when creating Pull-Down Menu Content. I will explain a bit later.

Create a new Sheet (or Choose an existing one) and Right Click it.
Choose Rename from the context menu,  Name the Sheet “Settings”

1. In the first column type the index numbers from 1 to X (where X is the number of lines needed).
2. In the Second Column, type in the Options (Weekdays for this example)

Now, head over to our form-sheet to add our menu.

1. Click the Developer Toolbar (Earlier Office, look for the FORM Controls)
2. In the Developer Toolbar there’s a menu-item called Insert, click the little arrow underneath to show the Form Controls menu.
3. Click on the ComboBox Control (#2 from left)
4. Draw the ComboBox in place (or simply click where you want it)
   
   Resize the control by dragging the handles in each corner.
5. Right-Click the ComboBox and Choose “Format Control”.
   In the dialogue box, we need to assign which cells from which we will collect the data,
   which cell to store the user choice, and what appearance we like.
6. Place the cursor within the Input Range Fieldbox (click it)
7. Click the Settings-Sheet and highlight the options area (Weekdays)
   
8. Activate the Cell-Link Field (this should automatically open the Form-Sheet) and Type in the CellReference to save the user choice in. In this example we use "B5" (which is hidden by the ComboBox).
   
9. Decide how many lines to display in the ComboBox (I set it to 7) and check the 3D-Shadow option.
10. Click OK
11. Click somewhere outside the ComboBox to activate it

Now when you click the ComboBox the weekdays should be appearing.

Practical use

It looks good right? So how do we put this to good use? If you were to print this form you would see the ComboBox appearing on the print. If you Right-Click the ComboBox and click the Properties Tab. Uncheck “Print Object” to make it invisible on print.

IF you do then the print will look something like this:

The weekday (or user choice) is represented by an index number (in this case #3).

Use ComboBox as LookUp Tool

We can use the index number to look up data corresponding to the user’s choice. If you use the ComboBox to retrieve Customer Names from a list, then we can use formulas to retrieve more data like contact person, address, phone number etc. In this example we will retrieve the Weekday from our Settings-Sheet, making it appear in real text instead of an index number. The procedure for fetching more data is exactly the same.

1. Right-Click the ComboBox and choose “Format Control”
2. Click the Properties Tab and uncheck the “Print Object”.
   
3. Click the Control Tab and Change the Cell-Link to "C5" instead of "B5"
4. Click OK
5. Now format the Text Color in Cell "C5" to white, making it seem invisible
6. With the ComboBox active (Right Click it) move the Combo Box temporarily away
7. Activate ce)ll "B5" and type in the following Formula:
   =VLOOKUP(C5;Settings!B3:C9;2)

   (if unsure how this works you can use the Formula Wizard by clicking the fx-button and choose VLOOKUP)
8. Test the Formula by changing weekdays in the ComboBox
9. When Successful, Move the ComboBox back (step 6) over the Formula field to cover it.

A Handy Guide To Microsoft’s Free Security Tools

Microsoft publishes a range of free security-related software.  One of the best-known is Security Essentials, the excellent antivirus/antimalware product which was recently updated to version 2.0.

Many of the other products in the collection are aimed primarily at large business customers, and are not of general interest to anyone else.  But of the handful of exceptions, MBSA has always stood out for me.  MBSA is the Microsoft Baseline Security Analyzer.  It’s a clever tool which analyzes your computer’s contents and configuration, and provides relevant security advice in areas such as missing critical patches, user accounts that don’t have passwords, and so on.

If you’ve never used MBSA before, it’s worth a try.  Version 2.2 is the latest, which was released back in August.  It’s available for all recent versions of Windows, both 32- and 64-bit flavours, and the initial download is only 1.6 MB.

You’ll find links to MBSA, as well as pointers to all of Microsoft’s security tools, on the excellent web page at http://technet.microsoft.com/en-us/security/cc297183.

 

The Easy Way to Repair Your Internet Connection

Sometimes, Windows loses its connection to the internet.  This happens regardless of whether you’re using an ADSL modem, an ADSL router, a cable router, a dial-up modem, or any other hardware device to connect.  And it happens regardless of whether your connection is cabled or wireless.

Troubleshooting connectivity problems involves running through a number of standard procedures, such as flushing the DNS cache, deleting and reinstalling the TCP/IP protocol, and more.  For even a techie, this can be tedious.  And if you’re trying to talk a non-technical friend or relative through the procedure, it’s virtually impossible.

So here’s a great idea that will make your life easier.  Complete Internet Repair is a free program for Windows XP and above.  You can get it from www.rizone3.com, and it’s only a 0.5 MB download.  As you can see from the screen shot below, it automates all of the common troubleshooting techniques for fixing connectivity issues.  Just tick the relevant boxes and press the Go button.  Then, when it’s finished, reboot your PC and you should hopefully find that everything’s working again.

While such programs can be useful, a couple of caveats before you rush to download it.  Firstly, heed the program’s own warning, as shown below, and don’t choose any option unless your computer is likely suffering from that specific problem.  There’s little to be gained by trying to fix a problem that doesn’t exist, and you might simply end up making the problem worse.

Secondly, because the program performs some low-level trickery and tweakery with various parts of the operating system, your anti-virus program may object and report that the program is a virus or that it’s exhibiting suspicious behaviour.  Rest assured that the makers of CIR are adamant that it’s free of malware and that it’s not a virus.

 

Computer and Tech Talk Shows on the airwaves

CPUG members: Here are the computer and tech talk shows that are currently on the airwaves. If you know of any others please leave a comment thru the blog or email me.

Thanks, Jon

 

-Computer America Show 1360 AM 10p-12a M-S

-Kim Komando Show 104.7 FM 9p-12a Saturdays (during baseball season Pirates broadcast on 104.7, so I will listen to her on WBAL out of Baltimore or WBAL.com)

-Leo Laporte ch. 158 SiriusXM or KFI 640AM out of L.A. or on the internet at live.twit.tv Sat. and Sun. 2-5pm

This Has To Be The Easiest Photo-Sharing Site Ever

The web is awash with photo-sharing sites, such as Flickr.  But while these sites are a great way to show off your own work to others, and to share photos that you’ve taken, they don’t make it particularly easy to share the job of contributing the images.  It’s generally accepted that one person will create and upload the pictures, and others will share the job of looking at them.

But a startup site called Project Minus (www.min.us)  aims to change all that.  It’s incredibly quick and easy to use, looks fantastic, and makes a superb job of allowing a group of people to upload and view images.  Ideal if you want to create a common repository for a group of people to upload their own pictures of, say, a wedding or a school play.

Assuming you have a supported browser (Chrome, Firefox, Safari, or IE9), just fire up the site, drag some photos onto the empty page, and you’re done.  Yes, really.  It takes just seconds, there’s no need to sign up, and the results are superb.  Some very clever Javascript code takes care of the uploading, and if you resize your browser then all the images get dynamically resized in real time too.

Once you’ve dragged your images, you’ll be allocated 2 unique URLs by which to access your collection.  One is for viewing, while the other allows editing (renaming existing pics, uploading new images, etc etc).  Just give those URLs to your fellow wedding guests, or proud parents, and the photo-sharing exercise is complete.

There are other features too. You can, if you want, sign up and create an account on the site.  For more news about what’s coming in the future, see the developers’ blog.

13 Must-Know Laptop Security Tips, Tools and Software

What do you fear the most when you travel with your laptop? Let me guess – it’s the fear of having your laptop stolen and your personal data compromised. Right?

Stories of laptops getting stolen at airports, restaurants, even from homes is nothing new. Hence you should have certain security measures in place to prevent your laptop from getting stolen, and if it does get stolen, to ensure that the data doesn’t fall into the wrong hands. This is what we will talk about today. (Photo by raindog )

This article lists several software products, hardware tools, laptop tracking tools and services, and some security tips. Most of them are free. I’ve tried to include tools that work in all operating systems so Linux and Mac users should check this out too.

Click below to read the entire article:

http://www.guidingtech.com/5222/laptop-security-software-tools-tips/

 

 

 

 

 

 

 

Free Training Manuals for MS Office

Mouse Training is a UK-based company that offers training courses to companies whose staff use Microsoft Office.  Following the release of Office 2010, the company has made available all of its training manuals for previous versions, totally free of charge.  So if you’re one of the millions of people using any of the Office 2003 or 2007 apps, and you’d like some in-depth information on how to use it properly, just head on over to http://www.mousetraining.co.uk/ms-office-training-manuals.html.

Near the top of the page you’ll find free downloadable Quick Guides to the Office 2010 apps (needless to say, the full manuals for Office 2010 aren’t free!).  Scroll down, and you’ll find the free guides for Office 2003 and 2007, including Word, Excel, PowerPoint, Visio, Outlook, FrontPage, Project and Access.

These are superbly-written tutorials, produced by professional trainers.  Whatever version of Office you’re using, they’re definitely worth a look.