Category Archives: 12354

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