excelwaitbefore-savevba

VBA to wait for response from UserForm and Import to table BeforeSave?

How do I have VBA wait for response from the UserForm "SavePrompt", then depending on the selection (Ok or Cancel) have the VBA continue?

I need the user to fill out a text box before the workbook saves.

This information will be then imported to column B on Table1. Column A is the current date and time.

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) = TextBox1 'this is from the SavePrompt Userform
End With

End Sub
Solution

  • TextBox1 is a property of SavePrompt instance, not a variable.

    Instead of this:

    With newrow
    .Range(1) = Now
    .Range(2) = TextBox1 'this is from the SavePrompt Userform
End With

    ... you should write this: 

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