Pass value from userform textbox to vba procedure

FryGirl

Well-known Member
Joined
Nov 11, 2008
Messages
1,360
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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 ""

Code:
i = Val(frmUserForm.txtnumber.Text)
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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

Code:
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

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

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

Code:
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 :)
 
Upvote 0

Forum statistics

Threads
1,213,563
Messages
6,114,332
Members
448,566
Latest member
Nickdozaj

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top