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

 

Application.Dialogs(xlDialogApplyStyle).Show

 

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
    ActiveWorkbook.Styles(StyleName).Delete
    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 http://www.jkp-ads.com/Articles/styles00.asp. 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
    ActiveWorkbook.Styles(StyleName).Delete
    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

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 *


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>