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)

 

[FryGirl]

Okay now I got stuck again. I have declard the variable global in a standard module, but not how do I empty that variable.

If the user opens the form and adds a number to the textbox and the procedure performs its function, the next time the user opens the form and hits cancel but has not entered anything in the textbox, the public variable is still stored.

How can I empty out the variable after the procedure has run?

 

[Rasm]

Not sure of your code - but try

i=Nothing or Set i=Nothing

 

[Rasm]

try this

Sub Test()
    Sheets("Sheet1").Range("A1").Copy Sheets("Sheet").Range("A1").Resize(i)
    TextBox1.Text = vbNullString
End Sub
 
Private Sub TextBox1_Change()
    i = Val(TextBox1.Text)
End Sub

 

[FryGirl]

Rasm,

Thanks again for the help, but I couldn't seem to get it to work with your method.

I have found another method which works. On form Initialize I've made the value of the textbox = to 0 so if they click the button I added if i = 0 then exit sub.

Works grand.

 

[Norie]

Gail

You shouldn't need to use a global/public variable for this.

In fact you could do it without a variable.

Just pass the value from the form to the sub in the other module before you unload the form.

Module1:

Option Explicit
 
Sub OpenUserForm1()

    UserForm1.Show

End Sub
 
Sub Test(X As Long)

    Sheets("Sheet1").Range("A1").Copy Sheets("Sheet2").Range("A1").Resize(X)

End Sub

Userform Module:

Option Explicit
 
Private Sub CommandButton1_Click()
    Test CLng(TextBox1.Value)
    Unload Me
End Sub

This worked fine for me, though I'm sure it's not perfect and there's probably a bit more to what you want to do.

 

[FryGirl]

So for the delay Norie, this worked great for my particular process.

 

[Scott B]

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)

I used your code to do something that's probably easy, BUT it does not work. SCREENSHOT:

Help me make this work! Download Link to Exported File: CLICK

 

[Norie]

What's ttl?