| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro code to put series name next to individual line in line grap | Charts and Charting in Excel | |||
| Strange issue freezing parent form when unloading a child form | Excel Programming | |||
| Unloading a form using the ESC key | Excel Programming | |||
| How to avoid re-initialisation when Unloading form | Excel Programming | |||
| Unloading A Form | Excel Programming | |||
Linear Mode
