Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Pass value from userform textbox to vba procedure

This is a discussion on Pass value from userform textbox to vba procedure within the Excel Questions forums, part of the Question Forums category; I have a userform where the user will input a number into a textbox and that value should be passed ...

  1. #1
    Board Regular
    Join Date
    Nov 2008
    Posts
    365

    Default Pass value from userform textbox to vba procedure

    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)
    Thanks, Gail
    (Using Excel 2007)

  2. #2
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,401

    Default Re: Pass value from userform textbox to vba procedure

    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?

  3. #3
    Board Regular
    Join Date
    Nov 2008
    Posts
    365

    Default Re: Pass value from userform textbox to vba procedure

    Hi Colin,

    Quote Originally Posted by Colin Legg View Post
    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
    Thanks, Gail
    (Using Excel 2007)

  4. #4
    MrExcel MVP
    Like totally RAD man
    Colin Legg's Avatar
    Join Date
    Feb 2008
    Location
    UK
    Posts
    3,401

    Default Re: Pass value from userform textbox to vba procedure

    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?

  5. #5
    Board Regular
    Join Date
    Nov 2008
    Posts
    365

    Default Re: Pass value from userform textbox to vba procedure

    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
    Thanks, Gail
    (Using Excel 2007)

  6. #6
    Board Regular
    Join Date
    Feb 2011
    Location
    Brampton, Ontario
    Posts
    496

    Default Re: Pass value from userform textbox to vba procedure

    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

  7. #7
    MrExcel MVP
    Join Date
    Apr 2006
    Posts
    15,460

    Default Re: Pass value from userform textbox to vba procedure

    Quote Originally Posted by FryGirl View Post
    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.
    Kind regards
    PGC

    To understand recursion, you must understand recursion.

  8. #8
    Board Regular
    Join Date
    Feb 2011
    Location
    Brampton, Ontario
    Posts
    496

    Default Re: Pass value from userform textbox to vba procedure

    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

  9. #9
    Board Regular
    Join Date
    Nov 2008
    Posts
    365

    Default Re: Pass value from userform textbox to vba procedure

    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
    Thanks, Gail
    (Using Excel 2007)

  10. #10
    Board Regular
    Join Date
    Feb 2011
    Location
    Brampton, Ontario
    Posts
    496

    Default Re: Pass value from userform textbox to vba procedure

    Good luck

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com