VBA To Launch And Complete Workbook

I'm nearing the end of my project and I have a few outstanding code queries if anyone could spare a little time to help me resolve them.

I have attached a dummy workbook and would like to know (if it's possible) how to do the following:

• Submit Button automatically opens form "example" in a seperate workbook and completes nominated Cells if the "Launch Test Form"check box is ticked. (eg, fields 1, 3, 6 to fill cells in Workbook "Example" A1, C12, H14 Respectively)
• Userform automatically saves and close when Submit is pressed.
• Cannot be closed with X (or clicking x closes aplication as well as form)

Many thanks in anticipation of any help that may result. I am online all the time to answer any queries.

(Password for form "Unlock" is 12345)

Friendly bump just in case some fresh eyes catch it!

Of the top of my head for the saves and close when subit is met, have all your code that might write to a sheet first then at the bottom so it reads it last you need something to call a sub, ie call force save then code off top of my head might be like

Public Sub Save_Survey(ForceSave)

Start:

Dim MySaveFile As String 'Used to switch between Survey and New File only
Dim FileName As String

MySaveTime = Format(Date, "yyyy-mm-dd")

'Set Default Initial FileName if required
If OpenSurveyFilename = vbNullString Then OpenSurveyFilename = "HPC_Survey_" & MySaveTime

'Save this Survey
If ForceSave = True Then
    fileSaveName = Application.GetSaveAsFilename(InitialFileName:=OpenSurveyFilename, FileFilter:="Excel Files (*.xls), *.xls", Title:="Forced save of Project: " & HPC_Main.PROJECT.Value)
Else
    fileSaveName = Application.GetSaveAsFilename(InitialFileName:=OpenSurveyFilename, FileFilter:="Excel Files (*.xls), *.xls", Title:="Save Project: " & HPC_Main.PROJECT.Value)
End If

If fileSaveName = False Then
    'Check if this is a forced save
    If ForceSave = True Then
        Response = MsgBox("You must save your project before the data is finally submitted!", vbCritical)
        GoTo Start
    Else
        GoTo Finish
    End If
End If

this might give you an idea, but you call that sub in your submit click then the close would be an unload me etc after that

Originally Posted by kieranbop

Of the top of my head for the saves and close when subit is met, have all your code that might write to a sheet first then at the bottom so it reads it last you need something to call a sub, ie call force save then code off top of my head might be like


this might give you an idea, but you call that sub in your submit click then the close would be an unload me etc after that

Thanks for that, but how would I insert a sub in a sub?

I have a similar problem with another piece of code I'm using to launch the second workbook:

[code]
Private Sub ChkCorrection_Click()
Workbooks.Open FileName:= _
"filename and path"
Windows("Real.xlsm").Activate
End Sub
[End Sub]

If I attach the code to a command button and click the button, the form launces as requested, but when I assign the code to a tick box, I cannot figure out where to put the code. Logic would say it should go somewhere before the unload command in the Submit Button code, but I can't figure out how to make it work.

Any suggestions would be most welcome!

for your last question add this code to you user form module:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim Answer As VbMsgBoxResult
If Not CloseMode = vbFormCode Then
    Answer = MsgBox(prompt:="Are you sure you want close the form and exit the applicatoin?", Title:="Application", Buttons:=vbYesNo + vbQuestion)
    
    If Answer = vbYes Then
        Unload Me
        Application.Quit
    Else
        Cancel = True
    End If
End If

End Sub