Change Option Button forecolor

[FryGirl]

I have multiple option buttons on a user form. Each of those option buttons will be selected, one after the other, but one at a time. In other words, I'll select the first option button and then the user form will close. The next time I open the user form, I'd like to see a forecolor on the option button text indicating that option has been used already. Then, when all is done, I'd like to select the last option button which resets all of the option buttons to not have the forecolor.

Using this below, the color does not persist when I open the userform the next time.

If OptionButton2.Value = True Then
   OptionButton2.ForeColor = &H80FF&
End If

 

[DanteAmor]

You need to create in your workbook a sheet to store the number of option button selected, the sheet can be called for example: "unused".
Put all of the following code in your userform.
In all optionbuttons there is a similar code. But in the last optionbuton goes the code to reset the color.

Dim sh As Worksheet

Private Sub OptionButton1_Click()
  If OptionButton1 Then sh.Range("A2").Value = 1
End Sub
Private Sub OptionButton2_Click()
  If OptionButton2 Then sh.Range("A2").Value = 2
End Sub
Private Sub OptionButton3_Click()
  If OptionButton3 Then sh.Range("A2").Value = 3
End Sub
Private Sub OptionButton4_Click()
  If OptionButton4 Then sh.Range("A2").Value = 4
End Sub
Private Sub OptionButton5_Click()
  'in the last option button
  If OptionButton5 Then
    Call setforecolor(&H80000012)
    sh.Range("A2").Value = 0
  End If
End Sub

Private Sub UserForm_Initialize()
  Set sh = Sheets("unused")
  Call setforecolor(&H80FF&)
End Sub

Sub setforecolor(xcolor)
  Dim optionstatus As Long
  Dim i As Long
 
  optionstatus = sh.Range("A2")
  For i = 1 To optionstatus
    Controls("OptionButton" & i).ForeColor = xcolor
  Next
End Sub

 

[FryGirl]

Hi Dante and thanks. I almost have it all set up, but now the Userform will not open. Error, "Could not find the specified object".

In a normal module, I have

Sub OpenMe()
   UserForm1.Show
End sub

If I remove the code

Private Sub UserForm_Initialize()
  Set sh = Sheets("unused")
  Call setforecolor(&H80FF&)
End Sub

from the User form code, the line above works fine. All of the spellings are correct, just can't understand what is stopping this?

 

[FryGirl]

My mistake Dante. I have it fixed now. I hadn't changed all of my option button names.

 

[DanteAmor]

I'm glad to hear that and I'm happy to help you.
Thanks for the feedback.