Zembian

Problem

Using Microsoft Word 2007 I wanted to use my simple collection of macros that I have been using for a number of years and attach them to a toolbar or similar. In Office 2003 this is nice and easy, just set-up a new toolbar, drag and drop the macros onto it and tweak a little. Office 2007 has made this rather difficult in comparison. 

Much of the more recent Microsoft documentation seems to indicate that you need Visual Studio 2005/2008 Professional and be familiar with C#. Not an option for me and a shame that Visual Studio Express editions are not supported. 

There are various useful looking ribbon editing tools available but like Visual Studio these incur a license fee. 

The goal was to create one ribbon entry with have one button that runs a custom VBA macro, no Visual Studio 2005 or 2008, no Visual Studio Tools for Office (VSTO), no learning C# (just yet) and free.

Solution 

This excellent post helped "Customize the Ribbon (It doesn't take Rocket Science)" but it does cover a bit more ground than I initally wanted.

I also use my own Normal.dot (previous versions of office file name). 

1) Create a directory for your user templates.

2) Start Word 2007 and click the Office Logo > Word Options > Advanced > File Locations

Scroll down to find the file locations button as it is under the General section.

3) Set user templates location to the new directory, do not shut down Word yet.

4) Create a Normal.dot. As you have Word already open and hopefully an empty document called Document1 (or higher number), click Office Logo > Save as > Word Template. The save as type should be Word Template (*.dotx), change the location to your user directory and rename the file to Normal.dotx

I got two files at this point Normal.dotx and Normal.dotm 

5) Enable the developer tab. Office Logo > Word Options > Popular > check "Show Developer tab in the Ribbon".

6) Download and install Microsoft Office 2007 Custom UI Editor.

7) Download and install/run the Office 2007 Icons Gallery.

8) Run the Microsoft Office 2007 Custom UI Editor, open Normal.dotm, paste in the following XML and save the changes.

A lot just happened here but from my little understanding we have defined a new tab with a new group and a button. The button id line has some values worth mentioning.

imageMso assigns a built-in Office icon to the button but as we are using free tools here you need to know what values can be used. Open up Office2007IconsGallery.xlsm from step 7. In Excel 2007 click Developer > Gallery 1 (or any other) > hover over an icon you wish to use and the tooltip will display the name in Bold.

Just for information screenshot shows Windows XP using Microsoft Zune theme and Office 2007 colour scheme set to black.

onAction will run a macro (to be defined in the next step).

9) In Word 2007 click Developer > Visual Basic and create to modules. I called mine Macros and RibbonControl. 

In RibbonControl paste the following :

Option Explicit
Sub myCourier9(ByVal control As IRibbonControl)
    Macros.Courier9
End Sub

In Macros paste :

Sub Courier9()
'
' Courier9 Macro
'
    Selection.Font.Name = "Courier"
    Selection.Font.Size = 9
End Sub

As the onAction item on the button  is set to "myCourier9" it runs myCourier9 in RibbonControl. Currently I do not understand why it works this way but I was happy to accept that it does for now, just to see some results.

10)  You should now have a tab called My Macros with a Formatting group and a Courier 9 button. To check it runs, type in something into an open document and make sure it is not in font Courier 9. Select the text and click the new Courier 9 button.

Problem

Editing a chart in Excel I wanted to change the line thickness. This isn't a difficult process for one or two lines, but it is tedious for 23 lines.

Solution

It turns out a simple piece of VBA (Visual Basic for Applications) will allow you to change all lines on a Excel chart.

The following VBA code only affects your current selected chart. If you wish to change multiple charts using this code just run it multiple times, selecting a new chart after each run. Also as there is no error handling when you run this on a worksheet without a chart you will get an - Run-time error '91': Object variable or With block variable not set 

Sub ChartLineWeight()
Dim lines As Object

For Each lines In ActiveChart.SeriesCollection
    lines.Border.Weight = xlThick
Next
   
End Sub

Weight can have the following values :

xlHairline
xlThin
xlMedium
xlThick

Example Chart with thin lines :

Example chart with thick lines :