vb code needed to finish after a check box is selected on a UserForm

[tcrjmom]

Hello All,

I'm trying to write code to allow a user of the excel spreadsheet to click a button then a pop-up box appears that needs to be checked, once it's checked the code that I have will save the document and then prepare it to be emailed. The problem that I'm having is I don't know how to write the code to have the rest of the code to start working after the check box is selected. Below is the code that I currently have - once the button is selected in Excel - a pop up appears and states they need to check the box and this is where I get stuck:


Sub Customer_PO_Acknowledgement()
UserForm1.Show

' Customer_Usage_Report Macro
' This macro will allow the document to be prepared and sent to Dell

Sheets("CUSTOMER").Copy

filenam = "C:\Customer Purchase Order Form" & Range("F4") & ("_") & Range("C5") & (" ") & Range("F5") & ("_") & Format(Now(), "mmddyy") & ".xlsm"

ActiveSheet.Shapes.SelectAll
Selection.Delete
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False
ActiveWorkbook.SaveAs Filename:=filenam, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close

Windows("Customer Purchase order Form.xlsm").Activate

End Sub




Thank you, for your assistance.

 

[JLGWhiz]

In development mode, right click your checkbox on your form and then click 'View Code' in the pop up menu. Copy the code below into the code window that opens.

Private Sub Checkbox1_Click() 'If not Checkbox 1, change to appropriate index number in title and elsewhere in the code.
If Me.Checkbox1.Value = True Then
 Customer_PO_Acknowledgement
End If
End Sub

If the Checkbox equals False when the form appears and the user clicks it, then it will call the other macro. If the Checkbox equals True when the form appears and the user clicks it, nothing will happen. The default for Checkboxes is normally 'False' or blank.

 

[tcrjmom]

Thank you, very much, for your help.

I tried the code but now it will not show the Pop-up window so they can check it.

I have a Submit button in Excel
Once it's clicked - the code should run through the code, bring up UserForm1 which has the check box on it and a comment. At that point the box needs to be checked and then the vb code should start the process of saving the document and preparing the email.

I have to have the code show the UserForm1 pop-up 1st then the customer check the box then the save and email code will kick in.

I'm sorry, I'm not really strong with VB code and maybe missing a VERY EASY step.

Regards,

 

[JLGWhiz]

Are you using a MSO Forms control button or an Active-X button?
If using the MSO Forms you would need to attach your Custom_PO_Acknowledgement macro to the button by right clicking the button and select 'Attach Macro' from the pop up menu.
If using an Active-X button, enter design mode and right click the button, then click 'View Code' in the pup up menu. Change the title line of your 'Custom_PO_Acknowledgement' mactor to:

Private Sub CommandButton1_Click()

Then copy the code into the code window that opens.

If it is not CommandButton1 then change that part to whatever the actual button naem is.

 

[tcrjmom]

Hello JLGWhiz,

THANK YOU VERY MUCH for your help! I was re-reading your previous post and notice that I had a typo error in the code and once I fixed the erro - the code worked.

I have two more questions that I have and hope that you can help so I can finish this project:

1.) How do I write the code to hide the button from the Excel Spreadsheet after is is selected?
2.) How do I make the UserForm1 disappear after the Check box is selelcted and before the code moves onto the the Customer_Prep_Email command?

Below is the code that I currently have:

UserForm1 Code

Private Sub Checkbox1_Click() 'If not Checkbox 1, change to appropriate index number in title and elsewhere in the code.
If Me.CheckBox1.Value = True Then
 Customer_Prep_Email
End If
End Sub

Module1 Code:

 'user can create email only if check box is selected
   
'This macro will allow the document to be prepared to send to processing

Sub Customer_PO_Acknowledgement()
    UserForm1.Show
End Sub
Sub Customer_Prep_Email()
Sheets("SWPO").Copy
filenam = "C:\Customer Purchase Order Form" & Range("F4") & ("_") & Range("C5") & (" ") & Range("F5") & ("_") & Format(Now(), "mmddyy") & ".xlsm"
ActiveSheet.Shapes.SelectAll
Selection.Delete
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False
ActiveWorkbook.SaveAs Filename:=filenam, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
Application.Dialogs(xlDialogSendMail).Show
ActiveWorkbook.Close
Windows("Customer Purchase order Form.xlsm").Activate

End Sub