Workbook Closes after Submit button pressed

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
629
Office Version
  1. 365
Platform
  1. Windows
Good Morning,

I am using the following code to submit a form that is completed by an agent in work. The file keeps closing when it has submitted and i need it to re loop after the Msg box at the end. If it it finds no open enquiries i need it then to confirm no work outstanding. Then to the file needs to stay open on the "Front Sheet" Tab

Any help would be appreciated:confused:


Private Sub cmdsubmit_Click()
Dim iRow As Long
Dim ws As Worksheet
If Me.JEN.Value = "" Then
MsgBox "Please Complete is this a justified Enquiry"
Else
If Me.AComm.Value = "" Then
MsgBox "Please Leave Your Comments"
Else
If Me.CBoxAdd.Value = "" Then
MsgBox "Please Enter Your Name"
Else
If Me.TextBox12.Value = "" Then
MsgBox "Please Enter Todays Date"
Else
Workbooks.Open Filename:="\\W2K6082\COMMON\SHARED\Simone's Team\Misc Spreadsheets\SimoneData.xls" 'File saved area
Set ws = Worksheets("Data")
iRow = ws.Cells(Rows.Count, 11) _
.End(xlUp).Offset(1, 0).Row
'copy the data to the database
ws.Cells(iRow, 11).Value = Me.JEN.Value
ws.Cells(iRow, 12).Value = Me.AComm.Value
ws.Cells(iRow, 13).Value = Me.CBoxAdd.Value
ws.Cells(iRow, 14).Value = Me.TextBox12.Value
'clear the data
Me.JEN.Value = ""
Me.AComm.Value = ""
Me.CBoxAdd.Value = ""
Me.TextBox12.Value = ""
Me.txtdate.Enabled = True
Me.txtdate.SetFocus
Windows("SimoneData.xls").Activate
ActiveWindow.Close (True)
Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

MsgBox "Your Car Park has been Completed"
ActiveWorkbook.Close False

End If
End If
End If
End If

End Sub
 

Some videos you may like

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

gmazza76

Well-known Member
Joined
Mar 19, 2011
Messages
629
Office Version
  1. 365
Platform
  1. Windows
When i remove the info as suggested it just leaves the form showing with the previous data.:(
 

Watch MrExcel Video

Forum statistics

Threads
1,122,840
Messages
5,598,386
Members
414,234
Latest member
grlevesq

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top