Find me on Facebook
Visit my Briticiser Facebook Page
to keep up with things here - lots of reviews of stuff I’ve really used and also all kinds of helpful computer tips with a heavy bias towards SAVING YOU TIME.
As I’ve just discovered after so many years of using Excel if you merge a group of cells and then use a lookup (I only ever use VLOOKUP but it will probably apply to HLOOKUP too) to return the value from that merged group of cells, then you run the risk of getting the wrong result.
Here’s why, and why you should NEVER use merged cells in part of your spreadsheet which you might (one day) want to look up date from via a formula…. [Read More…]
But only a little. I create shortcuts using VBA all the time and - up till now - I thought that the only extra field which was possible to display information about the target was “Description”, so I was surprised to see a shortcut which I created manually (just in Windows Explorer) showing the title field from the target file. [Read More…]
For anyone who has a problem trying to save a Word 97-2003 Document (this may apply to other Office file types too) in Office 2010, specifically where you receive a message saying “The save failed due to out of memory or disk space” then there is an easy fix for this. [Read More…]
If you frequently switch between one job or project and another then this Task Time Counter which runs in Excel might be just what you’re looking for.
It’s very simple to use - just click on the Switch Task button to bring up your list of jobs and select one by clicking on the drop down arrow.
When you open an Word document from an email and start making changes to it, it’s easy to forget that you’re only editing a “temporary” document. If you save your changes it’s often quite difficult to find your revised document again.
To help avoid that here’s a nice snippet of VBA code which you can use to issue you a warning message every time you open a document from an email, like this:
As a kind of “bonus” the following script will also warn you if the document you are opening has tracked changes enabled, and ask if you want to turn them off.
To install the code open up Word and press Alt+F11 to get into the Microsoft Visual Basic Editor.
In the left hand pane under “Normal” double click “Microsoft Word Objects” then double click “ThisDocument”, and enter the following code in the pane on the right. It should all look something like this:
And here’s the code to put in there. Please note that you’ll need to remove the apostrophe from the beginning of the line saying ‘MsgBox(ActiveDocument.Path) and then open an document from an email in order to find out and jot down the Windows file path of your temporary folder, which you need to enter later in the code. I figure if you’ve been brave enough to go this far then this step should be plain sailing for you :)
Private Sub Document_Open()
' the following code will determine if the document you are opening
' has tracked changes enabled and ask if you want to turn them off
' it will also warn you if you are opening a document from an email
' in case you intend making changes to it
Dim TurnOffTrackedChanges As Variant
' Uncomment the following line and open a Doc from an email to find out what your temporary folder is
If ActiveDocument.TrackRevisions = True Then
TurnOffTrackedChanges = MsgBox("This document has tracked changes enabled." & vbCrLf & vbCrLf & "Do you want to turn tracked changes off?", vbYesNo, "WARNING - Tracked changes enabled!")
If TurnOffTrackedChanges = vbYes Then
ActiveDocument.TrackRevisions = False
MsgBox ("Tracked changes have been disabled :)")
' You need to put your temporary folder name below - uncomment the message box at the top to find out what it is
'example for WinXP
If ActiveDocument.Path = "D:Documents and SettingsPaulLocal SettingsApplication DataOperaOpera 11.64temporary_downloads" Then
' Example for Win7
'If ActiveDocument.Path = "C:UsersLee CliffordAppDataLocalOperaOperatemporary_downloads" Then
MsgBox ("WARNING! You may have opened a temporary copy of this document from an email as the current file path is" & vbCrLf & vbCrLf & ActiveDocument.Path & vbCrLf & vbCrLf & "If you intend making any changes you should save it RIGHT NOW to a different location... or you could lose your changes!"), vbCritical
Unless you’re familiar with Word documents which have tracked changes enabled it can be a bit confusing when you start editing such a document and everything you type is in red and underlined. Unless you know why this is happening you might waste a lot of time re-formatting everything you type.
Fortunately there IS a way to automatically detect if the document you are opening has tracked changes enabled, and pop up a box asking if you’d like to turn this feature off. Here it is. [Read More…]
Making full use of the document properties sheet for a Word document (or any document) is not only good practice but it’s more useful than you might at first think because you can use this information in a number of ways, not the least of which is to simply see what the document is about without having to open it, often simply by hovering your mouse over the file name in Windows Explorer…
Setting the document properties is a bit of a chore, to be honest, and busy people tend to follow the line of least resistance… and just ignore it. Even though some companies insist that their staff use the document properties to enable the documents to be more easily searched and sorted, it’s a difficult thing to police.
Here’s a much easier way to set the document properties which really is as simple as selecting some text and doing a simple keystroke. This example shows you how to set the document’s “Subject” field. [Read More…]
[updated May 2015 - VBA code updated to check for a hyperlink in a table cell, and just remove the white space from the displayed text while leaving the hyperlink intact]
It may happen that you need to remove white spaces from the ends of lines in Word documents - not just in the main text, but in table cells too.
Removing white spaces from the ends of lines in normal paragraphs is relatively easy - just do a find and replace of ” ^p” (that’s a space followed by the “hat” character which you can usually get by typing Shift+6) followed by a p, with “” (nothing). “^p” stands for the paragraph mark, or pilcrow (¶) which you can see if you show all of the non-printing characters in your document.
This will instantly rid your document of all those unwanted spaces. But what about the same thing in table cells? For this we need to box a bit cleverer. [Read More…]