VBA Option Button Text Colour Change

D

drefiek2

Board Regular
Joined
Apr 23, 2023
Messages
59
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
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

N
  • Question Question
ActiveX Option Button to sort Data in another sheet and update conbobox
2 3
Replies
22
Views
344
NUC_N_FUTS2
N
T
  • Question Question
thisworkbook.saved changes inexplicably
Replies
9
Views
168
tomluigi
T
E
  • Solved
Option buttons not clickable after running VBA export code
Replies
2
Views
398
Domenic
D
N
  • Question Question
VBA - Intersect code being re-initiated
Replies
1
Views
233
Peter_SSs
Peter_SSs
B
  • Question Question
Using VBA to retrieve specific pages from PDFs
Replies
1
Views
220
Big_Al_fae_the_rigs
B

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,021
Latest member
Justyna P

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