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

Maybe it's better to disable events instead. – Storax May 25 '18 at 16:15 — Storax, May 25 '18 at 16:15

score 1 · Answer 1 · answered May 25 '18 at 16:21

1

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.

answered May 25 '18 at 16:21

Storax

11,158
3
16
33

Even though this doesn't answer my direct question. It solved my problem. thankyou – Kenny May 25 '18 at 17:21

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

1 Answers1