Styles AIn’t What They Used To Be


Everybody knows
that if you are going to create a meaningful document in Word, you use Styles.
Excel has styles as well, but how many of us use these built-in styles, or add
to them with our own custom styles?

I have been building a styles utility recently, so I have
delved deeply into this functionality. Clearly, one of the principal aims of
such a utility would be the ability to edit styles. Being a rational coder, I
don’t want to re-invent the wheel, so I decided to use the built-in styles editor.
This is easily called using dialogs




From here, the style can be tailored to your needs, a new
style can be added, or a style can be deleted. This dialog defaults to the Normal style, but you can pre-load the style by passing
it as an argument


Application .(xlDialogApplyStyle).Show


So far so good. This all looks ideal.

Unfortunately, things are never that simple. The following
is a code snippet that should create a new style called NFx1Dec and open the styles edit dialog for that style.


Const StyleName As String = “NFx1Dec”

    On Error Resume Next
    On Error GoTo 0

    Activecell.Style = “Normal”
    ActiveWorkbook.Styles.Add StyleName, Activecell
    Application.Dialogs(xlDialogApplyStyle).Show ActiveWorkbook.Styles(StyleName)

If you call this dialog from VBA as shown above, select your
style to work with, and then click the Modify button, you are presented with a
series of tabs to adjust the particular style properties. From here, select the
Patterns tab and you see a colour palette, a pattern dropdown, and a sample preview box.

So, you want to change the fill colour. Simple, just select
a colour from the colour palette. Of course, it is not simple, otherwise I wouldn’t
be writing this. Select the colour, and nothing happens. You do not see that colour
in the Sample box (as you would if you
were modifying this style in Excel), and when you exit, that style does not have
that fill colour.

You can force it by selecting the pattern option to say solid,
but you don’t have to do this in Excel, so you shouldn’t have to just because it
is being invoked from VBA. You can also force it by selecting your colour, then
select No Colour, then select you desired
colour again (what!).

I tried various things to try and force the pattern to be
solid before invoking the tab, such as


ActiveWorkbook.Styles(StyleName).Interior.Pattern = xlSolid
ActiveWorkbook.Styles(StyleName).Interior.PatternColorIndex =

but no joy. So, a  real
failing on using what is a useful function of Excel from within VBA.

I was discussing this with Jan Karel Pieterse, a Dutch Excel
MVP. Jan Karel also has an interest in styles; he has a page about using styles on
his website
Jan Karel came up with the following workaround


Const StyleName As String = “NFx1Dec”
Dim CB As
Dim sMenuKeys As

Dim sMenuCap As String
CB = Application.CommandBars(“Worksheet Menu Bar”)
    sMenuCap = CB.FindControl(Id:=30006,
    sMenuKeys = “%” &
Mid(sMenuCap, InStr(sMenuCap, “&”) + 1, 1)
    sMenuCap = CB.FindControl(Id:=254,
    sMenuKeys = sMenuKeys &
Mid(sMenuCap, InStr(sMenuCap, “&”) + 1, 1)
    On Error
Resume Next

    On Error
GoTo 0

    Activecell.Style = “Normal”
    ActiveWorkbook.Styles.Add StyleName,
    SendKeys sMenuKeys &

Although this uses the dreaded SendKeys, it does seem to work. As a stand-alone procedure, it does
work, but when embedded in an application, it had some serious problems.

… to be continued

9 thoughts on “Styles AIn’t What They Used To Be

  1. Excel styles are so weak compared to Word styles, why bother? Word styles are hierarchical, Excel’s ain’t. You could define a base style named SectionDefault in Word then derive several other styles from it. Make a change to the base style, and it’s reflected in the derived styles. That most definitely doesn’t happen in Excel. Interestingly, that IS the way styles work in OpenOffice Calc.

  2. I get the same non update stuff with xlm, sometimes it works better but not in this case.
    I agree with Harlan tho’, and would add Excel styles are crapply implemented crap.
    Start copying sheets between workbooks, or ranges between Excel instances, and you quickly end up in style hell.
    I built a style add-in for a client, about 80% through we decided they were unusable in that environment.

  3. I used styles all the time for about a year, some time back I found them very useful, not perfect, but save a load of time, and the drop down was a real win!
    Ironically I have never liked them in Word, real men use LaTex anyway right?

  4. I agree Ross, we won’t listen to these luddites.

    I did a tech review on a book once and that was all in LaTex. I had a real game getting hold of a decent editor, with some special addons, I gave it up in the end.

  5. Ross – real men use edlin and hard code their binary printer sequences. Boys embed troff dot formatting commands in plain text files editied with vi.

  6. Bob, I feel sorry for them some time! 😉

    Harlan, just the word edlin, makes me cry! I watch a guy at work grep out words in unix, and I think man this is 2009, what an I doing!

Leave a Reply

Your email address will not be published. Required fields are marked *