VBA Option Button Text Colour Change

D

drefiek2

New Member
Joined
Apr 23, 2023
Messages
48
Office Version
  1. 365
  2. 2021
Platform
  1. Windows
Hi folks, I need some help with option buttons (form control not ActiveX).
I have two of them "Option Button 41" and "Option Button 42".
I also have this code on a click button which alternates "True" and "False" in a particular cell.

VBA Code: 
Sub DarkMode()
    Dim rng As Range
    Set rng = ThisWorkbook.Worksheets("Mechanics").Range("B15")
    rng.Value = IIf(rng.Value = False, True, False)
End Sub

Essentially what I need is, when the above click button is pressed and it changes the cell to "True", I need both Option Buttons to change text colour from their default black to WHITE. The button colour itself should remain unchanged.
 

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!
Sort by date Sort by votes
the following example changes both the font color and the background color
with a True/False toggle
I hope this helps

VBA Code: 
Public Sub DarkMode()
    With Range("B15")
      If .Value <> True And .Value <> False Then
        .Value = False
      Else
        .Value = Not .Value 'toggle between True/False
      End If
      'add 2 make for Choose function compatibilty (needs index of 1 or greater
      .Interior.Color = IIf(.Value, vbBlue, vbWhite) 'change fill color
      .Font.Color = IIf(.Value, vbWhite, vbBlack) 'change font color
    End With
End Sub
 
Upvote 0
Bosquedeguate said:
the following example changes both the font color and the background color
with a True/False toggle
I hope this helps

VBA Code: 
Public Sub DarkMode()
    With Range("B15")
      If .Value <> True And .Value <> False Then
        .Value = False
      Else
        .Value = Not .Value 'toggle between True/False
      End If
      'add 2 make for Choose function compatibilty (needs index of 1 or greater
      .Interior.Color = IIf(.Value, vbBlue, vbWhite) 'change fill color
      .Font.Color = IIf(.Value, vbWhite, vbBlack) 'change font color
    End With
End Sub
Click to expand...
Hi there, thanks for the prompt response.
This changes the cell colours rather than the actual Option Button text colour. I need the black option button text colour to change to white, and back to black on TRUE/FALSE toggle.
Screenshot 2023-08-04 124355.png
 
Upvote 0
.ForeColor to change the font color
.BackColor to change the background color

You will need to change the button to an ActiveX control (rather than a Form control).

buttonname.ForeColor = vbBlack or vbWhite
buttonname.BackColor = vbGray or vbBlack

Let's say you name your button (name, not the Caption) btnDarkMode

The above code would look something like

VBA Code: 
Public Sub btnDarkMode_Click()
    With Range("B15")
      If .Value <> True And .Value <> False Then
        .Value = False
      Else
        .Value = Not .Value 'toggle between True/False
      End If
      'add 2 make for Choose function compatibilty (needs index of 1 or greater
      btnDarkMode.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color
      btnDarkMode.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color
    End With
End Sub
 
Upvote 0
Solution
Bosquedeguate said:
.ForeColor to change the font color
.BackColor to change the background color

You will need to change the button to an ActiveX control (rather than a Form control).

buttonname.ForeColor = vbBlack or vbWhite
buttonname.BackColor = vbGray or vbBlack

Let's say you name your button (name, not the Caption) btnDarkMode

The above code would look something like

VBA Code: 
Public Sub btnDarkMode_Click()
    With Range("B15")
      If .Value <> True And .Value <> False Then
        .Value = False
      Else
        .Value = Not .Value 'toggle between True/False
      End If
      'add 2 make for Choose function compatibilty (needs index of 1 or greater
      btnDarkMode.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color
      btnDarkMode.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color
    End With
End Sub
Click to expand...
Hi,
I named them SummerButton and WinterButton and received the following error.

424 Object Required

VBA Code: 
Public Sub DarkModeTest()
    With ThisWorkbook.Worksheets("Mechanics").Range("B15")
      If .Value <> True And .Value <> False Then
        .Value = False
      Else
        .Value = Not .Value 'toggle between True/False
      End If
      'add 2 make for Choose function compatibilty (needs index of 1 or greater
      SummerButton.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color
      WinterButton.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color
      SummerButton.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color
      WinterButton.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color
    End With
End Sub
 
Upvote 0
drefiek2 said:
Hi,
I named them SummerButton and WinterButton and received the following error.

424 Object Required

VBA Code: 
Public Sub DarkModeTest()
    With ThisWorkbook.Worksheets("Mechanics").Range("B15")
      If .Value <> True And .Value <> False Then
        .Value = False
      Else
        .Value = Not .Value 'toggle between True/False
      End If
      'add 2 make for Choose function compatibilty (needs index of 1 or greater
      SummerButton.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color
      WinterButton.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color
      SummerButton.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color
      WinterButton.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color
    End With
End Sub
Click to expand...
Where do you have your button code located?
You will need some event code on the Sheet(s) where you have the buttons.

You are trying to reference your buttons outside of the code for your sheet. In other words VBA does not know about your two buttons

The other option is to move the DarkMode Sub into the code for the "Mechanics" sheet (with a few minor changes).

If your buttons both on the "Mechanics" sheet you will need the following VBA code for the sheet.

VBA Code: 
Private Sub SummerButton_Click()
  DarkMode
End Sub

Private Sub WinterButton_Click()
  DarkMode
End Sub

VBA Code: 
Public Sub DarkModeTest()
  Dim SaveValue
  
  With ThisWorkbook.Worksheets("Mechanics")
    With .Range("B15")
      SaveValue = .Value
      If .Value <> True And .Value <> False Then
        .Value = False
      Else
        .Value = Not .Value 'toggle between True/False
      End If
    End With

    .SummerButton.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color
    .WinterButton.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color

    .SummerButton.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color
    .WinterButton.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color

  End With

End Sub
 
Last edited:
Upvote 0
Bosquedeguate said:
Where do you have your button code located?
You will need some event code on the Sheet(s) where you have the buttons.

You are trying to reference your buttons outside of the code for your sheet. In other words VBA does not know about your two buttons

The other option is to move the DarkMode Sub into the code for the "Mechanics" sheet (with a few minor changes).

If your buttons both on the "Mechanics" sheet you will need the following VBA code for the sheet.

VBA Code: 
Private Sub SummerButton_Click()
  DarkMode
End Sub

Private Sub WinterButton_Click()
  DarkMode
End Sub

VBA Code: 
Public Sub DarkModeTest()
  Dim SaveValue
 
  With ThisWorkbook.Worksheets("Mechanics")
    With .Range("B15")
      SaveValue = .Value
      If .Value <> True And .Value <> False Then
        .Value = False
      Else
        .Value = Not .Value 'toggle between True/False
      End If
    End With

    .SummerButton.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color
    .WinterButton.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color

    .SummerButton.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color
    .WinterButton.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color

  End With

End Sub
Click to expand...
also, change .Value to SaveValue for your 4 color changes.
 
Upvote 0
Bosquedeguate said:
Where do you have your button code located?
You will need some event code on the Sheet(s) where you have the buttons.

You are trying to reference your buttons outside of the code for your sheet. In other words VBA does not know about your two buttons

The other option is to move the DarkMode Sub into the code for the "Mechanics" sheet (with a few minor changes).

If your buttons both on the "Mechanics" sheet you will need the following VBA code for the sheet.

VBA Code: 
Private Sub SummerButton_Click()
  DarkMode
End Sub

Private Sub WinterButton_Click()
  DarkMode
End Sub

VBA Code: 
Public Sub DarkModeTest()
  Dim SaveValue
 
  With ThisWorkbook.Worksheets("Mechanics")
    With .Range("B15")
      SaveValue = .Value
      If .Value <> True And .Value <> False Then
        .Value = False
      Else
        .Value = Not .Value 'toggle between True/False
      End If
    End With

    .SummerButton.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color
    .WinterButton.ForeColor = IIf(.Value, vbWhite, vbBlack) 'change font color

    .SummerButton.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color
    .WinterButton.BackColor = IIf(.Value, vbBlue, vbWhite) 'change fill color

  End With

End Sub
Click to expand...
Got it working, thank you. I didn't have it in a sheet, but a Module instead! :)
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Question
VBA Selenium How select an option from dropdown list witthout select?
Replies
1
Views
780
bionic
B
W
  • Question
multiple password protected pages with same password and using macros
Replies
0
Views
464
WindowGuy
W
O
  • Solved
ActiveX checkbox that changes color
2
Replies
15
Views
219
John_w
J
C
  • Solved
Changing cell fill color and text color using a ActiveX Button
Replies
9
Views
803
cr130
C
D
  • Solved
VBA Option buttons to change sheet contents to create a 'dark mode' illusion
Replies
5
Views
615
Micron
M

Forum statistics

Threads
1,215,073
Messages
6,122,975
Members
449,095
Latest member
Mr Hughes

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top