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