PDA

View Full Version : [SOLVED] Using only names found in combo box



malik641
09-27-2005, 05:18 AM
What's the proper code to allow users to enter ONLY values in the list of a combo box???

Here's what I have so far:


Private Sub UserForm_Initialize()
On Error Resume Next
Dim Lst As Variant
'Sets list for Combo box for Current Employees
Lst = ThisWorkbook.Sheets("Employee Names").Range("Employees")
EmpCombo.List = Lst
End Sub

Private Sub OKButton_Click()
Dim WS As Worksheet
Dim EmpName As Variant
'Checks for Valid Employee Name
If EmpCombo.Value = "" Then
MsgBox "You have entered no employee name." & vbCrLf & _
"Please enter an employee name.", vbOKOnly + vbCritical, _
"Missing Employee Name"
EmpCombo.SetFocus
Exit Sub
End If
'For Each EmpName In Range("Hide_Sheets")
' If EmpCombo.Text = EmpName Then
' Exit For
' End If
' If EmpCombo.Text = EmpName Then
' MsgBox "You have not entered a valid employee name." & vbCrLf & _
' "Please enter a valid employee name.", vbOKOnly + vbCritical, _
' "Invalid Employee Name"
' EmpCombo.SetFocus
' Exit Sub
' End If
'Next EmpName
'If No option was selected
If OptionHide = False And OptionDelete = False Then
MsgBox "You have not selected an option." & vbCrLf & vbCrLf & _
"Please select an option to continue.", vbOKOnly + vbExclamation, _
"No Option Selected"
OptionHide.SetFocus
Exit Sub
End If
'Actions based on which option selected - Delete or Hide
If OptionDelete Then
For Each WS In ThisWorkbook.Worksheets
If WS.Name = EmpCombo.Value Then
With WS
.Select
Selection.Delete
End With
End If
If WS Is Nothing Then
MsgBox "The sheet " & EmpCombo.Text & " is non-existent." & vbCrLf & _
"Please select a valid sheet to delete.", vbOKOnly + vbCritical, _
"Sheet Non-existent"
End If
Next WS
End If
If OptionHide Then
For Each WS In ThisWorkbook.Worksheets
If WS = EmpCombo.Text Then
WS.Visible = xlSheetHidden
End If
If EmpCombo.Value Is Nothing Then
MsgBox "The sheet " & EmpCombo.Text & " is non-existent." & vbCrLf & _
"Please select a valid sheet to hide.", vbOKOnly + vbCritical, _
"Sheet Non-existent"
End If
Next WS
End If
End Sub

The For-Next loop that is commented out is what I was attempting to make work, but I wasn't getting any luck. And I'm still working on the actions for which option was selected, so don't mind those too much. Anybody have any suggestions??

Thanks :thumb

mvidas
09-27-2005, 05:33 AM
Since you have a named range for the allowed cells as it is, you could use Data Validation, and use a countif formula to check for it.

Say you have a named range called "Employees", and you want a user to only allow those names to be entered into column A. You could select column A, go to Data, then Validation, then under 'Allow' enter 'Custom', and for a formula you could use:


=COUNTIF(Employees,A1)>0

If an invalid entry was typed (case doesnt matter) then it tells the user it was an invalid entry.

By vba it would be:


With Columns("A").Validation
.Delete
.Add Type:=xlValidateCustom, AlertStyle:=xlValidAlertStop, Formula1:= _
"=COUNTIF(Employees,A1)"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With

I may have missed your point completely. If so, I apologize and blame it on the shortage of caffeine I've had this morning so far.

Matt

Norie
09-27-2005, 07:38 AM
Just set the MatchRequired property to True.

malik641
09-27-2005, 09:29 AM
I may have missed your point completely. If so, I apologize and blame it on the shortage of caffeine I've had this morning so far.
MattYes, you did. And I feel ya with the caffeine bit. But thanks for the code, I'm sure I could use that later.


Just set the MatchRequired property to True.Thanks, but I have a problem with this. If the user enters nothing and tries to press cancel, the MsgBox keeps coming up that says "Invalid Qualifier"...???:dunno If the user presses cancel on the userform no matter what the case, it should close. How can I set this to close on cancel even without the correct name from the text box? (I had to press the X button to close it out)

malik641
09-27-2005, 11:51 AM
Okay, I got it.

I just used this as my code instead of using the MatchRequired method.



For Each EmpName In Range("Employees")
If EmpCombo.Text = EmpName Then
i = i + 1
End If
Next EmpName
If i < 1 Then
MsgBox "You have not entered a valid employee name." & vbCrLf & _
"Please enter a valid employee name.", vbOKOnly + vbCritical, _
"Invalid Employee Name"
EmpCombo.Text = ""
EmpCombo.SetFocus
Exit Sub
End If


Thanks for all the help guys :)