Re: Form not unloading before next code line

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Tom,

Many thanks that did it.

Its amazing how many times one overlooks the simple things !

Regards

Michael

"Tom Ogilvy" <twogilvy@xxxxxxx> wrote in message
news:upCsR%23qFGHA.740@xxxxxxxxxxxxxxxxxxxxxxx
> Application.ScreenUpdating = True
> Unload frmHR
> doevents
> ActiveCell.Offset(0, 2).Select
>
> Would be an additional suggestion
>
> --
> Regards,
> Tom Ogilvy
>
>
>
> "Michael Beckinsale" <michael.beckinsale@xxxxxxxxxxxx> wrote in message
> news:e1efW8qFGHA.3912@xxxxxxxxxxxxxxxxxxxxxxx
>> 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" <twogilvy@xxxxxxx> wrote in message
>> news:uBojD3qFGHA.740@xxxxxxxxxxxxxxxxxxxxxxx
>> > Try adding a doevents:
>> >
>> > Unload frmHR
>> > doevents
>> > ActiveCell.Offset(0, 2).Select
>> >
>> > --
>> > Regards,
>> > Tom Ogilvy
>> >
>> >
>> > "Michael Beckinsale" <michael.beckinsale@xxxxxxxxxxxx> wrote in message
>> > news:e69CItpFGHA.1100@xxxxxxxxxxxxxxxxxxxxxxx
>> >> 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
>> >>
>> >>
>> >
>> >
>>
>>
>
>


.

Relevant Pages