Pass value from userform textbox to vba procedure

[FryGirl]

I have a userform where the user will input a number into a textbox and that value should be passed to a vba procedure for use as a variable.

I have declared the variable in vba as long, but when stepping through the procedure it show this line as ""

i = Val(frmUserForm.txtnumber.Text)

 

[Colin Legg]

It's a guess, but perhaps the original instance of the userform (where the number was entered) has been destroyed and you are auto-instantiating a new instance of the userform in that line of code and hence the textbox is empty?

 

[FryGirl]

Hi Colin,

It's a guess, but perhaps the original instance of the userform (where the number was entered) has been destroyed and you are auto-instantiating a new instance of the userform in that line of code and hence the textbox is empty?

Not 100% sure what you mean by auto-instantiating, but again stepping through the code is shows the variable as zero and the txtnumber as "".

Are you saying that when the number is entered and the close button on the form is activated the entered number is dropped?

Does the variable need to be declared as global? In the user form code in the declaration line under Option Explicit I put Public i as Long but no luck

 

[Colin Legg]

Are you saying that when the number is entered and the close button on the form is activated the entered number is dropped?

Yes, exactly. The userform is toast if the close button is pressed so you won't be able to retrieve the number in the textbox afterwards. Then, a new instance of the userform is automatically created (auto-instantiated) on that line of your code.

Perhaps you could add a "Process" button to your userform which the user clicks on once they have entered the relevant information. That button could then pass the value in the textbox to your procedure and, once the processing is done, close the userform?

 

[FryGirl]

Hi Colin,

Thanks for the direction and that is a good idea about adding a process button, but the drawback, I would have to ensure the button is pressed. Instead, I will just have the value written to a sheet and then have the procedure retrieve it from the sheet. This seems to be a good solution for me.

Thanks for all of your time

 

[Rasm]

Try This


code in unserform or whereever - you can declare them as well - if you are passing more than one variable separate them by commas

Call NameOfSub(i)





Public Sub NameOfSub(i)
'Now 'i' is passed
End Sub

 

[pgc01]

In the user form code in the declaration line under Option Explicit I put Public i as Long but no luck

That is a good idea, using a public variable avoids writing the value in a cell an then read it later. You just have to declare the public variable in a standard module, not in the userform module.

 

[Rasm]

You dont have to make it a Public variable - you can dim it under option explicit - making it global (but not public)- or inside the textBox_click event or whatever event you are using for the textbox

 

[FryGirl]

Thanks for all the direction, I think I have it all sorted out now. In case somebody is following this

In a standard module at the top I put

Option Explicit
Public i As Long

Sub OpenUserForm1()
    UserForm1.Show
End Sub

Sub Test()
    Sheets("Sheet1").Range("A1").Copy Sheets("Sheet").Range("A1").Resize(i)
End Sub

then in the UserForm

Private Sub TextBox1_AfterUpdate()
    i = Me.TextBox1.Text
End Sub

then just to test it placed a command button on the UserForm

Private Sub CommandButton1_Click()
    Unload Me
    Call Test
End Sub

The test sub is just taking what's in A1, copying and then resizing it to the variable of i.

Took me a while to understand it, but thanks to everyone's help I think it's sinking in. Thanks again for the help

 

[Rasm]

Good luck