PDA

View Full Version : [SOLVED] Updating Menu



Sir Phoenix
09-22-2005, 09:16 PM
Okay. I have a userform that makes employee records. The employee data is contained in columns A, B, and C, and starts on Row 2 and extends downward. My form has various comboboxes. The employee name combo box dynamically extends/shrinks as people are added. I'm trying to make it so that when a name is selected, it autopops the other information. That part currently works. However, whenever you add/remove a person, and the information resets, (cmbEmpName.value = "") it errors.

I was debugging it, and after I removed a person, the name combobox still stored there name as a value, but the find statement was coming up with nothing, and it was erroring on that.


Private Sub cmbEmpName_Change()
Dim myRange, findRange As Range
If Not cmbEmpName.Value = "" Then
With ThisWorkbook.Sheets(5)
Set myRange = .Range("A2", .Cells(Rows.Count, "A").End(xlUp))
Set findRange = myRange.Find(Me.cmbEmpName.Value, MatchCase:=True)
If Not findRange Is Nothing Then
myRange.Select
cmbShiftType.Value = findRange.Offset(0, 1).Value
cmbSchedType.Value = findRange.Offset(0, 2).Value
End If
End With
End If
End Sub


Example attached in Zip.

Bob Phillips
09-23-2005, 01:00 AM
.

Sir Phoenix
09-23-2005, 03:08 PM
I found it. Instead of using the change method of the combobox, I used the AfterUpdate method, which more directly responds to a user-entry. Using the change method caused it to autopop as soon as they entered something in, which was bad if they were entering a new record.