CODEWITHSUNDEEP
vbaexceluser-interfaceformatting
przemo_li
przemo_li

Reputation: 4053

VBA, Excel how to set particular styles without using their names?

VBA, Excel how to set particular styles without using their names?

Names are localized and hence useless for my app which will be used by different language Excel version.

One UGLY solution I can think off, is to keep list of those styles applied to some cells on hidden sheet, and then check their names and use them on the run time....

But there must be some easier way, right? MS could not botched so important aspect of Excel.

PS Here are some exemplary styles from registering macros:

Selection.Style = "Akcent 6"
Range("G4").Select
Selection.Style = "60% — akcent 6"
Range("G5").Select
Selection.Style = "Akcent 5"

Upvotes: 5

Views: 10946

Answers (2)

Dave Maff
Dave Maff

Reputation: 848

The code here can be used to find the localised version of a built-in style name (using its English version name). It isn't efficient but it probably doesn't need to be.

Public Sub Foo()
    localisedStyleName = FindLocalisedBuiltinStyleName("20% - Accent6")
End Sub

Public Function FindLocalisedBuiltinStyleName(EnglishStyleName) As String
    accentNumber = 0
    percentage = 0

    If Strings.Left(EnglishStyleName, 6) = "Accent" Then
        AccentNumber = CInt(Strings.Mid(EnglishStyleName, 7, 1))
    Else
       AccentNumber = CInt(Strings.Mid(EnglishStyleName, 13, 1))
       Percentage = CInt(Strings.Mid(EnglishStyleName, 1, 2))
    End If

    ThemeColorIndex = AccentNumber + 4
    FontToFind = 2

    Select Case Percentage
    Case 0
        FontToFind = 1
        TintAndShadeToFind = 0
    Case 20
        TintAndShadeToFind = 0.799981688894314
    Case 40
        TintAndShadeToFind = 0.599993896298105
    Case 60
        FontToFind = 1
        TintAndShadeToFind = 0.399975585192419
    End Select

    For i = 1 To ActiveWorkbook.Styles.Count
        ThemeColor = ActiveWorkbook.Styles.Item(i).Interior.ThemeColor
        TintAndShade = ActiveWorkbook.Styles.Item(i).Interior.TintAndShade
        Font = ActiveWorkbook.Styles.Item(i).Font.ThemeColor

        If ThemeColor = ThemeColorIndex And Abs(TintAndShade - TintAndShadeToFind) < 0.001 And Font = FontToFind Then
            FindLocalisedBuiltinStyleName = ActiveWorkbook.Styles.Item(i).NameLocal
            Exit Function
        End If
    Next

    FindLocalisedBuiltinStyleName = ""

End Function

Upvotes: 2

Gary&#39;s Student
Gary&#39;s Student

Reputation: 96773

For the purpose of establishing Styles in the workbooks you distribute, you can create your own Styles and assign names to them. For example przemo1, przemo2, etc.

For example:

Sub MakeAStyle()
    ActiveWorkbook.Styles.Add Name:="PrZemo1"
    With ActiveWorkbook.Styles("PrZemo1")
        .IncludeNumber = True
        .IncludeFont = True
        .IncludeAlignment = True
        .IncludeBorder = True
        .IncludePatterns = True
        .IncludeProtection = True
    End With
    With ActiveWorkbook.Styles("PrZemo1").Font
        .Name = "Arial Narrow"
        .Size = 11
        .Bold = False
        .Italic = False
        .Underline = xlUnderlineStyleNone
        .Strikethrough = False
        .Color = -16776961
        .TintAndShade = 0
        .ThemeFont = xlThemeFontNone
    End With
    With ActiveWorkbook.Styles("PrZemo1")
        .HorizontalAlignment = xlCenterAcrossSelection
        .VerticalAlignment = xlCenter
        .ReadingOrder = xlContext
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
    End With
End Sub

EDIT#1

Here are some COLORs and associated indexes:

colors

Upvotes: 4

Related Questions