OLE! UserForm. Huh???

[wapperdude]

Add a UserForm to get input from User to be used by currently running code macro.  OK.  Seems straight forward.  Perhaps.  Not for me. 

Searching the web generally sends to you to NeverNeverLand, aka, Excel. Of those results, none proved useful for my task.  Perhaps the one important step is globally declaring variables of interest.

So, here's the background.  I have some code that needs User entry for variable values.  A UserForm with TextBox object is the obvious method.  Easy enough, since I like to KISS, I created simple form with a TextBox and a CommandButton.  That's all.  The test macro was also simple as it called the form, and then debug printed the results. 

The code declares the variable as Public.  I think Dim would've worked too.  The sub invokes the UserForm with the Call command.  From the UserForm, User enters a value, it gets assigned to the variable, and then the form is hidden and returned main sub.  Whereupon Debug.Print prints result.  Except the varialbe does not contain the User inputed value.  Fail!!!
The unsuccessful code:

Code Select Expand

Public ufTB1 As Double
 Sub OpenUF1()
    Dim frm As New UserForm1
    frm.Show
    Debug.Print ufTB1
    Unload frm
End Sub
UserForm code:
Note, the print statement shows that ufTB1 was successfully set.

Code Select Expand

Private Sub CommandButton1_Click()
    ufTB1 = TextBox1.Value      'Assign entry to desired variable.
    Debug.Print ufTB1           'Verify variable value
    Me.hide
End Sub

After much trial and error, my successful, working solution, is as follows.  This seems somewhat counter intuitive.  The form is merely invoked, allows user input, hidden and returns to main code.  Main code then sets the variable value to the form entry.  Print statement confirms.  Form is closed.  Final print shows that variable value is retained and useable.

Does anyone have an alternative approach?  If so, please provide with working solution.
Final main macro code:

Code Select Expand

Public ufTB1 As Double
Sub OpenUF1()
    Dim frm As New UserForm1
    frm.Show
    ufTB1 = frm.TextBox1.Value  'Value assigned to variable here, not in UserForm code.
    Debug.Print ufTB1
    Unload frm
    Debug.Print 2 * ufTB1
End Sub

Final user form code:

Code Select Expand

Private Sub CommandButton1_Click()
    Me.hide          'Hide form and return to main macro
End Sub


[Yacine]

Nope, my ChatGPT told me the same method as yours.