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 “myParticularStyle”


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 = xlAutomatic

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 CommandBar
Dim sMenuKeys As String
Dim sMenuCap As String
    Set CB = Application.CommandBars(“Worksheet Menu Bar”)
    sMenuCap = CB.FindControl(Id:=30006, recursive:=True).Caption
    sMenuKeys = “%” & Mid(sMenuCap, InStr(sMenuCap, “&”) + 1, 1)
    sMenuCap = CB.FindControl(Id:=254, recursive:=True).Caption
    sMenuKeys = sMenuKeys & Mid(sMenuCap, InStr(sMenuCap, “&”) + 1, 1)
    On Error Resume Next
    On Error GoTo 0

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

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