Need help with data validation on VBA userform

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
Hello forum friends,

Hoping someone can help with my VBA script below. You can see two lines of the code are for GenderComboBox.List and SGenderComboBox.List. I would like to add some code that will validate these two combo boxes so that users can ONLY choose M or F from the drop-down list and they would be unable to enter any other characters or numbers in that field. I know there must be a way to do this but I can't seem to figure it out. If the user tries to enter something from their keyboard, I would like to have a message box pop-up that says "Please select from the list.". Appreciate any suggestions! Thanks!
VBA Code:
Private Sub UserForm_Initialize()
Dim objControl As MSForms.Control

    For Each objControl In Me.Controls
        If TypeName(objControl) = "TextBox" And objControl.Tag <> "" Then
           Me.setupPlaceholder objControl.Name, False
        End If
    Next objControl

    Me.GenderComboBox.List = Array("M", "F")
    
    Me.OptionComboBox.List = Array("100% Joint Life", _
        "60% Joint Life 5-year guarantee", _
        "60% Joint Life 10-year guarantee", _
        "60% Joint Life 15-year guarantee", _
        "Single Life no guarantee", _
        "Single Life 5-year guarantee", _
        "Single Life 10-year guarantee", _
        "Single Life 15-year guarantee", _
        "Other")

    Dim LastRow As Long
    Dim SheetName As String
    SheetName = "Sheet20"
    LastRow = Sheets(SheetName).Cells(Rows.Count, "A").End(xlUp).Row
    Me.ProviderComboBox.List = Sheets("Sheet20").Range("A2:A" & LastRow).Value
    Me.SProviderComboBox.List = Sheets("Sheet20").Range("A2:A" & LastRow).Value
    
    Me.SGenderComboBox.List = Array("M", "F")
    
    Me.SOptionComboBox.List = Array("100% Joint Life", _
        "60% Joint Life 5-year guarantee", _
        "60% Joint Life 10-year guarantee", _
        "60% Joint Life 15-year guarantee", _
        "Single Life no guarantee", _
        "Single Life 5-year guarantee", _
        "Single Life 10-year guarantee", _
        "Single Life 15-year guarantee", _
        "Other")

End Sub
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@rpaulson I did as you suggested and it still allows me to enter other characters in the combobox. I ran the script and typed the letter "D" and was able to submit the form.
 

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,183
how about this on you submit button.

VBA Code:
Private Sub CommandButton1_Click()
If UCase(Me.GenderComboBox.Text) = "M" Or UCase(Me.GenderComboBox.Text) = "F" Then

Else
  MsgBox "you need to enter M or F for gender"
Exit Sub
End If
End Sub
 
Solution

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@rpaulson it works great! Thank you so much for your help!
 

leopardhawk

Well-known Member
Joined
May 31, 2007
Messages
587
Office Version
  1. 2016
Platform
  1. Windows
@rpaulson sorry, I have a couple of questions about your solution (which works great!).

1. How does the UCase work to eliminate any input except the M or F?

2. If I want to do something similar with a date field where the user inputs a 'long-date', i.e. September 14, 1973, is there an easy way to ensure that they absolutely use the long-date format? In other words, force them to use a comma, ensure the month is spelled correctly and that the day and year are valid?

Just curious, always learning. Thanks!
 

rpaulson

Well-known Member
Joined
Oct 4, 2007
Messages
1,183
1. all the Ucase does is allow the user the enter a lower case m or f


2.
VBA Code:
If IsDate(Me.TextBox1.Text) = False Then
  MsgBox "Date is not valid"
Exit Sub
End If
 

Forum statistics

Threads
1,136,322
Messages
5,675,075
Members
419,549
Latest member
EliteBeat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top