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
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