CODEWITHSUNDEEP
excelvbafonts
mvidude
mvidude

Reputation: 51

Excel - Is it possible to set or change the "theme fonts" with VBA?

In the ribbon menu under "page layout", there is a menu point which gives the user the ability to set a header and body font document-wide.

I would like to use VBA to set that option for multiple documents at once.

Regardless of the syntax, which I haven't figured out yet, the official docs say the properties are read-only (header font and body font). Is this a fruitless undertaking or is there some arguably hack-y way to achieve this, i.e. getting to the point where it is possible to write something akin to

Application.MajorThemeFont.Name ="Arial"

?

Or does one have to somehow load an already existing theme into a (already existing) workbook?

Upvotes: 1

Views: 786

Answers (2)

seb
seb

Reputation: 101

Great help this question! I think the correct answer to the question should be:

You can't define the theme fonts using VBA/macro but you can load an existing theme using VBA.

This has worked for me:

   Dim pathappdata As String
   pathappdata = Environ("APPDATA")
   'theme is located at %APPDATA%\Microsoft\Templates\Document Themes\Theme Fonts
    wb.Theme.ThemeFontScheme.Load ( _
        pathappdata & "\Microsoft\Templates\Document Themes\Theme Fonts\MY theme fonts.xml" _
        )

Upvotes: 0

bankeris
bankeris

Reputation: 201

In Ribbon "Developer" TAB press "Record Macro", and then set Font, Color or Theme and the Code will be written.

Upvotes: 2

Related Questions