Run Userform within VBA code?

[filarap]

Hi All,

I am trying to find a way to have a userform appear while the code is running and to set a condition if the code will continue to run, based on selection made in the userform.


Below is the part of the code. What I am trying to achieve is to have a step that will check is the user wants to send an email or not and to have this within the userform with options (yes/no). I have no issue to create the condition part, but i cannot make the form appear. The process should follow steps - Run 1st form, fill data, press command button, code starts and fills data in the sheets, then the next form apears asking if email needs to be send, and if yes, code continues, if no, code stops.

I tried to use userform.show, but it gives me the error file not found?

.....If holiday.value = True Then
Sheet4.Range("m" & Rows.Count).End(xlUp).Offset(1, 0).value = "Yes"
Else
Sheet4.Range("m" & Rows.Count).End(xlUp).Offset(1, 0).value = "No"
End If


Sheet4.Range("s" & Rows.Count).End(xlUp).Offset(1, 0).value = comments.value


This is where i would need the help




Dim xOutApp As Object
Dim xOutMail As Object
Dim xMailBody As String
Dim typesl As String
Dim dayofftype As String .......

PLease help
Regards
Filarap

 

[Logit]

.
Here is a short example, missing some of the macro code for writing data to excel and the email code :

Option Explicit


Private Sub CommandButton1_Click()
Dim answer As Variant


'place macro code here to write data to excel sheet


Unload Me 'unload userform


  answer = MsgBox("Do you want to send email ?", vbYesNo + vbQuestion, "Email Send ?")
  
  If answer = vbYes Then
    Call EmailSend
   Else
    Exit Sub
End If


End Sub


Sub EmailSend()
   'macro for sending email here
End Sub



Sub EmailSend()
   'macro for sending email here
End Sub

Create a UserForm with one commandbutton. The CommandButton1_Click macro begins with your macro code to write the data to the worksheet. Then it unloads the UserForm and immediately displays a MsgBox. The
MsgBox gives the user an opportunity to send the email or opt out. If the answer is yes, the MsgBox calls the email macro (code to by inserted) and the MsgBox closes. The email is sent.

 

[Norie]

All you should need is this to show the userform.

nameofuseform.Show

 

[filarap]

Thank you both,

I managed to achieve the goal with 2 subs. Thank you Logit, i did not know the "vbYesNo + vbQuestion" until now, so thanks for that as well

Norie, i tried userformname.show, but it didn't work for me.

Kind regards
Filarap

 

[Logit]

You are welcome.