VBA Option buttons to change sheet contents to create a 'dark mode' illusion

[drefiek2]

Hi, I would like to add an option button with a code for 'Light mode' and 'dark mode' into my sheet. If dark mode is chosen then everything on the spreadsheet changes colour to create the illusion of a dark mode. The spreadsheet is very bright particularly when used during night shifts so I would like users to have the option to change it to a darker set of colours with a black background etc.
Any other suggestions, please let me know.

 

[Micron]

Interesting question/idea. I imagine that if 2 option buttons were in a frame (so that only one could be selected at a time) you'd alter several sheet display options such as font, cell and border colours (perhaps as individual ranges) based on the chosen option. Simpler approach might be to just manually dim the display at night.

 

[Skyybot]

Create a button that runs the following Macro. It will add a "Dark Mode" to the Cell Styles in the Home tab. Select all cells and apply. Hope this helps. Some tweaking may be required if you used any other cell formatting.

Sub Dark_Mode_Cells()
'
' Dark_Mode_Cells Macro
' Dark Mode cell format.
'
' Keyboard Shortcut: Ctrl+Shift+D
'
    ActiveWorkbook.Styles.Add Name:="Dark_Mode"
    With ActiveWorkbook.Styles("Dark_Mode")
        .IncludeNumber = True
        .IncludeFont = True
        .IncludeAlignment = True
        .IncludeBorder = True
        .IncludePatterns = True
        .IncludeProtection = True
    End With
    With ActiveWorkbook.Styles("Dark_Mode").Font
        .Name = "Calibri"
        .Size = 11
        .Bold = False
        .Italic = False
        .Underline = xlUnderlineStyleNone
        .Strikethrough = False
        .ThemeColor = 1
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
    With ActiveWorkbook.Styles("Dark_Mode").Borders(xlLeft)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With ActiveWorkbook.Styles("Dark_Mode").Borders(xlRight)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With ActiveWorkbook.Styles("Dark_Mode").Borders(xlTop)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With ActiveWorkbook.Styles("Dark_Mode").Borders(xlBottom)
        .LineStyle = xlContinuous
        .ThemeColor = 1
        .TintAndShade = 0
        .Weight = xlThin
    End With
    ActiveWorkbook.Styles("Dark_Mode").Borders(xlDiagonalDown).LineStyle = xlNone
    ActiveWorkbook.Styles("Dark_Mode").Borders(xlDiagonalUp).LineStyle = xlNone
    With ActiveWorkbook.Styles("Dark_Mode").Interior
        .Pattern = xlSolid
        .PatternColorIndex = 0
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
End Sub

 

[Micron]

You can also do this with conditional formatting, thus no code. However, that would not alter the workbook theme. Code could should be able to take care of theme settings, as well as apply one format to used range and another to unused range.

 

[drefiek2]

I ended up creating two option buttons and linked these to a hidden true/false cell which in turn linked to some conditional formatting options, it works a treat Thanks for the tips and ideas though Gents.

 

[Micron]

Editing a cell is what I had in mind (could do so directly as well), but after reviewing, I don't think I made that very clear. Apologies, and thanks for the recognition.