Store forms input into variable

[Shiro26]

Hi,

I have a code which is activated by a button click.
After several seconds of run, I show a form containing two textboxes named TB1 and TB2.

How can I store the values that the user enters in TB1 and TB2 into two variables named respectively TB1val and TB2val for example, after clicking an 'OK' button on the form?
Clicking the 'OK' button would also close the form and resume my code with my variables filled

I would like these parameters to remainstored until the end of the macro run
I know how to do that when there is only one value using an inputbox but with more, how can I do that?

thank you for your help.

Best,

Shiro

 

[pbornemeier]

I believe the values in the text box will be retained as long as the code is running, so referring to userform.TB1.value and userform1.TB2.value would bring them back.
If for some reason that does not work, you can define TB1val and TB2val as global variables or save the TB1val and TB2val to cells on a worksheet when the form OK button is clicked.

 

[Norie]

How are you resuming the code after the OK button on the form has been clicked?

 

[mikerickson]

If the user form is unloaded user form wide variables will pass out of scope.

You could put code like this in the user form

'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 GetValues(ByRef Value1 As String, ByRef Value2 As String) As Boolean
    Me.Show
    If UserForm1.Tag = "OK" Then
        Value1 = Me.TextBox1.Text
        Value2 = Me.TextBox2.Text
        GetValues = True
    Else
        GetValues = False
    End If
    Unload UserForm1
End Function

(Note: the use of "Userform1" (your user form's name in practice) rather than the keyword Me in GetValues is critical)

And call it from a normal module like this.

Sub test()
    Dim TB1Val As String, TB2Val As String
    
    TB1Val = "a": TB2Val = "b"
    
    If UserForm1.GetValues(TB1Val, TB2Val) Then
        Rem all is good
    Else
        MsgBox "cancel pressed. Values not changed"
    End If
    MsgBox TB1Val & vbCr & TB2Val
End Sub

How long TB1Val and TB2Val retained their values would depend on the scope of their dimensioning.

 

[Shiro26]

Hi all,

Thank you very much for your input, I am going to try the solutions you have provided.

Could you please help me understand better that line in the normal sub?

TB1Val = "a": TB2Val = "b"

An additional question though.
After the code has ended, will the dimensioned values all be reset or kept in the memory until the workbook is closed?

Kind Regards,

Shiro

 

[mikerickson]

That line was just to set some initial values so you could see the effect of pressing Cancel rather than OK.
It's a demo line only and is not needed in practice.

 

[Shiro26]

That line was just to set some initial values so you could see the effect of pressing Cancel rather than OK.
It's a demo line only and is not needed in practice.

Very clear, I have adapted it and going to run it to see whether I am missing something.

Thank you for your kind help.

Shiro

 

[Shiro26]

If the user form is unloaded user form wide variables will pass out of scope.

You could put code like this in the user form

'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 GetValues(ByRef Value1 As String, ByRef Value2 As String) As Boolean
    Me.Show
    If UserForm1.Tag = "OK" Then
        Value1 = Me.TextBox1.Text
        Value2 = Me.TextBox2.Text
        GetValues = True
    Else
        GetValues = False
    End If
    Unload UserForm1
End Function

(Note: the use of "Userform1" (your user form's name in practice) rather than the keyword Me in GetValues is critical)

And call it from a normal module like this.

Sub test()
    Dim TB1Val As String, TB2Val As String
    
    TB1Val = "a": TB2Val = "b"
    
    If UserForm1.GetValues(TB1Val, TB2Val) Then
        Rem all is good
    Else
        MsgBox "cancel pressed. Values not changed"
    End If
    MsgBox TB1Val & vbCr & TB2Val
End Sub

How long TB1Val and TB2Val retained their values would depend on the scope of their dimensioning.

Hi Again,

Sorry, I have two difficulties.

in my module:

~~before code~~

>>>Call form<<<

Dim TB1Val As String, TB2Val As String
    
    TB1Val = "a": TB2Val = "b"

  If UserForm1.GetValues(TB1Val, TB2Val) Then
        Rem all is good
    Else
        MsgBox "cancel pressed. Values not changed. Macro interrupted"
    End If

~~Code After using the values TB1Val and TB2Val~~

How do I call the form from my module?

Thank you in advance.

Kind Regards,

Shiro

 

[Shiro26]

Hi,
nobody can help me understanding further? :/
Kind Regards
Shiro

 

[pbornemeier]

Put this in a standard module

Sub OpenAndProcessForm()

    UserForm1.Show

    Dim bStatus As Boolean, value1 As String, value2 As String
    
    If UserForm1.Tag = "OK" Then
        bStatus = UserForm1.GetValues(value1, value2)
        
        Stop
        'your code processing value1 and value2 here
    Else
    
        'User choose Cancel
        Stop
        
    End If
    
End Sub