Form not unloading before next code line

[Michael Beckinsale]

Hi All,

I have a large form which l need to unload before the MsgBox asks the user
if they want to move to the next record.

The code below works but does not unload frmHR before the MsgBox appears.
Can anybody tell me whats wrong?

Regards

Michael Beckinsale


Private Sub cmbHREnter_Click()

Dim RowNo
Dim Q1

Call Unprotect
'Assign text box values to variables
Nino = tbNino.Text
Surname = tbSurname.Text
Forename1 = tbForename1.Text
Forename2 = tbForename2.Text
DOB = tbDOB.Value
DPSno = tbDPS.Value
EstCode = tbEstCode.Text
Est = tbEst.Text
Quantum = tbQuantum.Text
Email = tbEmail.Text
Grade = cbxGrade.Value
ContStaff = cbxStaff.Value
Salutation = cbxSalutation.Value
Status = cbxStatus.Value
Gender = cbxGender.Value
LineManager = cbxLineManager.Value
ContArea = cbxContractorarea.Value
Ethnic = cbxEthnicgroup.Value
'Enter variable values / text into relevant cells
ActiveCell.Offset(0, -1).Value = Nino
ActiveCell.Value = Surname
ActiveCell.Offset(0, 1).Value = Forename1
ActiveCell.Offset(0, 2).Value = Forename2
ActiveCell.Offset(0, 3).Value = DOB
ActiveCell.Offset(0, 4).Value = DPSno
ActiveCell.Offset(0, 10).Value = EstCode
ActiveCell.Offset(0, 11).Value = Est
ActiveCell.Offset(0, 12).Value = Quantum
ActiveCell.Offset(0, 13).Value = Email
ActiveCell.Offset(0, 8).Value = Grade
ActiveCell.Offset(0, 14).Value = ContStaff
ActiveCell.Offset(0, 15).Value = Salutation
ActiveCell.Offset(0, 16).Value = Status
ActiveCell.Offset(0, 17).Value = Gender
ActiveCell.Offset(0, 18).Value = LineManager
ActiveCell.Offset(0, 19).Value = ContArea
ActiveCell.Offset(0, 20).Value = Ethnic
'Highlight row in red to indicate it has been checked
ActiveCell.Offset(0, -2).Value = "Yes"
RowNo = ActiveCell.Row
Rows(RowNo).Select
Selection.Font.ColorIndex = 3
Unload frmHR
ActiveCell.Offset(0, 2).Select
'Application.Wait Now + TimeValue("00:00:05") Entered this to see if it
would unload before next code line
'Ask user if they want to move to next record or exit
Q1 = MsgBox("Do you want to move to the next record ?", vbYesNo,
"Question")
If Q1 = vbYes Then
ActiveCell.Offset(1, 0).Select
frmHR.Show
Else
Call Protect
Exit Sub
End If

Call Protect

End Sub

 

[Tom Ogilvy]

Try adding a doevents:

Unload frmHR
doevents
ActiveCell.Offset(0, 2).Select

 

[Michael Beckinsale]

Tom,

Thanks for the reply.

I have tried what you suggested and l still get the MsgBox before the frmHR
unloads! Any idea's please.

I am using Excel 2003 / Windows XP

Regards

Michael

 

[Tom Ogilvy]

Application.ScreenUpdating = True
Unload frmHR
doevents
ActiveCell.Offset(0, 2).Select

Would be an additional suggestion

 

[Michael Beckinsale]

Tom,

Many thanks that did it.

Its amazing how many times one overlooks the simple things !

Regards

Michael