ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Form not unloading before next code line (https://www.excelbanter.com/excel-programming/350079-form-not-unloading-before-next-code-line.html)

Michael Beckinsale
Form not unloading before next code line
 
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
Form not unloading before next code line
 
Try adding a doevents:

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

--
Regards,
Tom Ogilvy


"Michael Beckinsale" wrote in message
...
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

Michael Beckinsale
Form not unloading before next code line
 
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" wrote in message
...
Try adding a doevents:

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

--
Regards,
Tom Ogilvy


"Michael Beckinsale" wrote in message
...
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
Form not unloading before next code line
 
Application.ScreenUpdating = True
Unload frmHR
doevents
ActiveCell.Offset(0, 2).Select

Would be an additional suggestion

--
Regards,
Tom Ogilvy



"Michael Beckinsale" wrote in message
...
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" wrote in message
...
Try adding a doevents:

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

--
Regards,
Tom Ogilvy


"Michael Beckinsale" wrote in message
...
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

Michael Beckinsale
Form not unloading before next code line
 
Tom,

Many thanks that did it.

Its amazing how many times one overlooks the simple things !

Regards

Michael

"Tom Ogilvy" wrote in message
...
Application.ScreenUpdating = True
Unload frmHR
doevents
ActiveCell.Offset(0, 2).Select

Would be an additional suggestion

--
Regards,
Tom Ogilvy



"Michael Beckinsale" wrote in message
...
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" wrote in message
...
Try adding a doevents:

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

--
Regards,
Tom Ogilvy


"Michael Beckinsale" wrote in message
...
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


All times are GMT +1. The time now is 05:41 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com