How to have VBA select a button on a userform within another workbook?

Question

So I am creating thousands of workbooks using a VBA from a Master Workbook. I have a template the Vba pastes to. This template workbook has a VBA

BeforeSave

function where the user has to fill out a Userform to log changes the user decided to make durring that session.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

Dim ws As Worksheet
Set ws = Sheets("EDITS")
Dim tbl As ListObject
Set tbl = ws.ListObjects("Table1")
Dim newrow As ListRow
Set newrow = tbl.ListRows.Add

SavePrompt.Show

With newrow
    .Range(1) = Now
    .Range(2) = SavePrompt.TextBox1.Text
End With

End Sub

My problem now is that the VBA I run is in the master workbook. (just to be clear) it opens the template, Copies Row5 from Master Workbook and Pastes to Row4 on Sheet 2 on Template Workbook, then saves..

wkbDest.SaveAs Filename:=Path & FileName2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled

Here's the Problem because the BeforeSave VBA runs, opening a Userform. How can I have this Master VBA select the CommandButton1 on the Userform that is now open and pausing the Macro?

then closes..

wkbDest.Close

Storax Storax · Accepted Answer · 2018-05-25T16:21:00

Based on my comment I suggest to disable events before you save

Application.EnableEvents = False
wkbDest.SaveAs Filename:=Path & FileName2 & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
Application.EnableEvents = True

And the code triggered by BeforeSave event will not run.

How to have VBA select a button on a userform within another workbook?

1 Answers