Prevent Custom Theme from Affecting Spreadsheets?

[bencar]

Hi. i got a win 10 custom theme installed. But it affects the background color of my spreadsheets. Is there a vba that can black any themes from affecting them?

 

[Worf]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

Hi

' sheet module
Private Sub Worksheet_Activate()


' desired theme
ActiveWorkbook.ApplyTheme ("C:\Program Files\Microsoft Office 15\Root\Document Themes 15\Facet.thmx")


'("C:\Users\XXX\AppData\Roaming\Microsoft\Templates\Document Themes\Tema1.thmx") user custom theme


End Sub

 

[bencar]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

Hi

' sheet module
Private Sub Worksheet_Activate()


' desired theme
ActiveWorkbook.ApplyTheme ("C:\Program Files\Microsoft Office 15\Root\Document Themes 15\Facet.thmx")


'("C:\Users\XXX\AppData\Roaming\Microsoft\Templates\Document Themes\Tema1.thmx") user custom theme


End Sub

Hi Worf,

I meant to say 'block' instead of 'black' in my original question. Sorry for the typo. As such I'm looking to block excel from my win 10 custom theme. Will your code block my win 10 custom theme from affecting Excel?

Also how do i install your code? I'm a newbir when it comes to this on excel. Looking forward to your reply.

 

[Worf]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

Hi

What Excel version are you using? Details of a solution may be version specific.

To use the code, follow the steps below:

• Open the workbook.
• Press alt+F11 to go to the VBE.
• At the left pane, right click a worksheet and choose “show code”.
• Paste the code at the right pane.
• Press alt+F11 to return.

Now, activating that worksheet will apply the theme to the workbook. Check the code for a valid theme path (a .thmx file).
Another event that could be used is the workbook open event.

 

[bencar]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

Hi

What Excel version are you using? Details of a solution may be version specific.

To use the code, follow the steps below:

• Open the workbook.
• Press alt+F11 to go to the VBE.
• At the left pane, right click a worksheet and choose “show code”.
• Paste the code at the right pane.
• Press alt+F11 to return.

Now, activating that worksheet will apply the theme to the workbook. Check the code for a valid theme path (a .thmx file).
Another event that could be used is the workbook open event.

Hi worf,

I got Excel 2016 to answer your question.

So I right-clicked on Sheet1(Sheet1) under VBAProject (Book1) to 'view code.' Then I added the code to the worksheet, 'Book1(Sheet1).' Image is attached.

But I dont understand how to activate the code. You said, "check the code for a valid theme path (.thmx file)." I dont understand. Did you mean to save it to this path below? If so then I get a message saying: "the following features cannot be saved in a macro-free workbook: VB Project."
C:\Users\x\AppData\Roaming\Microsoft\Templates\LiveContent\16\Managed\Document Themes\1033

What am I doing wrong?

And this will apply to and and all spreadsheets, correct?

 

[Worf]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

o The page below shows how to set a default Excel theme. If it is applied, produces the desired result and stays there, you do not need the code.
o Try manually applying a theme at page layout>themes. Does this remove the unwanted background colour?
o A workbook with macros must have the XLSM extension.
o The code expects a file path. On my example, it uses the “Facet.thmx” theme located at C:\Program Files\Microsoft Office 15\Root\Document Themes 15. You can take advantage of THMX files provided by Microsoft or create your own, as explained on the page below. The VBA only refers to a pre-existent theme file.
o A theme applies to the entire workbook.

https://support.office.com/en-us/ar...or-Excel-c846f997-968e-4daa-b2d4-42bd2afef904

 

[bencar]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

o The page below shows how to set a default Excel theme. If it is applied, produces the desired result and stays there, you do not need the code.
o Try manually applying a theme at page layout>themes. Does this remove the unwanted background colour?
o A workbook with macros must have the XLSM extension.
o The code expects a file path. On my example, it uses the “Facet.thmx” theme located at C:\Program Files\Microsoft Office 15\Root\Document Themes 15. You can take advantage of THMX files provided by Microsoft or create your own, as explained on the page below. The VBA only refers to a pre-existent theme file.
o A theme applies to the entire workbook.

https://support.office.com/en-us/ar...or-Excel-c846f997-968e-4daa-b2d4-42bd2afef904

Hi worf,

I'm confused. I dont want to create a new excel theme if thats what youre suggesting. I just dont want my win 10 custom theme to affect excel at all.

Again, after i try to save this code, when i click 'save as type' in the drop down theres no .thmx on the list at all even when I go to the path you mentioned. And there are no files in the 'save as' dialogue either.

Again I need something to not affect ANY part of excel whatsoever.

 

[Worf]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

o I understood what you want.


o I need an answer to this question:

Try manually applying a theme at page layout>themes. Does this remove the unwanted background colour?

o You are going to save the workbook as XLSM, not THMX. The code will reference a THMX file on your hard drive, provided by Microsoft.

 

[bencar]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

o I understood what you want.


o I need an answer to this question:

Try manually applying a theme at page layout>themes. Does this remove the unwanted background colour?

o You are going to save the workbook as XLSM, not THMX. The code will reference a THMX file on your hard drive, provided by Microsoft.

Worf,

I saveed this theme as 'book1.xlsm in: C:\Program Files\Microsoft Office\root\Document Themes 16. I then closed excel and reopend it. But the background color from the custom theme is still there.

I also tried to manually applying one of the themes from page layout > themes as you suggested. It doesnt change the background color at all.

What do you suggest?

 

[Worf]

Re: How Do I Prevent Custom Theme from Affecting Spreadsheets?

o Is your custom theme built in Windows or provided by a third-party company?
o My code attempts to solve the issue by applying an Office theme. If manually changing themes does not affect the background color, then this method can be discarded.
o I suggest you use a theme that gives the desired cell color.