leopardhawk
Well-known Member
- Joined
- May 31, 2007
- Messages
- 611
- Office Version
- 2016
- Platform
- 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!
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