Can a user form be part of a macro?

[drluke]

I have a commandbutton in my spreadsheet, that asks users to choose Yes or No. When Yes is clicked, a macro is called

Here is my code to do that (I think this code is ok):

Private Sub CommandButton1_Click()
Dim Answer As VbMsgBoxResult
    Answer = MsgBox("Existing data will be cleared. Are you sure?", vbYesNo, "Create Journal Template")
        If Answer = vbYes Then
            Call FuncAllocJnl
        End If
End Sub

The form should ideally be part of a macro that utilises the information entered in the form. This part of my FuncAlloJnl
macro opens the userform, and I also show the part of the code that uses information enetered in the form

UserForm1.Show
    
    With wsJournal
        .Rows("7:" & Rows.Count).ClearContents
        .Rows("7:" & Rows.Count).ClearFormats
        .Cells(4, 3).Value = UserForm1.TextBox9
        .Cells(4, 5) = UserForm1.TextBox6
        .Cells(4, 6) = UserForm1.TextBox8
        .Cells(4, 6).NumberFormat = "mm"
        .Cells(4, 8).Value = UserForm1.TextBox10
    End With

My problem is that I can't find
(a) how to pause the macro until the userform is closed,
(b) how to unload the userform textboxes, close and reset the form and let the rest of the macro continue to run, and
(c) when the Close button on the userform is clicked a message box warning the user that the form inputs will be lost and that the macro FuncAllocJnl will be ended. User to confirm with OK/Cancel.

Can this be a part of the macro?

 

[My Aswer Is This]

I do not believe you can start a Macro have it do a few things. Then have it open a Userform.
Then Pause while you enter values into controls on the Userform and then continuing running the script.

I would suggest having the script do what you want. Then in the same script have it open the Userform and script ends here.

Then when you have entered what you want in the Userform controls have a button on the Userform you can click that will then load all the data from the Userform controls into your sheet and continue doing what you want. And at some point close the Userform if you want.

 

[My Aswer Is This]

I'm sorry.

You can do what you asked for.

What does this do:

Call FuncAllocJnl

I see no script here named

FuncAllocJnl
​

_{^{<strike>
</strike>}}​

 

[mikerickson]

You can create a userform that returns a delimited string of entries from multiple controls.

One example would the this code for a userform that returns the three entries from three textboxes.
The userform has three textboxes and two command buttons, butOK and butCancel.

' in userform code module

Private Sub butCancel_Click()
    Unload Me
End Sub

Private Sub butOK_Click()
    Me.Tag = "OK"
    Me.Hide
End Sub

Public Function Value(Optional Delimiter As String = ",", Optional Default1 As String, _
        Optional Default2 As String, Optional default3 As String) As String
        
    With Me
        .TextBox1.Text = Default1
        .TextBox2.Text = Default2
        .TextBox3.Text = defualt3
        .Show
    End With
    
    With UserForm1
        If .Tag = "OK" Then
            Value = TextBox1.Text & Delimiter & TextBox2.Text & Delimiter & TextBox3.Text
        End If
    End With
    
    Unload UserForm1
End Function

One would would use that userform similar to how one uses an InputBox.

' in normal module

Sub test()
    Dim uiTriplet As String
    
    uiTriplet = UserForm1.Value

    If uiTriplet = vbNullString Then
        Rem cancel pressed
        Exit Sub
    Else
        Range("A1:C1") = Split(uiTriplet, ",")
    End If

End Sub

Note the unused, optional Default and Delimiter arguments.

 

[mikerickson]

I should point out that, like InputBox, the macro that calls the Userform1.Value function will pause until a value (or cancel) is returned.

 

[drluke]

Thank you all!