Using IF/Then Statements with Option Buttons in UserForm VBA

Question

Is there a way to code the option buttons to give a value based off both being selected? I have tried multiple ways and seems I am getting nowhere. This is the last code I have tried and this is how I would like it to work if possible. The way this code is right now, I am only getting one value.

Private Sub OptBtn()

    Dim n As Long

    n = Sheets1.Range("A" & Application.Rows.Count).End(xlUp).Row
    Range("A" & Rows.Count).End(xlUp).Select
    ActiveCell.Offset(1, 0).Select
    ActiveCell.EntireRow.Insert


    If Me.OptBtn_AddCard.Value = True & Me.OptBtn_ManagedBy.Value = True Then
        Sheets1.Range("I" & n + 1).Value = "ACTIVE"

    Else

        If Me.OptBtn_AddCard.Value = True & Me.OptBtn_ManagedBy.Value = False Then
            Sheets1.Range("I" & n + 1).Value = "AVAILABLE"

        Else

            If Me.OptBtn_AddCard.Value = False & Me.OptBtn_ManagedBy.Value = True Then
                Sheets1.Range("I" & n + 1).Value = "ACTIVE"

            End If
        End If
    End If

End Sub

Me.OptBtn_AddCard.Value = True And Me.OptBtn_ManagedBy.Value = True, & operator is for concatenating strings. — Raymond Wu
– Raymond Wu, Commented Mar 24, 2022 at 5:53

TinMan · Accepted Answer · 2022-03-24 05:56:49Z

3

Use And instead of &.

If Me.OptBtn_AddCard.Value = True And Me.OptBtn_ManagedBy.Value = True Then
    Sheets1.Range("I" & n + 1).Value = "ACTIVE"
Else
    If Me.OptBtn_AddCard.Value = True And Me.OptBtn_ManagedBy.Value = False Then
        Sheets1.Range("I" & n + 1).Value = "AVAILABLE"
    Else
        If Me.OptBtn_AddCard.Value = False And Me.OptBtn_ManagedBy.Value = True Then
            Sheets1.Range("I" & n + 1).Value = "ACTIVE"
        End If
    End If
End If

The code can be shortened like this:

If Me.OptBtn_AddCard.Value And Me.OptBtn_ManagedBy.Value Then
    Sheets1.Range("I" & n + 1).Value = "ACTIVE"
Else
    If Me.OptBtn_AddCard.Value And Not Me.OptBtn_ManagedBy.Value Then
        Sheets1.Range("I" & n + 1).Value = "AVAILABLE"
    Else
        If Not Me.OptBtn_AddCard.Value And Me.OptBtn_ManagedBy.Value Then
            Sheets1.Range("I" & n + 1).Value = "ACTIVE"
        End If
    End If
End If

I personally like the Switch() variation:

Switch function: Evaluates a list of expressions and returns a Variant value or an expression associated with the first expression in the list that is True

Sheets1.Range("I" & n + 1).Value = Switch( _
    Me.OptBtn_AddCard.Value And Me.OptBtn_ManagedBy.Value, "ACTIVE", _
    Me.OptBtn_AddCard.Value And Not Me.OptBtn_ManagedBy.Value, "AVAILABLE", _
    Not Me.OptBtn_AddCard.Value And Me.OptBtn_ManagedBy.Value, "ACTIVE")

answered Mar 24, 2022 at 5:56

TinMan

7,8142 gold badges13 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Patrick Over a year ago

That solved it. Thanks so much! I figured I was doing something wrong. I tried the switch variation and now I'm hooked. Thanks again for showing me something new.

Collectives™ on Stack Overflow

Using IF/Then Statements with Option Buttons in UserForm VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related