Need help with ComboBox validation

[leopardhawk]

Hello forum friends,

I have a comboBox where the user selects M or F for their gender. Currently, if they are going to click the OK command button to execute the form, they MUST make a choice in this comboBox based on my existing code.

I would like to modify the code so that it will allow them to use the OK command button even if the GenderComboBox is left blank. I'm sure there is a way to do this but I haven't been able to figure it out. I greatly appreciate any ideas or suggestions. Thanks!

I should also mention that it's important that the user can only choose M or F and not be able to type in any other characters.

My existing code:

Private Sub OKCommandButton_Click()

    If UCase(Me.GenderComboBox.Text) = "M" Or UCase(Me.GenderComboBox.Text) = "F" Then

    Else
        MsgBox "Please select M or F from the list."
    Exit Sub
    End If
   
    With Sheets(11)

        If Not AllmostEmpty(GenderComboBox) Then .Range("F9").Value = GenderComboBox.Value
       
    End With
   
Unload Me
   
End Sub

 

[EXCEL MAX]

Hello Leopardhawk,
why you don't do somethink like this...

Private Sub UserForm_Initialize()
    
    GenderComboBox.AddItem "M"
    GenderComboBox.AddItem "F"
    
End Sub

Private Sub GenderComboBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
    KeyCode = 0
    
End Sub

 

[leopardhawk]

@EXCEL MAX Hello, I tried your suggestion and it won't allow me to either type in an M or an F nor will it allow me to tab out of that ComboBox after I select from the drop-down.

 

[EXCEL MAX]

Why would you type anything in the combobox? Use DropDown.
The user choose between two or three options.
Try with this variant of code.

Private Sub UserForm_Initialize()
 
    GenderComboBox.AddItem "M"
    GenderComboBox.AddItem "F"
 
End Sub

Private Sub GenderComboBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Select Case KeyAscii
        Case 0 To 8
            KeyAscii = 0
        Case 10 To 69
            KeyAscii = 0
        Case 71 To 76
            KeyAscii = 0
        Case 78 To 101
            KeyAscii = 0
        Case 103 To 108
            KeyAscii = 0
        Case 110 To 255
            KeyAscii = 0
    End Select
 
End Sub

Private Sub OKCommandButton_Click()

    If UCase(Me.GenderComboBox.Text) = "M" Or UCase(Me.GenderComboBox.Text) = "F" Then
        Sheets(1).Range("F9").Value = GenderComboBox.Value
EX:     MsgBox "Your Code"
'       some code that will allways run
    Else
        MsgBox "Please select M or F from the list."
        GoTo EX:
    End If

End Sub

 

[Fluff]

How about

Private Sub OKCommandButton_Click()

   Select Case UCase(Me.GenderComboBox.Text)
      Case "", "M", "F"
         Sheets(11).Range("F9").Value = GenderComboBox.Value
      Case Else
         MsgBox "Please select M or F from the list."
         Exit Sub
   End Select
   Unload Me
   
End Sub

 

[EXCEL MAX]

Improved version...

Private Sub GenderComboBox_Change()
   
    If Len(GenderComboBox) > 1 Then
        GenderComboBox.BackColor = RGB(255, 0, 0)
    Else
        GenderComboBox.BackColor = RGB(255, 255, 255)
    End If
   
End Sub

Private Sub UserForm_Initialize()
   
    GenderComboBox.AddItem "M"
    GenderComboBox.AddItem "F"
   
End Sub

Private Sub GenderComboBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

    Select Case KeyAscii
        Case 0 To 8
            KeyAscii = 0
        Case 10 To 69
            KeyAscii = 0
        Case 71 To 76
            KeyAscii = 0
        Case 78 To 101
            KeyAscii = 0
        Case 103 To 108
            KeyAscii = 0
        Case 110 To 255
            KeyAscii = 0
       End Select
   
End Sub


Private Sub OKCommandButton_Click()

    If UCase(Me.GenderComboBox.Text) = "M" Or UCase(Me.GenderComboBox.Text) = "F" Then
        Sheets(1).Range("F9").Value = GenderComboBox.Value
EX:     MsgBox "Your Code"
'       some code that will allways run
'       Unload me
    Else
        MsgBox "Please select M or F from the list."
        GoTo EX:
    End If
   
End Sub

 

[Norie]

You can check if a value from the list has been entered/selected by checking the ListIndex property.

Private Sub OKCommandButton_Click()
    
    If Me.GenderComboBox.ListIndex <> -1 Then
         Sheets(11).Range("F9").Value = GenderComboBox.Value
    Else
         MsgBox "Please select M or F from the list."
         Exit Sub
   End If

   Unload Me

End Sub