CODEWITHSUNDEEP
excelvbaformsautofill
Elvis
Elvis

Reputation: 425

How to autofill the option button in excel user form based on a cell value?

enter image description here

Hi,

I want to let the UserForm to be able to detect the text in cell B4 and choose the correct option button without any extra click in the userform. May I know how should I achieve that? Thank you.

Upvotes: 0

Views: 756

Answers (2)

Simon
Simon

Reputation: 1375

Add this into your userform module:

Private Sub UserForm_Initialize()

If Sheets("Sheet1").Range("B4") = "Profit" Then Me.ProfitOption.Value = True

End Sub

Couple things to change:

-Change the Sheet1 to whatever yours is.

-Change the ProfitOption to whatever the name of your button is.

Upvotes: 2

Pᴇʜ
Pᴇʜ

Reputation: 57683

I recommend something like this

Private Sub UserForm_Initialize()
    Select Case Sheet1.Range("B4").Value 'evaluate the value of the cell
        Case "Profit"
            Me.OptionButton1.Value = True
            
        Case "Loss"
            Me.OptionButton2.Value = True
            
        Case Else 'if it is none of the above then go into undefined state
            Me.OptionButton1.Value = Null
            Me.OptionButton2.Value = Null
    End Select
End Sub

Note that this does not change the cell value if you change the option in the userform. Therefore you would need write the changed state back using either the Private Sub OptionButton1_Change() event or a "save" button.

Upvotes: 1

Related Questions