I’m so excited I’ve found an intelligent source for answers! In perusing the SDMB General Questions, I noticed a couple of inquiries that just ask for computer help with specific problems, and my favorite annoyance came to mind. Maybe someone can actually help me with it!
The problem (preface): I have a Microsoft Excel document (from the Office 2000 suite, if the version makes a difference) where I keep track of a list of clients and certain info on each of those clients, including their eMail address (if any). I also have a column indicating if they have chosen to receive an eMail with a monthly newsletter link. This spreadsheet is sorted alphabetically by last name.
When it comes time to send the newsletter eMail, I sort the spreadsheet first by the “did they choose to receive eMail” column, then by eMail address, and copy the cells of the eMail addresses (which are now contiguous) into a blank Word document. I then do the same thing from a second Excel document that contains eMail addresses of people who are not clients but who have asked to be notified of the monthly newsletter link. In the new Word document, I convert the table into text, use universal find and replace to replace each Paragraph mark with a space and a comma, and then after opening the eMail program, paste the resulting addresses into my new eMail “Send To” field.
The Problem (for real now): Excel makes every cell that has just an eMail address a hyperlink. If you click on it (in Excel) it tries to open up your default eMail program to send off eMail to that address. This is extremely annoying. I can’t click on any such cell (to edit it or move it or whatever) without having to waste my time while another program opens up and I then can exit it to get back to Excel. Instead, I have to click on a nearby cell and cursor over to the one I want. I have searched every menu item and option looking for a way to turn this “feature” off, either universally or per cell, but to no avail. The best I can find is the “format/style” command, which allows me to change the style from “hyperlink” to “normal.” This does change the color of the hyperlink from blue back to black and removes the underline, but even though it now looks the same as the rest of the data, it will still act as a hyperlink when you click on it.
Aside from the fact that this seems really stupid to me (why would anyone want to automatically open “send eMail” or a web page when they click on a hyperlink of any kind in a SPREADSHEET PROGRAM, for god’s sake?) it is even more ridiculous that there is not an easy way to turn this off.
Unless it’s just me being stupid. Is it? Anyone know how to do this?
Yeah, that is an annoying “feature.” There is a way to remove a hyperlink (right click on the hyperlinked cell and select “hyperlink” – one of the options is “remove”) but it can only be used one cell at a time, which is even more annoying.
I got around it by using a delete hyperlinks macro and setting up a keyboard shortcut to run it. You could also attatch the macro to a button on your toolbar.
The macro is really simple:
Sub deletehyper()
Selection.Hyperlinks.Delete
End Sub
Just make sure you select the column by clicking on the column letter – if you try to just select cells within a column, it doesn’t seem to work for every hyperlink.
There is a quick way to remove the hyperlinks from a range of cells: First, highlight the cells. From the Format menu, select Style. Pick the Normal style and click on OK. No more hyperlinks.
And if I had actually read the OP carefully, I would see that this doesn’t address the real problem. It looks like BoS has the best way to handle this.
One more try. I found this article on the Microsoft site that gives two methods for disabling the automatic hyperlink function.
The first way is to put a single quote before the email address, which forces the entry to be a text field (similar to putting the single quote before a number). The second way is through an “Application-Level Event Handler.” You create a Visual Basic script to disable the hyperlink formatting. The page gives the source code you need – just cut and paste in the appropriate place. It’s pretty quick to set up, but it only works on the existing workbook unless you have it run whenever you start Excel (which I haven’t tried).
OK, another question for the Excel geniuses. If I type in 8/7 it automatically becomes 07-Aug. Is there any way for me to make it stop doing this other than writing 8/7/?
Sure thing. Depending on what you want your 8/7 to be, you can either format it as text or change the date format.
To format it as text, right click the cell, choose Format Cells, and on the Number tab select Text. Make sure you format it before you type 8/7 or the you will wind up with the numerical value of the date instead of the text of “8/7”
To change the date format, right click, choose Format Cells, and on the Number tab choose Date. Then select the Type you want (you can pick Aug-7, 08-07-01, August 7 2001, etc.) If you don’t see the format you want, select Custom from the Category box and type in “m/d” to get the date to display “8/7”
If you want text show up as you type it with no special formatting simply put a “’” in front of it and Excel will bypass any formatting. So for 8/7 you’d put '8/7 and so on…
Thank you, Bottle of Smoke. The right-click method works perfectly, and the macro allows a way to remove all the previous hyperlinks without having to go cell by cell.
I’ve never done an excel macro before, but by trying it I was able to figure out how to insert your code appropriately. I even figured out how to put a “custom button” to run the macro on the main toolbar, which I’ve never done either.
Thanks also to JeffB, and I’ll try the apostrophe method sometime just to see that it works. What I would be concerned about (and I’ll just have to use the trial-and-error method to see) is if the apostrophe beginning the cell text will be included when I copy the eMail addresses and bring it over to Word. If it does, I suppose I could still use it by find-and-replacing nothing for the apostrophe while in Word. Clicking the excel column and running Bottle of Smoke’s macro once I’m done entering that week’s batch of new clients or before I copy the eMail addresses seems easier.
I looked at that Microsoft article, and while it seems that using an “Application-Level Event Handler” would ultimately be an even better solution, as it seems to eliminate the hyperlink creating behavior to begin with, it is starting to seem way too complicated for someone like me who prefers not to have to work any harder than necessary. I’d have to figure out what they’re talking about to put the “Application-Level Event Handler” into an Excel Start-Up folder so it would run every time.
It still seems like an awful lot of trouble to eliminate a feature that is so ludicrous. I think Microsoft put it in there just to prove they could, knowing that nobody using excel has any reasonable options to change spreadsheets. You’d think they were a monopoly or something!