Office » Ross Goodman

Microsoft Office–VBA Editor Keeps Opening

Microsoft, Office, Windows, Work No Responses »

Oct 072011

For weeks now I have had the mild annoyance that the VBA code editor for various Excel spreadsheet’s appear to be opening at “random”. I finally tracked it down to me unlocking my PC. If I have excel running then lock my PC, when I unlock it, the VBA code editor appears.

I’m using Office 2003 SP3 on XP Pro SP3.

Turns out that there is a VERY easy fix – don’t maximise the VBA code editor.
If the editor is maximised, it appears on unlock, if it is not maximised it does not appear automatically on unlock.

Changing Excel Cell Values To Uppercase

Microsoft, Office No Responses »

Aug 232011

A nice quick tip, I had lots of cell values in a mixture of cases that should have been uppercase.

Create a macro:

sub MakeUpper()

for each myCell in Selection

myCell.Value = UCase(myCell.value)

next myCell

end sub

Add a menu item to your toolbar and then assign this macro to it.

Select the cells you want to upper case then press the button!

Update All Fields In MS Word

Microsoft, Office, Work No Responses »

Apr 192011

Sometimes you find a feature in a product that is just blatantly missing. One of those in Microsoft Word is the ability to update all the fields in the document with the latest value in a field.

I use both the built in fields e.g. Author, Version, Title and custom fields e.g. Project Code. I set the values once in the document properties and then insert the field codes in the document rather than the text. All is well. If you change the value of a field you can press F9 and all of the fields update to the latest value EXCEPT if you have used the fields in the document header or footer, these do not get updated for some reason. I would classify this as a bug, why would I not want all instances of the same field to be in sync throughout my document.

The solution is remarkably simple. Create the following macro:

Sub UpdateAll()
Dim oStory As Range
Dim oField As Field
For Each oStory In ActiveDocument.StoryRanges
For Each oField In oStory.Fields
oField.Update
Next oField
Next oStory
End Sub

This can be saved in your normal.dot file and will be available in all your word documents.

Favourite Shortcut Keys – Outlook

Microsoft, Office, Technology 2 Responses »

Aug 092010

photo credit: laffy4k

If you look at the left hand menu you will see the items:

Mail
Calendar
Contacts
Tasks
etc

These can be quickly accessed by using the following shortcut keys:

(Ctrl+1) – Mail
(Ctrl+2) – Calendar
(Ctrl+3) – Contacts
(Ctrl+4) – Tasks

In addition, when you use Ctrl+1 to access mail, this takes you to the folder that you were last using, if you would rather jump straight into your In-Box – use (Ctrl+Shift+I).

Google Calendar Sync – Auto Run

Microsoft, Office 9 Responses »

Apr 162010

A nice quick hint.

I kept forgetting to start Google Calendar Sync when I started Outlook. I don’t always start Outlook when I start my laptop and I’m not always online when I run Outlook so puting it in my Startup folder is not really an option.
My solution, a macro that runs when Outlook starts that gives me the option of running Google Calendar Sync if I feel it’s appropriate.

Private Sub Application_Startup()
Dim response As Integer
Dim RetVal

response = MsgBox(prompt:=”Do you want to run Google Calendar Sync?”, buttons:=vbYesNo)

If response = vbYes Then
RetVal = Shell(“C:\Program Files\Google\Google Calendar Sync\GoogleCalendarSync.exe”, vbMinimizedNoFocus)
End If

End Sub

How Annoying is the Microsoft At Home RSS Feed?

Microsoft, Office 4 Responses »

Jan 272010

I’m using Microsoft Outlook 2007 and I think it’s a great piece of software.
It has a built in RSS feed reader. Typically I use Google Reader but as that can’t handle authenticated feeds I’m using Outlook for all of my work related Sharepoint feeds.
In order to introduce people to RSS, Microsoft have included two “standard” RSS feeds: “Microsoft At Home” and “Microsoft At Work”.
The only problem is, I’m not really interested in reading these feeds.
Continue reading »

MS Excel – Convert Percentage To Number

MIG, Maxima, Maxima Information Group, Microsoft, Office, Work 2 Responses »

Jul 252008

I have spent most of today throwing data around in MS Excel.

One of the things that I have had to do is to convert percentages into real numbers.

In excel percentages are stored as fractions, for example 50% is stored as 0.5, 75% as 0.75 etc.

I am trying to chart these figures in Business Objects Xcelsius but the y-axis labels are just showing 0 to 0 hence why i need to convert my numbers.

I have just found a really easy way to do this.

In the target cells, enter the value 100 in each cell.
Copy the source cells
Select “Paste Special” using the options “Values” and “Multiply”

This takes the source value 0.75 multiplies it by 100 and stores the result 75 !

I have used pase special – values often but I have never seen the need for the multiply option until now !

Microsoft Word 2007 Keyboard Shortcuts

Microsoft, Office, Work No Responses »

Jun 292008

I use Microsoft Office 2008 on my work laptop. Whilst I am no touch typist; I wish I could take the productivity hit whilst I learn, I do prefer to use the keyboard for most functions, growling when I have to resort to using the mouse.

I was torn by the ribbon interface; for 80% of the things that i want to do i can find it very quickly, the remaining 20% often required a trip to help to find it. Another thing that I hated was that I “had” to use the mouse to access the ribbon features.

Then I pressed the “Alt” key !!!!!!!

This pops up an overlay on top of the ribbon interface that shows you which key to press to access each feature. Or should I say, EVERY feature. If you can see it on the ribbon, you can access it via a keystroke. I’m beginning to like this user interface.

The remaining bugbear is that I always use styles, nost commonly the heading styles and normal. In the previous version of Word I always assigned my own keystroke shortcuts to access them, but I was always over riding exisiing keystrokes and features.

In Word 2007 I have found that there are system assigned keystrokes:

Ctrl + Alt + 1 = Heading 1

Ctrl + Alt + 2 = Heading 2

etc

Strl + Shift + N = Normal

Productive typing here I come !