vendredi 17 juin 2016

Excel VBA - How to run a macro inside a module

I have a delivery note spreadsheet that holds customer and product data. I want a button than when clicked Asks 'Do you want to Save as well as Print?'. I have recorded a macro for the file to be saved that works fine when the module is run on its own but when it is all put together in the code below I get the following error: 'Compile Error: Expected End Sub'. The else part also runs fine on its own. How do I solve this error and get the code to run? Thanks

Private Sub CommandButton1_Click()
MsgBox "Do you want to Save as well as Print?", vbYesNo
If answer = vbYes Then
Sub mac_SaveNote()
ChDir "C:\Users\User\Desktop\DeliveryNotes"
ActiveWorkbook.SaveAs Filename:="C:\Users\User\Desktop\DeliveryNotes\" & Range("A11"), _
    FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
End Sub
Else
Sheets("Note 1").PrintOut , Copies:=2   'prints 2 copies of note1
Range("A11:J16").ClearContents          'clears customer data
Range("A18:I42").ClearContents          'clears product data
End If
End Sub

Aucun commentaire:

Enregistrer un commentaire