vba Userform 'onchange' not executing

[ccordner]

I have two userforms, one of which has a combobox listing names. Once a name is entered, it executes some code based on the value of the combobox. This is on the 'onchange' event.

If a name is entered which doesn't already appear in the list, a new userform is opened. But on this userform, none of the 'onchange' or 'afterupdate' events work.

They all work fine when the form is opened direct, just not when it is opened from a different form.

Any ideas why?

Chris.

 

[RoryA]

Typically with a userform you enter code into the event procedures of the controls you want to monitor. I get the impression this is not what you are doing, so can you elaborate as to the exact code you are using and how it is assigned to the controls?

 

[ccordner]

Certainly.

The main userform (frmReportSickness) is to report an instance of sickness. I populate the combobox with the code below. This is so that the user can choose an employee by typing their surname, but so that their (unique) pay number is shown to avoid any confusion.

cboEmployee.Clear
With Sheets("Staff")
Set Employees = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
End With
  ReDim ray(1 To Employees.Count)
    
    For Each Employee In Employees
        
        Surname = Right(Employee, Len(Employee) - InStrRev(Employee, " "))
        Titles = Left(Employee, Len(Employee) - Len(Surname))
        Count = Count + 1
        ray(Count) = (Surname & ", " & Titles & " (" & Employee.Offset(, -1) & ")")
    
    Next Employee
    
    For XCount = 1 To UBound(ray) - 1
        
        For YCount = XCount To UBound(ray)
            
            If ray(YCount) < ray(XCount) Then
                Temp = ray(XCount)
                ray(XCount) = ray(YCount)
                ray(YCount) = Temp
            End If
        
        Next YCount
    
    Next XCount
With cboEmployee
    .List = Application.Transpose(ray)
    .ListIndex = 0
    .SelStart = 0
    .SelLength = Len(cboEmployee.Value)
End With

When they type a name into the combobox, I want it to then search the sheet ("Staff") and pull up details about their location. So I had the following code on the 'onchange' event.

PAYENumber = Left(Right(cboEmployee.Value, 6), 5)
With Sheets("Staff")
Set Employees = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
Set SelectedEmployee = Employees.Find(PAYENumber, LookIn:=xlValues)
End With
LocationNumber = Sheets("Staff").Cells(SelectedEmployee.Row, 3)
Location = Sheets("Staff").Cells(SelectedEmployee.Row, 4)
Me.lblLocation.Caption = Location & " (" & LocationNumber & ")"
cboLocation = Location
lblEmployee.Caption = cboEmployee.Value
lblLocation2.Caption = Location & " (" & LocationNumber & ")"
cboResumptionLocation = Location

This worked perfectly, until somebody entered a name that wasn't in the list. So, rather than exiting the routine and making them go to the add/edit staff form and then come back into it, I wanted to take them direct to the form, where they could input the necessary info and carry on.

So I added this code bit right at the top:

If Not cboEmployee.Value Like "* (#####)" Then
    frmEmployeeNotFound.Show
    
    If frmEmployeeNotFound.Tag = "Cancelled" Then
    
        Unload frmEmployeeNotFound
        With cboEmployee
            .ListIndex = 0
            .SelStart = 0
            .SelLength = Len(cboEmployee.Value)
        End With
        Exit Sub
    
    End If
    
End If

When the sub-form (frmEmployeeNotFound) is called, they have to enter the location at which the employee is based and the location number. To make it easy (and improve integrity), I wanted them to be able to type the location into a combobox, which would (if the location was already in the list) then look up the correct location number and fill in the textbox for them.

So on the 'onchange' event of the combobox in frmEmployeeNotFound, I put the following code:

For Count = 2 To LastRow
    If Sheets("Staff").Cells(Count, 4) = cboStaffLocation Then
        txtLocationNo = Sheets("Staff").Cells(Count, 3)
        Exit Sub
        Else
        If Count = LastRow Then txtLocationNo = ""
    End If
Next

But, for some reason with the one form open, none of the 'onchange' or 'afterupdate' routines are being executed (some are there purely to validate the entries, such as times etc.).

Is this insurmountable? The only way round it I can think of is to close the form frmReportSickness, open the frmEmployeeNotFound, then close that and re-open frmReportSickness.

Hope this makes sense.

Chris

 

[RoryA]

I cannot think of any reason for that - can you post the full code including the event declaration lines?

 

[ccordner]

Certainly. Three chunks (I do apologise, some of it could probably be a little neater and there is rather a lot!):

1) The procedure that calls the first userform (No declarations outside the procedure):

Sub ReportSickness(control As IRibbonControl)
Dim Sickness As Long
Load frmReportSickness
Sickness = 4
Found = False
Do Until Found = True
    If Sheets("Sickness").Cells(Sickness, 1) = "" Then Found = True Else Sickness = Sickness + 1
    frmReportSickness.txtSicknessID = Sickness - 3
Loop
frmReportSickness.Show
End Sub

2) The code behind frmReportSickness:

Dim WSheet As Worksheet
Dim XCount, YCount, SheetIndex As Long
Dim CellCount, Count, ComboCount, RowNumber, Column As Integer
Dim Employees, Employee, SelectedEmployee As Range
Dim SheetName, EmployeeName, DisplayName, Surname, Titles, PAYENumber, Temp, TemporaryWeek, LocationNumber, Location As String
Dim Found, Valid, ResumptionValid, ExistingSickness As Boolean
Dim WeekEnding As Date
Dim a, b
 
Private Sub UserForm_Initialize()
cboEmployee.Clear
With Sheets("Staff")
Set Employees = .Range(.Range("B2"), .Range("B" & Rows.Count).End(xlUp))
End With
  ReDim ray(1 To Employees.Count)
    
    For Each Employee In Employees
        
        Surname = Right(Employee, Len(Employee) - InStrRev(Employee, " "))
        Titles = Left(Employee, Len(Employee) - Len(Surname))
        Count = Count + 1
        ray(Count) = (Surname & ", " & Titles & " (" & Employee.Offset(, -1) & ")")
    
    Next Employee
    
    For XCount = 1 To UBound(ray) - 1
        
        For YCount = XCount To UBound(ray)
            
            If ray(YCount) < ray(XCount) Then
                Temp = ray(XCount)
                ray(XCount) = ray(YCount)
                ray(YCount) = Temp
            End If
        
        Next YCount
    
    Next XCount
With cboEmployee
    .List = Application.Transpose(ray)
    .ListIndex = 0
    .SelStart = 0
    .SelLength = Len(cboEmployee.Value)
End With
If Sheets("Sickness").Range("F2") > "" Then
    With Sheets("Sickness").Range("F2:F1048576")
        a = .Value
    End With
    With CreateObject("scripting.dictionary")
        .comparemode = 1
        For Each b In a
            If Not .exists(b) Then .Add b, Nothing
        Next
        If .Count Then
            cboReportingSupervisor.List = Application.Transpose(.keys)
            cboReportingSupervisor2.List = Application.Transpose(.keys)
        End If
    End With
End If
If Environ("Username") Like "*.*" Then
    cboReportingSupervisor.Value = StrConv(Left(Environ("Username"), InStr(Environ("Username"), ".") - 1), vbProperCase) & " " & StrConv(Right(Environ("Username"), (Len(Environ("Username")) - InStr(Environ("Username"), "."))), vbProperCase)
    cboReportingSupervisor2.Value = StrConv(Left(Environ("Username"), InStr(Environ("Username"), ".") - 1), vbProperCase) & " " & StrConv(Right(Environ("Username"), (Len(Environ("Username")) - InStr(Environ("Username"), "."))), vbProperCase)
End If
With cboReportingSupervisor
    .SelStart = 0
    .SelLength = Len(cboEmployee.Value)
End With
txtTime = Format(Now, "hh:mm")
txtShiftDate = Format(DateValue(Now) + 1, "d mmm yy")
txtTime2 = Format(Now, "hh:mm")
cboEmployee.SetFocus
End Sub
Private Sub cboEmployee_Change()
If Not cboEmployee.Value Like "* (#####)" Then
    frmEmployeeNotFound.Show
    
    If frmEmployeeNotFound.Tag = "Cancelled" Then
    
        Unload frmEmployeeNotFound
        With cboEmployee
            .ListIndex = 0
            .SelStart = 0
            .SelLength = Len(cboEmployee.Value)
        End With
        Exit Sub
    
    End If
    
End If
PAYENumber = Left(Right(cboEmployee.Value, 6), 5)
With Sheets("Staff")
Set Employees = .Range(.Range("A2"), .Range("A" & Rows.Count).End(xlUp))
Set SelectedEmployee = Employees.Find(PAYENumber, LookIn:=xlValues)
End With
LocationNumber = Sheets("Staff").Cells(SelectedEmployee.Row, 3)
Location = Sheets("Staff").Cells(SelectedEmployee.Row, 4)
Me.lblLocation.Caption = Location & " (" & LocationNumber & ")"
cboLocation = Location
lblEmployee.Caption = cboEmployee.Value
lblLocation2.Caption = Location & " (" & LocationNumber & ")"
cboResumptionLocation = Location
End Sub
Private Sub txtShiftTime_AfterUpdate()
If IsDate(txtShiftTime.Value) And txtShiftTime.Value Like "*:*" Then
    txtShiftTime.Value = Format(txtShiftTime.Value, "hh:mm")
    Else
    MsgBox ("Please enter a time in the format 'hh:mm'")
    txtShiftTime = Format(TimeValue(Now), "hh:mm")
End If
End Sub
Private Sub txtshiftDate_AfterUpdate()
If IsDate(txtShiftDate.Value) And Not txtShiftDate.Value Like "*:*" Then
        txtShiftDate.Value = Format(txtShiftDate.Value, "d mmm yy")
    Else
    MsgBox ("Please enter a date in the format 'dd/mm/yy'")
    txttxtshiftDate = Format(DateValue(Now) - 1, "d mmm yy")
End If
End Sub
Private Sub txtTime_AfterUpdate()
If IsDate(txtTime.Value) And txtTime.Value Like "*:*" Then
    txtTime.Value = Format(txtTime.Value, "hh:mm")
    Else
    MsgBox ("Please enter a time in the format 'hh:mm'")
    txtTime = Format(TimeValue(Now), "hh:mm")
End If
End Sub
Private Sub chkUFN_Click()
If chkUFN.Value = True Then
    lblExpectedDuration.visible = False
    txtExpectedDuration.Enabled = False
    txtExpectedDuration.visible = False
    MultiPage1.Pages("pgeresumesickness").Enabled = True
    Else
    lblExpectedDuration.visible = True
    txtExpectedDuration.Enabled = True
    txtExpectedDuration.visible = True
    MultiPage1.Pages("pgeresumesickness").Enabled = False
End If
End Sub
Private Sub txtResumptionDate_AfterUpdate()
If IsDate(txtResumptionDate.Value) And Not txtResumptionDate.Value Like "*:*" Then
    txtResumptionDate.Value = Format(txtResumptionDate.Value, "d mmm yy")
    Else
    MsgBox ("Please enter a date in the format 'dd/mm/yy'")
    txtResumptionDate = Format(DateValue(Now) + 1, "d mmm yy")
End If
End Sub
Private Sub txtResumptionShift_AfterUpdate()
If IsDate(txtResumptionShift.Value) And txtResumptionShift.Value Like "*:*" Then
    txtResumptionShift.Value = Format(txtResumptionShift.Value, "hh:mm")
    Else
    MsgBox ("Please enter a time in the format 'hh:mm'")
    txtResumptionShift = Format(TimeValue(Now), "hh:mm")
End If
End Sub
Private Sub txtTime2_AfterUpdate()
If IsDate(txtTime2.Value) And txtTime2.Value Like "*:*" Then
    txtTime2.Value = Format(txtTime2.Value, "hh:mm")
    Else
    MsgBox ("Please enter a time in the format 'hh:mm'")
    txtTime2 = Format(TimeValue(Now), "hh:mm")
End If
End Sub
Sub Validation()
Valid = False
If cboEmployee.Value = "" Then
    MsgBox ("Please select an employee.")
    cboEmployee.SetFocus
    Exit Sub
End If
If txtShiftTime = "" Then
    MsgBox ("Please enter the start time of their next rostered shift in the format 'hh:mm'.")
    txtShiftTime.SetFocus
    Exit Sub
End If
If txtShiftDate = "" Then
    MsgBox ("Please enter the date of their next rostered shift in the format 'dd/mm/yy'.")
    txtShiftDate.SetFocus
    Exit Sub
End If
If cboLocation.Value = "" Then
    MsgBox ("Please select the location of their next rostered shift.")
    cboLocation.SetFocus
    Exit Sub
End If
If txtReason = "" Then
    MsgBox ("Please enter a reason for absence.")
    txtReason.SetFocus
    Exit Sub
End If
If cboReportingSupervisor.Value = "" Then
    MsgBox ("Please enter the name of the supervisor reporting the sickness.")
    cboReportingSupervisor.SetFocus
    Exit Sub
End If
If txtCoverArranged = "" Then
    MsgBox ("Please give any details of cover arranged, or who has arranged cover.")
    txtCoverArranged.SetFocus
    Exit Sub
End If
Valid = True
End Sub
Sub ResumptionValidation()
ResumptionValid = False
If txtResumptionShift = "" Then
    MsgBox ("Please enter the start time of the shift for which they will be resuming in the format 'hh:mm'.")
    txtResumptionShift.SetFocus
    Exit Sub
End If
If cboResumptionLocation = "" Then
    MsgBox ("Please enter the location at which they will be resuming.")
    txtResumptionlocation.SetFocus
    Exit Sub
End If
If cboReportingSupervisor2.Value = "" Then
    MsgBox ("Please enter the name of the supervisor reporting the resumption.")
    cboReportingSupervisor2.SetFocus
    Exit Sub
End If
If txtMedical = "" Then
    MsgBox ("Please give any details of any medication they may be taking, and/or any restrictions on the duties they can perform on resumption.")
    txtMedical.SetFocus
    Exit Sub
End If
ResumptionValid = True
End Sub
Private Sub cmdOK_Click()
Dim Sickness As Long
Dim Name, Location, Reason, SickShift, Duration, Resumption, Cover, Medication, Comments, Supervisor As String
Sickness = 4
Found = False
ExistingSickness = False
Do Until Found = True
    If Sheets("Sickness").Cells(Sickness, 1).Text = txtSicknessID.Value Then
        ExistingSickness = True
        Found = True
        Else
        If Sheets("Sickness").Cells(Sickness, 1) = "" Then Found = True Else Sickness = Sickness + 1
    End If
Loop
Validation
If Valid = False Then
    Exit Sub
    Else
    PAYENumber = Format(Right(Left(cboEmployee.Value, (Len(cboEmployee.Value) - 1)), 5), "#####")
    EmployeeName = Right(Left(cboEmployee.Value, (Len(cboEmployee.Value) - 8)), (Len(cboEmployee.Value) - (InStr(cboEmployee.Value, ",") + 9))) & Left(cboEmployee.Value, InStr(cboEmployee.Value, ",") - 1)
    
    'Resumption data is filled in first to avoid entering partially complete sickness information,
    'or having to test chkUFN twice.
    If chkUFN.Value = True Then
        
        ResumptionValidation
            
        If ResumptionValid = False Then
            Exit Sub
            Else
            With Sheets("Sickness")
            
                .Cells(Sickness, 14) = Now
                .Cells(Sickness, 15) = cboReportingSupervisor2.Value
                .Cells(Sickness, 16) = txtTime2
                .Cells(Sickness, 17) = Format(txtResumptionDate, "d mmm yy")
                .Cells(Sickness, 18) = txtResumptionShift
                .Cells(Sickness, 19) = cboResumptionLocation
                .Cells(Sickness, 20) = txtMedical
                .Cells(Sickness, 21) = txtComments
            
            End With
        
        End If
        
    End If
        
    If ExistingSickness = False Then
    
        With Sheets("Sickness")
        
            .Cells(Sickness, 1) = Sickness - 3
            .Cells(Sickness, 2) = PAYENumber
            .Cells(Sickness, 3) = EmployeeName
            .Cells(Sickness, 4) = lblLocation.Caption
            .Cells(Sickness, 5) = Now
            .Cells(Sickness, 6) = cboReportingSupervisor.Value
            .Cells(Sickness, 7) = txtTime.Value
            .Cells(Sickness, 8) = txtShiftDate.Value
            .Cells(Sickness, 9) = txtShiftTime.Value
            .Cells(Sickness, 10) = cboLocation
            .Cells(Sickness, 11) = txtReason
            .Cells(Sickness, 12) = txtCoverArranged
            .Cells(Sickness, 13) = txtExpectedDuration
        
        End With
    
    End If
    
    Load frmEmailSickness
    
    If ExistingSickness = True Then
        
        frmEmailSickness.txtSubject.Value = "Resumption: " & frmReportSickness.cboEmployee.Value
        SickShift = ""
        Cover = ""
        Resumption = "Resuming: " & frmReportSickness.txtResumptionShift & " on " & Format(frmReportSickness.txtResumptionDate.Value, "ddd dd mmm") & " at " & frmReportSickness.cboResumptionLocation.Value & Chr(10)
        Medication = "Medication/Restricted Duties: " & frmReportSickness.txtMedical.Value & Chr(10)
        Comments = "Comments: " & frmReportSickness.txtComments.Value & Chr(10)
        Supervisor = Chr(10) & frmReportSickness.cboReportingSupervisor2.Value
        
        Else
            
        frmEmailSickness.txtSubject.Value = "Sickness: " & frmReportSickness.cboEmployee.Value
        SickShift = "Shift: " & frmReportSickness.txtShiftTime.Value & " on " & Format(frmReportSickness.txtShiftDate.Value, "ddd dd mmm") & " at " & frmReportSickness.cboLocation.Value & Chr(10)
        If frmReportSickness.chkUFN.Value = False Then
            If frmReportSickness.txtExpectedDuration.Value = "" Then
                Duration = "Expected Duration: No details at present." & Chr(10)
                Else
                Duration = "Expected Duration: " & frmReportSickness.txtExpectedDuration & Chr(10)
            End If
            Else
            Duration = ""
        End If
        If frmReportSickness.chkUFN.Value = True Then
            Resumption = "Resuming: " & frmReportSickness.txtResumptionShift & " on " & Format(frmReportSickness.txtResumptionDate.Value, "ddd dd mmm") & " at " & frmReportSickness.cboResumptionLocation.Value & Chr(10)
            Else
            Resumption = ""
        End If
        Cover = "Cover Arranged: " & frmReportSickness.txtCoverArranged.Value & Chr(10)
        Medication = ""
        Comments = ""
        If frmReportSickness.chkUFN.Value = True Then
            Medication = "Medication/Restricted Duties: " & frmReportSickness.txtMedical.Value & Chr(10)
            Comments = "Comments: " & frmReportSickness.txtComments.Value & Chr(10)
            Else
            Medication = ""
            Comments = ""
        End If
        Supervisor = Chr(10) & frmReportSickness.cboReportingSupervisor.Value
    End If
    
    Name = "Name: " & frmReportSickness.cboEmployee.Value & Chr(10)
    Location = "Location: " & frmReportSickness.lblLocation.Caption & Chr(10)
    Reason = "Reason: " & frmReportSickness.txtReason.Value & Chr(10)
                                        
    frmEmailSickness.txtMessageBody.Value = Name & Location & Reason & SickShift & Duration & Resumption & Cover & Medication & Comments & Supervisor
                                            
    frmReportSickness.Hide
                                            
    frmEmailSickness.Show
                 
    Unload frmReportSickness
        
End If
 
End Sub
Private Sub cmdResume_Click()
Validation
If Valid = False Then Exit Sub
If chkUFN = False Then chkUFN.Value = True
MultiPage1.Pages("pgeresumesickness").Enabled = True
If MultiPage1.Value < 1 Then MultiPage1.Value = MultiPage1.Value + 1
txtResumptionDate.SetFocus
End Sub
Private Sub cmdCancel_Click()
Unload Me
End Sub

3) The code behind frmEmployeeNotFound:

Dim LastRow As Long
Private Sub UserForm_Initialize()
Dim v, e
frmEmployeeNotFound.Tag = "False"
With Sheets("Staff")
    LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
End With
'cboTitle
'cboStaffLocation
With Sheets("Staff")
    v = .Range("D2:D" & LastRow).Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In v
        If Not .exists(e) Then .Add e, Nothing
    Next
    If .Count Then cboStaffLocation.List = Application.Transpose(.keys)
End With
'cboManager
With Sheets("Staff")
    v = .Range("G2:G" & LastRow).Value
End With
With CreateObject("scripting.dictionary")
    .comparemode = 1
    For Each e In v
        If Not .exists(e) Then .Add e, Nothing
    Next
    If .Count Then cboManager.List = Application.Transpose(.keys)
End With
End Sub
Private Sub txtPayNumber_AfterUpdate()
If IsNumeric(txtPayNumber.Value) Then
    txtPayNumber.Value = Format(txtPayNumber.Value, "00000")
    Else
    MsgBox ("Please enter a valid Pay Number")
    With txtPayNumber
        .SetFocus
        .Value = ""
    End With
End If
End Sub
Private Sub txtInitials_AfterUpdate()
If Not IsNumeric(txtInitials.Value) Then
    txtInitials.Value = StrConv(txtInitials.Value, vbUpperCase)
    Else
    MsgBox ("Please enter their initials")
    With txtInitials
        .Value = ""
        .SetFocus
    End With
End If
End Sub
Private Sub txtSurname_AfterUpdate()
If Not IsNumeric(txtSurname.Value) Then
    txtSurname.Value = StrConv(txtSurname.Value, vbProperCase)
    Else
    MsgBox ("Please enter their Surname")
    With txtSurname
        .Value = ""
        .SetFocus
    End With
End If
End Sub
Private Sub cboStaffLocation_Change()
MsgBox ("Hoohoo")
For Count = 2 To LastRow
    MsgBox ("True" & Sheets("Staff").Cells(Count, 4) & cboStaffLocation)
    If Sheets("Staff").Cells(Count, 4) = cboStaffLocation Then
        MsgBox ("True" & Sheets("Staff").Cells(Count, 4))
        txtLocationNo = Sheets("Staff").Cells(Count, 3)
        Exit Sub
        Else
        'MsgBox (Sheets("Staff").Cells(Count, 4))
        If Count = LastRow Then txtLocationNo = ""
    End If
Next
End Sub
Private Sub txtLocationNo_AfterUpdate()
If IsNumeric(txtLocationNo.Value) Then
    txtLocationNo.Value = Format(txtLocationNo.Value, "000000")
    Else
    MsgBox ("Please enter a valid Location Number")
    With txtLocationNo
        .SetFocus
        .Value = ""
    End With
End If
End Sub
Private Sub txtHomePhone_AfterUpdate()
If Not IsNumeric(txtHomePhone.Value) Then
    MsgBox ("Please enter a valid Phone Number")
    With txtHomePhone
        .SetFocus
        .Value = ""
    End With
End If
End Sub
Private Sub txtMobilePhone_AfterUpdate()
If Not IsNumeric(txtMobilePhone.Value) Then
    MsgBox ("Please enter a valid Phone Number")
    With txtMobilePhone
        .SetFocus
        .Value = ""
    End With
End If
End Sub
Private Sub cmdOK_Click()
If txtPayNumber = "" Then
    MsgBox ("Please enter a valid Pay Number.")
    txtPayNumber.SetFocus
    Exit Sub
    ElseIf cboTitle = "" Then
        MsgBox ("Please select or type a Title.")
        cboTitle.SetFocus
        Exit Sub
    ElseIf txtInitials = "" Then
        MsgBox ("Please enter their initials.")
        txtInitials.SetFocus
        Exit Sub
    ElseIf txtSurname = "" Then
        MsgBox ("Please enter their surname.")
        txtSurname.SetFocus
        Exit Sub
    ElseIf cboStaffLocation = "" Then
        MsgBox ("Please select a location.")
        cboStaffLocation.SetFocus
        Exit Sub
    ElseIf txtLocationNumber = "" Then
        MsgBox ("Please enter a location number.")
        txtLocationNumber.SetFocus
        Exit Sub
    ElseIf txtHomePhone = "" And txtMobilePhone = "" Then
        MsgBox ("Please enter a home or mobilephonenumber.")
        txtHomePhone.SetFocus
        Exit Sub
    ElseIf cboManager = "" Then
        MsgBox ("Please select their manager.")
        cboManager.SetFocus
        Exit Sub
    Else
        LastRow = LastRow + 1
        With Sheets("Staff")
            .Cells(LastRow, 1) = txtPayNumber
            .Cells(LastRow, 2) = cboTitle & " " & txtInitials & " " & txtSurname
            .Cells(LastRow, 3) = txtLocationNumber
            .Cells(LastRow, 4) = cboStaffLocation
            .Cells(LastRow, 5) = txtHomePhone
            .Cells(LastRow, 6) = txtMobilePhone
            .Cells(LastRow, 7) = cboManager
        End With
        Unload Me
End If
End Sub

Private Sub cmdCancel_Click()
With Me
    .Tag = "Cancelled"
    .Hide
End With
End Sub

 

[RoryA]

I can't see anything immediately obvious there - any chance you can post a sanitised copy of the workbook on a site or email it for review?

As an aside, if you write this:

Dim XCount, YCount, SheetIndex As Long

then only SheetIndex is a Long - the other two are Variants. You have to write:

Dim XCount As Long, YCount As Long, SheetIndex As Long

You should also really use variables for your forms and use Me in code within a form that refers to that form. I don't think any of these would cause your issue though.

I do wonder if it is perhaps ribbon related - I will try a quick test on that. Are you using 2007 or 2010?

 

[RoryA]

OK, I've had a look at the file and have a few comments:
1. Your InitialiseRibbon sub won't fire because it is declared incorrectly - it must be exactly this:

Sub InitialiseRibbon(ribbon As IRibbonUI)
    Set MyRibbon = ribbon
End Sub

2. You need to edit the CustomUI so that the button id for the ReportSickness button is not exactly the same as its onAction property. (even changing the case will do).
3. Use the Exit event of your controls rather than the afterupdate, and then simply set Cancel = True if the validation fails - for example:

Private Sub txtPayNumber_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   If IsNumeric(txtPayNumber.Value) Then
       txtPayNumber.Value = Format(txtPayNumber.Value, "00000")
       Else
       MsgBox ("Please enter a valid Pay Number")
      Cancel = True
   End If

End Sub

I'm not entirely sure yet why the AfterUpdate events are not triggered, but the Exit event works fine.

 

[ccordner]

Thanks for this.

I hadn't noticed the InitialiseRibbon not working, because I have nothing in this workbook that uses it. I copied it from another workbook just in case I need it really. I have changed it anyway and have changed the ReportSickness one.

That ribbon has caused me more problems than anything else in Excel. I use the CustomUI Editor, but sometimes I've tried copying in xml code that works in one workbook and saving it, but then it disappears. For some reason, some workbooks don't seem to accept it. I've had to resort to copying whole sheets and code across to a different workbook!

Tried changing to the exit event, which works fine, but of course the combo-box still won't autocomplete. Not only that, but because of this it will only find a match if the text is exactly right.

I can try modifying it to use the 'Instr' function, or tell them they have to click on the list, if you think there's no hope of getting the 'onchange' working...

Thanks ever so much for your help
Chris

 

[RoryA]

I'm not sure why but it really doesn't seem to like being called from the change event. You can move the code to the Exit event of the Employee combo and then it seems to work fine.

 

[ccordner]

Yes, it is odd.

What really puzzles me is that if you have a normal combobox, without any code attached, with a list of items and you start typing one in, it 'autocompletes' for you.

Yet it's not even doing that. It would be okay if I could at least get it to do that, then I could use the 'Exit' event.

But as it is, if you're typing in the combobox, you have no way of knowing if you're typing a valid entry.

This one is strange! Even more so, when you consider that if you go into the VB Editor, run the form itself, it all works perfectly. That's why at first I wondered if I'd used a variable name twice or something.