CODEWITHSUNDEEP
vbaexceluserform
Vedant Dhawan
Vedant Dhawan

Reputation: 29

Nested IF statements for a User Form

My User-Form gives the user an option of 3 colors[red, green, blue] for "High Values" and "Low Values". However, the user must choose one color for each and not the same color for both High and Low Values of course. The colors highlight low and high values within the data in order to differentiate them. I have attached the picture of my User Form and the part of my code where I'm unable to assign different options different colors in order for it work. Any help on how to correct my IF Logic would be greatly appreciated. 

       Public Function ShowInputsDialog(LowColor As Long, HighValue As Single, HighColor As Long, LowValue As Single)
Call Initialize
Me.Show
If Not Cancel Then
    If optRed1.Value Then '<-- Assigning the 3 colors to the Low Values
    LowColor = vbRed
    ElseIf optGreen1.Value Then
    LowColor = vbGreen
    Else
    LowColor = vbBlue
    End If
    If optRed2.Value Then HighColor = vbRed  '<-- Assigning the 3 colors to the High Values
    ElseIf optGreen2.Value Then HighColor = vbGreen
    Else
    HighColor = vbBlue
    End If
 End If
    HighValue = txtHigher.Value
    LowValue = txtLower.Value
ShowInputsDialog = Not Cancel
Unload Me
End Function

Userform

Upvotes: 0

Views: 580

Answers (1)

user3598756
user3598756

Reputation: 29421

I'd go as follows:

  • add a BeforeUpdate() event for every radio button

  • have that event handler let the control assume the user input value if compatible with its "counterpart" control one

    this, by means of a sub that check the active control value against its "counterpart" one

for instance you could add in your userform code pane the following code:

Private Sub optBlue1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    CrossCheck
End Sub

Private Sub optBlue2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    CrossCheck
End Sub

Private Sub OptGreen1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    CrossCheck
End Sub

Private Sub OptGreen2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    CrossCheck
End Sub

Private Sub OptRed1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    CrossCheck
End Sub

Private Sub OptRed2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    CrossCheck
End Sub

Private Sub CrossCheck()
    Dim optNr As String, optName As String

    With Me
        If .ActiveControl.ActiveControl.Value Then
            optName = .ActiveControl.ActiveControl.name
            optNr = Mid(optName, Len(optName), 1)
            .ActiveControl.ActiveControl.Value = Not (.ActiveControl.ActiveControl.Value = .Controls(Replace(optName, optNr, IIf(optNr = "1", "2", "1"))).Value)
        End If
    End With
End Sub

of course a Class approach could relieve the burden of writing all those Private Sub optXXXX_BeforeUpdate() event handlers and give you more flexibility for both current coding and future code enhancements, but if you are sticking to only have three radio buttons it could be a little overkill

BTW all what above means that your ShowInputsDialog() sub must not care about option buttons compatibility and can do its plain value assigning work, for which I'd use a Select Case syntax instead of the If Then - Else If Then - End If one:

If Not Cancel Then
    Select Case True
        Case OptRed1.Value
            LowColor = vbRed
        Case OptGreen1.Value
            LowColor = vbGreen
        Case Else
            LowColor = vbBlue
    End Select

    Select Case True
        Case OptRed2.Value
            HighColor = vbRed
        Case OptGreen2.Value
            HighColor = vbGreen
        Case Else
            HighColor = vbBlue
    End Select
End If

or you could use a helper function: 

Function GetColor(opt1 As MSForms.OptionButton, opt2 As MSForms.OptionButton) As Long
    Select Case True
        Case opt1.Value
            GetColor = vbRed
        Case opt2.Value
            GetColor = vbGreen
        Case Else
            GetColor = vbBlue
    End Select
End Function

and simply write

If Not Cancel Then        
    LowColor = GetColor(OptRed1, OptGreen1)
    HighColor = GetColor(OptRed2, OptGreen2)        
End If

Upvotes: 2

Related Questions