Poll

Aboot this blog

This blog is a cross between a scrapbook and a diary. I hope you find something here of interest. If you’d like to keep up with things as I see them then you can subscribe to my news feed by clicking the icon below:

feed-icon-28x28.png

DISCLAIMER

Please note that any reviews/recommendations are based solely on my own experience and does not constitute a guarantee that you will have the same experience. Please do your own research before parting with any money - the risk is all yours!

The Briticiser

Looking for my world famous US to GB English spelling converter?

THE “BRITICIZSER”

www.us2uk.eu

IT’S HERE

Tags

  Hardware     Bookmarks     Word     Windows         VBA     Browsers     Utilities     Overcoming Disabilities     Equipment     Bits and Bobs     3D Stuff     Education     Reviews     PHP     News     Flatpress     Odds and Sods     Software     Family     Acrobat     Excel     Tutorials     Plausible Lies  

Find me on Facebook

facebook-icon-28x28.pngVisit 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.

How to crack the password on an Excel VBA Project

Permalink: http://paul.us2uk.eu/?x=entry:entry170809-124855

I never set passwords on my VBA Projects. Initially this was out of pure laziness but they’re not secure anyway so setting one is pointless if the sole reason for setting one is to keep your code a jealously guarded secret.

Now, there are legitimate reasons why you might want to crack a VBA Project password and I recently needed to do just that on one of the workbooks in our office which was causing the user a problem when trying to copy and paste from one sheet to another: when the user selected a different tab they also “lost” whatever was on their clipboard so they couldn’t do the usual thing of selecting a cell in the sheet where they wanted to paste something when the “Select destination and press ENTER or choose paste” message appeared at the bottom, because this message disappeared when they selected a new sheet.

There was obviously some script being triggered when they selected this sheet and, if you carry out any operation on a sheet before you get chance to paste your information in, then that chance goes begging.

Unfortunately this particular workbook was one they’d inherited from a user who had long-since left the company and the VBA Project had been locked with a password to protect the code… but no-one knew the password.

Of course there are solutions out there which you can download or buy but, if you’re already comfortable with VBA (otherwise why would you want to get in there to fix things?) then here’s an easy solution which works for all versions of Excel, both 32 bit and 64 bit.

https://stackoverflow.com/a/27508116

Make sure you click the link (or scroll down the page) to see the corresponding code for 64 bit versions of Excel (you need to use the code which applies to your version).

Credit for this solution goes to Duc Thanh Nguyen.

The basic instructions are that you copy the code given into a new workbook, save that with whatever file name you want, then leave the workbook open.

Switch back to your workbook with VBA password protection enabled and click on the Developer tab, then Macros, and choose to run the macro called, simply, “unprotected” which should pop up a nice friendly message like this:

VBA Project is unprotected message box

Merging cells in Excel can lead to formula errors

Permalink: http://paul.us2uk.eu/?x=entry:entry160930-180600

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…]

Windows shortcut files are a little more useful than I thought

Permalink: http://paul.us2uk.eu/?x=entry:entry151214-092353

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…]

The save failed due to out of memory or disk space

Permalink: http://paul.us2uk.eu/?x=entry:entry150507-125645

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…]

Task Time Counter

Permalink: http://paul.us2uk.eu/?x=entry:entry141205-170816

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.

image[Read More…]

How to avoid losing changes made to a Word document opened from an email

Permalink: http://paul.us2uk.eu/?x=entry:entry141204-110640

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:

image

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:

image

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
'MsgBox(ActiveDocument.Path)

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 :)")

End If

End If

' 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

End If

End Sub

Goodius luckius!

How to detect if tracked changes are enabled when opening a Word document

Permalink: http://paul.us2uk.eu/?x=entry:entry140521-031442

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…]

A quick way to set the Subject in Word document properties

Permalink: http://paul.us2uk.eu/?x=entry:entry140131-170717

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…

image

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…]

Removing unwanted white spaces in MS Word

Permalink: http://paul.us2uk.eu/?x=entry:entry130605-170650

[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…]

Support our caregiving

If you found anything on this site of use, interesting, or even mildly amusing then please consider dropping a few pennies in the jar to help us to take care of our disabled son who contracted encephalitis in 2007 at the age of 6 and who is now confined to a wheelchair. He is getting bigger as his mother and I get older, imagine that, please. Every penny we collect goes towards his upkeep, and towards his future care requirements.

Thank you.

Support our caregiving

Support our caregiving

If you found anything on this site of use, interesting, or even mildly amusing then please consider dropping a few pennies in the jar to help us to take care of our disabled son who contracted encephalitis in 2007 at the age of 6 and who is now confined to a wheelchair. He is getting bigger as his mother and I get older, imagine that, please. Every penny we collect goes towards his upkeep, and towards his future care requirements.

Thank you.