416-621-9348 cgreaves@chrisgreaves.com
Home Services Products

Excel Styles (Home), Excel Styles are Additive , Hand-Crafted Styles, Implementing Protection , Style History

Style History

We have seen that style application is additive, that is, we can apply several styles in succession to a selection.

Excel 2000 retains the history of the last style applied.

Sub test()
    Dim cl As Range
    Set cl = ActiveCell
    MsgBox cl.Style.Name
End Sub

The little macro above displays the name of the style associated with a single cell.

If you delve into VBA and examine the variable “cl” in the Locals Window you will see that there is no Styles.Count or similar.

It follows that we have no way of determining any history other than the most-recently applied style

From this we deduce that we will have no way of undoing a series of applied styles, nor of distinguishing whetheer any specific element of formatting of a cell can be attributed to a style.

If we are applying a collection of styles to cells of a workshete, we must make sure that all but the UserInput style are set to Locked:

Sub test()
    Dim sty As Style
    For Each sty In ActiveWorkbook.Styles
        Debug.Print sty.Name & " " & sty.Locked
        If sty.Name = "UserInput" Then
            sty.Locked = False
        Else
            sty.Locked = True
        End If
    Next sty
End Sub

APuzzle True
Comma True
Comma [0] True
Constant True
Currency True
Currency [0] True
Currency0 True
Currency1 True
Currency2 True
Followed Hyperlink True
Heading1 True
Heading2 True
Heading3 True
Heading4 True
Hyperlink True
Normal True
Number0 True
Number1 True
Number2 True
Number3 True
Percent True
Percent0 True
Report True
Table True
UserInput False
WordWrap True

Loading

Toronto and Mississauga, Sunday, December 05, 2010 3:08 PM

Copyright © 1996-2010 Chris Greaves. All Rights Reserved.