VB Code to Show User Form so it can be checked then go to Module to run addtional code

Hello All,

I'm trying to figure out how to write code to do the following AFTER a Button is selected on an Excel Spreadsheet (NOTE: the button on the Excel Spreadsheet is currently assigned to the Macro Name: Customer_Prep_Email)

1.) Open UserForm1 (a check box with a comment is on the form) and allow the user to check the box
2.) After the the Checkbox is selected, I would like to have the button disappear from the Excel Spreadsheet
3.) Once the box has been checked - go to the module and run the code that I have there.

Below is the current code that I have, the problem is that I can't get the UserForm1 to pop-up and allow the user to check the box. I originally had code to show the UserForm1 and allow the user to check the box, but then couldn't get it to run the rest of the code:

CODE WITHIN THE UserForm1:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

CODE WITHIN THE Module1

Please Login or Register  to view this content.

'Sub Customer_PO_Acknowledgement()

'UserForm1.Show


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

Please Login or Register  to view this content.

If it's possible, I would also like to create the document to be converted to a PDF before saving it and then saving it as PDF and emailing a PDF Document.

I WOULD GREATLY appreaciate any help with this issue, I've been searching and trying differnt code for a few days now. I'm not strong with VB code but need it to accomplish my task.

Thank you, in advance, for your assistance.

CODE WITHIN THE Module1

Please Login or Register  to view this content.

you have to eliminate char ' and add end sub

Hi, tcrjmom,

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger