I'm trying to update the code for my existing UserForms in my shared Microsoft Excel workbooks to correspond to the value that the user has currently set the Office Theme option to in the options of the Excel desktop app (accessible from within Excel by clicking "File", clicking "Options", clicking the "General" tab, navigating to the "Personalize your copy of Microsoft Office" section, and selecting one of the options in the dropdown box to the right of the "Office Theme:" label).
What I'm trying to achieve is relatively simple (at least from my perspective); depending on the value that the Office Theme option is currently set to, I want the colors of the background and text on my UserForms to change accordingly. Specifically, if the option is set to "Black", "Dark Gray", or "Use system setting" (and Windows has dark mode enabled), then I want the UserForms to have white text on a black background, whereas if the option is set to "White", "Colorful", or "Use system setting" (and Windows has dark mode disabled), then I want the UserForms to have black text on a white background.
I used the following code in an attempt to achieve this, which I placed in the module corresponding to UserForm_1:
Private Sub UserForm_Initialize()
Call DDMS.Determine_Dark_Mode_Status
If Dark_mode_status = True Then
BackColor = RGB(32, 32, 32)
UserForm_1_Text.ForeColor = RGB(255, 255, 255)
ElseIf Dark_mode_status = False Then
BackColor = RGB(255, 255, 255)
UserForm_1_Text.ForeColor = RGB(0, 0, 0)
End If
End Sub
When the UserForm is initialized, it transfers control to the Determine_Dark_Mode_Status sub in the DDMS module; this sub is supposed to determine whether or not dark mode is enabled in Windows settings, after which it sets the value of the Dark_mode_status variable to either "True" or "False" accordingly (the Dark_mode_status variable is a global boolean variable that's declared in a separate module that I use specifically for the purpose of declaring all of my global variables in one location), after which the colors of the UserForm's background and text are changed based on the value of the variable.
The above code, which performs the actual color changing, works as intended; when I ran it with the Office Theme option set to "Use system setting" and dark mode enabled in Windows settings, the colors changed as I expected them to. The part that I'm struggling with is the code in the Determine_Dark_Mode_Status sub that makes the determination on whether the colors should be changed or not.
The code that I have in the sub currently reads the AppsUseLightTheme registry value in the Registry Editor, which corresponds to the current dark mode status for apps. Although the code appeared to successfully read the registry value, the code ultimately didn't work as intended; once implemented, the UserForm colors were changed to my dark mode colors with dark mode enabled (which is how I know that the color-changing code works), but now the dark mode colors remain on the UserForm whether dark mode is enabled or not.
What I eventually realized is that the Windows dark mode status doesn't necessarily directly matter for my purposes anyway, because the value of the Office Theme option in the Excel app, unless set to "Use system setting", is applied independently of the Windows dark mode. To this end, I'm now trying to figure out if the value of the Office Theme option can just be directly read into VBA somehow, the idea being that if it can, I can simply do that in place of reading the dark mode registry value, which didn't fully work anyway.
I wrote the following test code in a new blank module just to see if the current value of the Office Theme option could be accessed:
Sub Test()
MsgBox ThisWorkbook.Theme
End Sub
However, when I run the sub, it gives me an error message that reads "Run-time error '438': Object doesn't support this property or method", which I do not understand, especially given that a.) Microsoft's own VBA documentation on this property appears to indicate that it's a functioning, non-deprecated property and that I invoked it correctly, and b.) the VBA Editor not only directly supplies this property to you when you type "ThisWorkbook." and then start typing "Theme" but is also listed as one of many predefined properties and methods in a scrollable tooltip that appears onscreen.
I'm not even positive that this property would be the solution to my problem anyway, but at the moment, I can't even determine whether it is or not because I'm getting this error message for some reason. Does anyone know why I would be getting this error message? Alternatively, does anyone know of either a.) another way to access the value of the Office Theme option via VBA, or b.) another way to achieve the end result that I'm trying to achieve without using this option or registry values?
Workbook.Theme refers to the Theme that is used in a workbook (see Page-Layout, Themes) and has nothing to do with dark or light mode.
I played around with the settings @Rory mentioned in the comments and it seems that is the way to read the Theme you are interested in.
For the case that the option "Use system setting" is selected, we need to read the system setting, that's done by reading the key @silas-paul uses. However, it's possible that this value is missing. I played some more on my computer: If the key is missing, windows runs in light mode. When changing the system settings to Dark mode, the key is created and set to 0. When now changing back to light mode, the key is set to 1. I came up with the following function:
Function ExcelIsUsingDarkTheme() As Boolean
Const OfficeThemeKey = "HKCU\Software\Microsoft\Office\16.0\Common\UI Theme"
Const WindowsThemeKey = "HKCU\Software\Microsoft\Windows\CurrentVersion\Themes\Personalize\AppsUseLightTheme"
Dim officeTheme As Long, windowsLightTheme As Long
With CreateObject("WScript.Shell")
On Error Resume Next
officeTheme = .RegRead(OfficeThemeKey)
windowsLightTheme = 1 ' If Key is missing, assume Light
windowsLightTheme = .RegRead(WindowsThemeKey)
On Error GoTo 0
Debug.Print officeTheme, windowsLightTheme
End With
' Office Theme setting:
' 3 Gray
' 4 Black
' 5 White
' 6 Use System setting
' 7 Color
Select Case officeTheme
Case 3, 4: ExcelIsUsingDarkTheme = True
Case 6: ExcelIsUsingDarkTheme = (windowsLightTheme = 0)
Case Else
ExcelIsUsingDarkTheme = False
End Select
End Function