Select visible cells only in Excel 2007

There are two ways to do this. If you don't need to use it much, then you can get to it directly through the links on the menu. On the Home tab, go to "Find & Select" on the right. In that menu, select "Go To..." Click "Special..." Here is where you can choose the "Visible cells only" option. Click OK, and that will select the visible cells only. This method is also outlined in Microsoft help pages, and can be found here.

If you need to do this often, going through all of those steps may get annoying. The good news is that you can create a shortcut for it on your quick access menu. To do this, right-click on the menu bar and select "Customize Quick Access Toolbar...". In the "Choose command from:" drop down list, change the option to "All Commands". This will show all available shortcuts that you can place in your toolbar. Scroll down in that list to find the "Select Visible Cells" shortcut, which looks like 4 black boxes. Select it, then click Add>>. This will add it to your quick access toolbar. Now click OK. Look in the upper-right and you should now see the visible cell selection shortcut icon next to the save, undo, and redo links in the quick access toolbar. You can use this same method to add (or remove) additional shortcuts to your toolbar.

Free Mac OS X anti virus software

Odds are most Mac users will never need antivirus software. Out of the 35-40 Mac users I manage, I don't have any AV software installed at all. However, that doesn't mean you can't use it if you feel you need to. There's a free package out there called ClamXAV if you're interested. If it'll help you sleep easier at night, by all means, give it a try.

Product key problem when installing Office 2004 on OS X

I have 3 legitimate product keys for Office 2004 for Mac, but none of the original install media. However, I have plenty of Office 2004 installation CDs from other machines, so I thought it shouldn't be too hard to just use one of those with my unused product keys. That was a wrong assumption. I double-checked the keys I had typed to make sure I hadn't transposed any letters, and also tried a couple of the keys, but the installation continued to tell me that the product key was not valid. At this point I had no choice but to turn to Microsoft Support.

I was told that this was a bug with Tiger and Leopard, and that if I did a manual install and updated to the 11.5.0 upgrade prior to trying to activate, the problem should be solved. Sure enough it worked. I couldn't find any documentation of it anywhere though, so hopefully others having the problem run across my post.

To perform a manual install, just drag the Microsoft Office 2004 folder from the CD to your Applications folder. This can take a short time while the files are transferred. After that, go to http://www.microsoft.com/mac/downloads.mspx and download the 11.5.0 update. Once downloaded, you'll have to launch the installer. Go through the install process, and once that is finished, try to launch one of the Microsoft Office applications. You should be prompted for the product key again, and this time it should work. Once you're in, I'd recommend going through the rest of the updates available using the AutoUpdate feature (under the Help menu).

It seems like a silly problem, but at least it's not too difficult to resolve. Hopefully this saves you from pulling hair and turning to Microsoft for help. If you're going to need to open the newer docx, xlsx, pptx, etc formats with your new Office 2004 install, you should check out my other post. Good luck!

DIY - How to replace a Macbook Pro screen/display

I had a user drop her 15" Macbook Pro last week, which pretty much shattered the screen. The laptop is still under Applecare, but things like this aren't covered. I talked to Apple and they gave me a quick quote of $1,200 to replace the screen. At that rate, a person is almost better off buying a new Macbook. After laughing at that, I decided to see just how cheaply I could replace the display on my own. Turns out I was able to do it for $214, and it took about an hour. Here's the story:

First, I turned to Google shopping and Ebay to see what the prices on a replacement screen were going to be. They varied, but I was able to find one for $200 (+ s/h of ~$14) from a user named macrecycling. That seemed like a pretty good deal to me since I didn't expect it to require $1,000 in labor costs to put the new screen in. I ordered the new screen and it arrived in a few days.

Next, I needed to find directions as to how you can remove the screen from the laptop. Normally this isn't too bad, but with all Apple products, they like to make it a little more difficult to just take it apart without instructions or training. A quick Google search brought me to www.ifixit.com, where they have a step-by-step guide to removing the display. It was very helpful, but it stopped at a crucial point of the process. It didn't show how to actually remove the screen from the case. Another quick Google search led me to www.screentekinc.com to their Macbook display removal guide. Luckily they skipped over most of the instructions given at ifixit.com and picked up where the other left off. Using the two sites, I was able to get the screen pulled out, and the new one installed in approximately an hour. That sure beat spending $1,200 to have someone else do it.

One thing to keep in mind when looking for your replacement screen is to make sure you're ordering the correct one. Apple made a change in their Macbook screen in 2007, so newer ones are LED backlit and older ones aren't. If you're not sure which one you'd need, you can either try Apple's Tech Specs page (requires the serial number of your Macbook), or try contacting the seller to see if they'll help you determine the correct replacement.

Another thing to remember is to take your time and be careful. Anytime you try replacing parts on your own, you run the risk of damaging the computer. Also, replacing hardware on your own can sometimes void the warranty, so you may want to check into that and take it into consideration before proceeding with any replacements.

Sharp MX3501N stuck offline during maintenance warning

We have two Sharp MX3501N printers and they've been great for us. One of them had a "Needs maintenance, code FK3" pop up yesterday, so I called in for service. It's a routine change of the fuser web kit, whatever that is. I know if helps keeps things clean, so without it the prints can get spotty. Anyway, the tech came out and found that he'd need to order a part. He said the printer would work in the meantime, but for some reason it was registering as offline on our print server and the clients. It seemed to print directly from a Linux app we use though, which seemed odd. I did some digging and found the answer.

In Windows, the Sharp driver will force the printer into offline mode if it detects a maintenance warning, regardless of what it is. This is because SNMP is enabled on the printer port by default. It is simple to disable. What you need to do is go into your Printers & Faxes, right-click your Sharp printer, then select Properties. From there, go to the Ports tab, select the Port that is active for your printer, then click on Configure Port. Towards the bottom you will see a box that is checked and reads "SNMP Status Enabled". Uncheck that box, then click OK a few times to get back to your Printers & Faxes window. Check to see if the Sharp printer is back to the "Ready" state, and if so, anything stuck in the queue should be printing too.

I'm assuming this carries over into other Sharp printers, but I can verify that it worked with the MX3501N printer that we were having issues with. I also found that this only seemed to affect Sharp printers using the default IP port settings. The Sharp-specific ports didn't have any SNMP options available, so using a Sharp port is a better option anyway. However, I just ran into a problem where the printer was registering as offline using the Sharp ports. To fix it, I changed the port to a generic TCP/IP port using that same IP address, and everything printed out right away. 

I originally found the answer here.

Outlook attachment error, can't create file

If you're one of those users who is notorious for opening all attachments without downloading and saving them first, you may have run into this problem before. Eventually you may get an error when trying to do so, which says:

Can't create file: yourfile.ext. Right-click the folder you want to create the file in, and then click properties on the shortcut menu to check your permissions for the folder

This can be very annoying, especially since you will most likely experience it with only a select few of your files. However, the good news is that it's not a big deal and is easily fixable.

The problem is that every time you open an attachment without saving it first, Outlook will first download it into a temporary folder and then open it from there. Unless you keep up on cleaning this temp folder, the files eventually build up. Each time you open a file with a name that already exists in this temp folder, Outlook with append a number to the name (e.g. "file.txt" opened a 2nd time would be saved in the temp folder as "file (2).txt"). This is where the problem comes in. Outlook is limited to 100 versions of the same file, so once your file name reaches "file (99).txt" (starts numbering at 0), the next one will fail to open and you'll get the error above. The fix is to go to the temp folder and clear the other copies of the same file, or if you want to be really good, empty the folder completely.

In order to clean out the temp folder, you'll first have to know where it is stored. This is really the only tricky part because it can vary from machine to machine. However, the location is stored in the system registry so you can retrieve it from there. Here's how to do this for Outlook 2003

1. Go to Start->Run, type "regedit", then press Enter. This will bring up the registry editor tool. Be careful not to make any changes to the registry as they can have adverse affects on your computer.

2. Navigate to HKEY_CURRENT_USER->Software->Microsoft->Office->11.0->Outlook->Security registry key

3. Look for the OutlookSecureTempFolder value. This is the folder location you'll need. It's easiest to double-click on it, then copy the path of the folder

4. Close Registry Editor

5. Go to Start->Run, then paste the folder path in the box and press Enter

6. The folder should pop up in Windows Explorer. This is where you'll want to delete the files from

7. Now that the files are deleted you can close that folder and try opening your attachment again

The easiest way to avoid this would be to first save your attachment to a location you're aware of, and then open it from there. Since 90+% of you will probably never do that, this will at least allow you to continue opening attachments

I haven't run into this problem with Outlook 2007, but that doesn't mean it doesn't exist. If you're having the issue with Outlook 2007, just replace the key in step #2 with HKEY_CURRENT_USER->Software->Microsoft->Office->12.0->Outlook->Security. That is where Outlook 2007 specifies its value for the temp folder

The original post that helped me figure this out can be found here.

Applescript to automatically replace characters in filename

Here's the second half to my post regarding automatically adding file extensions to files in OS X.

If you're in a mixed environment where Mac and PC users are, you know that it can sometimes be a challenge to get everyone to name files so that they are compatible with both types of computers. Normally the Mac users are the culprits because Macs allow a wider range of characters in the filenames. Configuring folder actions, and using a simple Applescript, you can automatically remove any characters you want from the file names though, or replace them with a different character of your choosing. Here's the Applescript:

--set the list of characters you want to replace
--disallowedChars will be replaced with the replacementChar

--in this case, an underscore
property disallowedChars : ":;,/|!@#$%^&*()+" 

--anything in disallowedChars2 will be removed altogether

property disallowedChars2 : "'"

--set the character you'd like to use to replace the invalid 

--characters specified in disallowedChars
property replacementCharacter : "_"

on adding folder items to this_folder after receiving added_items

tell application "Finder"
try
repeat with x in added_items

set fileNamed to name of x

set newName to my CleanName(fileNamed)

set (name of x) to newName

end repeat

on error

display dialog "An error occurred."

end try

end tell

end adding folder items to

--function for cleaning the characters from the file name

on CleanName(theName)

set newName to ""

repeat with i from 1 to length of theName

--check if the character is in disallowedChars

--replace it with the replacementCharacter if it is

if ((character i of theName) is in disallowedChars) then

set newName to newName & replacementCharacter

--check if the character is in disallowedChars2

--remove it completely if it is

else if ((character i of theName) is in disallowedChars2) then

set newName to newName & ""

--if the character is not in either disallowedChars or

--disallowedChars2, keep it in the file name

else

set newName to newName & character i of theName

end if

end repeat

return newName

end CleanName

Using that script in combination with OS X folder actions will allow you to successfully replace any character you want with any character you specify. The only things you'd need to modify are the disallowedChars, disallowedChars2, and replacementCharacter variables. 

Add empty item to databound drop down list in C# .NET

Do you have a data-bound drop down list, but want to leave the list empty or blank initially? Well, you can, and it's really simple. In your aspx page, where you declare your DropDownList, just make sure to do it as follows:

<asp:DropDownList ID="yourID" runat="server" AppendDataBoundItems="True">
<asp:ListItem Value="" Text="" />
</asp:DropDownList>

The key here is the AppendDataBoundItems="True" property. This allows you to define your own list items, and then any items that are bound to data are added after those items, aka appended.

You can find the original post at http://stackoverflow.com

However, if you find that you're getting the same items added multiple times you can do it this way instead. Leave AppendDataBoundItems="False" and rather add the empty/blank entry programmatically. To do that add the following code after your drop-down has been filled

DropDownList1.Items.Insert(0, new ListItem(String.Empty, String.Empty));
DropDownList1.SelectedIndex = 0;

This will add an empty/blank entry at index zero of your dropdown box, and also make sure that it is the selected value. If you don't want to change it to the selected value don't use the second line. I found this on http://stackoverflow.com as well

Embed or un-embed a linked file in an Illustrator CS3 document

The trick here is paying attention to the options in the Link screen. The symbol for showing that a file is embedded is , which will show up next to the file in the Link panel. If that symbol is not there, then the linked file is just that, linked only.

To embed the file, simply clear the Link checkbox at the bottom left of the window when you select the file to be linked. That will embed it rather than create a link. 

If you want to link a file rather than embed it, make sure the Link checkbox is checked when you place the file into your document. If you've already embedded it, you can go back in and check that box to change it to being linked.

Just watch for the "embedded" image, or lack of, to keep track of the status of your placed document. There's an Adobe article on how to do this same thing, and it can be found here.

Send email via command line with sendmail

So you want to make sure sendmail is running and able to send email messages, but can't quite figure out the command to test it from the command line? It's easier than you may think, but it requires you to create an input file to specify the parameters.

Create a new file, we'll call it email.txt, with vi or whatever text editor you use. Then add parameters for the SMTP headers that you want to include in the message, such as "From:", "To:", and "Subject:". You also MUST remember to leave an extra, empty line at the end of the document because that signals the end of your email message. An example of the text for a basic message would be:

From:me@myemail.com
To:you@youremail.com
Subject:Hello, thanks for reading

Hi, I appreciate you reading this blog post.

You can specify other header parameters in the file as well, such as date, cc, etc by simply adding an entry for them. If you want to find out more, check the SMTP Wiki, or perform your own Google search to find resources.

After you've saved that text into your email.txt file, now you're ready to use sendmail to send this message. You can use the -t switch to import your file.

/usr/sbin/sendmail -t < email.txt

That is assuming you're still in the same directory as your email.txt file. If not, you'll want to specify the full path to the email.txt file so sendmail knows what to import.

If you're looking for more information, check out the original post I used to answer my question about sendmail.