+ Reply to Thread
Results 1 to 19 of 19

getting data from command button in userform why is my code not working?

  1. #1
    Registered User
    Join Date
    06-02-2013
    Location
    south africa
    MS-Off Ver
    Excel 2010
    Posts
    18

    getting data from command button in userform why is my code not working?

    Public Sub ymstoredata()
    q = 1
    Range("a2") = Me.TextBox1.Value
    Range("b2") = Me.TextBox2.Value

    End Sub

    Private Sub CommandButton1_Click()

    End Sub

    Public Sub UserForm_Click()
    Do While q = 1
    q = 1
    ymstoredata
    qstn = InputBox(prompt:="Add new data?")
    If qstn = y Then
    ymstoredata
    Else
    q = 2
    Unload UserForm1

    End If

    Loop
    End Sub

    The code is suposed, to take the values from the form and store it in the sheet and then loop through and ask the user if it wants to add another record and if question )qstn = y (yes) then loop and get new data to store in sheet
    above code is not working ,can an expert please explain to me why it doesnt work and what i must do to make it work?
    Thank you very much i greatly appreaciate your help!!!!!!!!!!!
    also i will need simple code to add a new record underneath the previous one

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,643
    You don't seem to have declared any variables.
    If posting code please use code tags, see here.

  3. #3
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: getting data from command button in userform why is my code not working?

    Your code is on UserForm_Click, while the CommandButton1_Click is blank. So when you click on the command button, it tries to run code, finds that there's nothing to run, so it doesn't run anything. Move the code from the UserForm_Click to the CommandButton_Click
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    06-02-2013
    Location
    south africa
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: getting data from command button in userform why is my code not working?

    heres the new program below according to the help provided so far, yet it doesnt work! yet can someone please help?
    The program simply gets info from the text box and should loop through when the user wants to add new records
    the code doesnt work why?
    Thank you for your help

    code Public Sub ymstoredata()
    Dim q As Integer
    Dim qstn As String
    q = 1
    Do While q = 1
    Range("a2") = Me.TextBox1.Value
    Range("b2") = Me.TextBox2.Value

    qstn = InputBox(prompt:="Add more data?")
    If qstn = y Then
    'let program loop to begining of loop and show textbox again
    q = 1
    Else
    q = 2
    Unload UserForm1

    End If

    Loop
    End Sub /code

  5. #5
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: getting data from command button in userform why is my code not working?

    You moved the code from the UserForm_Click into your custom sub ymstoredata, you need to move it to the CommandButton1_Click.

  6. #6
    Registered User
    Join Date
    06-02-2013
    Location
    south africa
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: getting data from command button in userform why is my code not working?

    i have made the adjustments, thank you and now i get an invalid outside procedure on qtest why?? thanks for the help
    code Public CommandButton1_Click()

    Dim qtest As Integer
    Dim qstn As String
    Const qtest = 1

    Do While qtest = 1
    q = 1
    Range("a2") = Me.TextBox1.Value
    Range("b2") = Me.TextBox2.Value

    qstn = InputBox(prompt:="Add more data?")
    If qstn = y Then
    'let program loop to begining of loop and show textbox again
    qtest = 1
    Else
    qtest = 2
    Unload UserForm1

    End If

    Loop
    End Sub/code

  7. #7
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: getting data from command button in userform why is my code not working?

    Remove the Const

  8. #8
    Registered User
    Join Date
    06-02-2013
    Location
    south africa
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: getting data from command button in userform why is my code not working?

    ok then how else can i asign the value 1 to qtest when not using const ?

  9. #9
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: getting data from command button in userform why is my code not working?

    You don't need the Const at all. Just delete that word. qtest = 1 will assign the value just fine.

  10. #10
    Registered User
    Join Date
    06-02-2013
    Location
    south africa
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: getting data from command button in userform why is my code not working?

    invalid outside procedure <-- i get that message when i just code qtest = 1 and remove the const

  11. #11
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: getting data from command button in userform why is my code not working?

    that means its not in a subroutine, like CommandButton1_Click

  12. #12
    Registered User
    Join Date
    06-02-2013
    Location
    south africa
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: getting data from command button in userform why is my code not working?

    Thank you for the information, could you please advice to what must i must do now??
    heres the revised little program
    code Public CommandButton1_Click()

    Dim qtest As Integer
    Dim qstn As String
    qtest = 1
    Do While qtest = 1
    qtest = 1
    Range("a2") = Me.TextBox1.Value
    Range("b2") = Me.TextBox2.Value

    qstn = InputBox(prompt:="Add more data?")
    If qstn = y Then
    'let program loop to begining of loop and show textbox again
    qtest = 1
    Else
    qtest = 2
    Unload UserForm1

    End If

    Loop
    End Sub /code

  13. #13
    Registered User
    Join Date
    06-02-2013
    Location
    south africa
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: getting data from command button in userform why is my code not working?

    heres the latest code, the program is finaly running again, yet it now loops at <add new data> and doesnt show the textbox again to enter new data
    i also need the coding to add new records underneath each other and obviously not using direct cell adress names
    thank you so much for your help!
    the program now display the text box and ask if you want o ad another record and works fine when i say no and when i say yes
    it displayes again "want to add another record" and then it never gives me the textbox again to display the data
    why is that? and how can we fix it o work like i want it?

    codePublic Sub CommandButton1_Click()

    Dim qtest As Integer
    Dim qstn As String
    qtest = 1
    Do While qtest = 1
    qtest = 1
    Range("a2") = Me.TextBox1.Value
    Range("b2") = Me.TextBox2.Value

    qstn = InputBox(prompt:="Add more data?")
    If qstn = "y" Then
    'let program loop to begining of loop and show textbox again
    qtest = 1
    Else
    qtest = 2
    Unload UserForm1

    End If

    Loop
    End Sub /code

  14. #14
    Registered User
    Join Date
    06-02-2013
    Location
    south africa
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: getting data from command button in userform why is my code not working?

    all the coding is done in the form module an explanatory solution will be greatly appreciated thank you

  15. #15
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: getting data from command button in userform why is my code not working?

    Curious2,

    Attached is an example workbook based on the criteria you've described.
    The workbook contains a userform that has two text fields, an Add Data button and a Cancel button. Here is the full userform code:
    Please Login or Register  to view this content.
    Try to adapt that into your userform, it should accomplish what you're looking for.
    Attached Files Attached Files

  16. #16
    Registered User
    Join Date
    06-02-2013
    Location
    south africa
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: getting data from command button in userform why is my code not working?

    Thank you very much for your help. Your coding is very proffesional. Perfect! you are a very skilled VB programmer!! Thank you very much for taking the time to help me, i sincerely appreciate it, if i can ever return a favour, dont hesitate to ask.. Im new at this forum and should probably go read the forum rules too! it was just urgent for me to fix this problem thank you again, very much!!

  17. #17
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: getting data from command button in userform why is my code not working?

    You're very welcome

    If that takes care of your need, please mark this thread as solved.
    How to mark a thread Solved
    New quick method:
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

    Or you can use this way:
    Go to the first post
    Click edit
    Click Go Advanced
    Just below the word "Title" you will see a dropdown with the words "No prefix"
    Change to "Solved"
    Click Save

  18. #18
    Registered User
    Join Date
    06-02-2013
    Location
    south africa
    MS-Off Ver
    Excel 2010
    Posts
    18

    Re: getting data from command button in userform why is my code not working?

    Thank you i figured out how the code works, however could you explain this part to me?
    code Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(Me.txtHeaderA.Text, Me.txtHeaderB.Text,me.combobox.text)/code
    and can i do this, to add more items for example a combobox, will it work if i add it to the array? what and how must i change to adda third column that accept the input of the combo?
    Thanks again for your help!!

  19. #19
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: getting data from command button in userform why is my code not working?

    The number in the Resize is how many items will be displayed from the array, so just change the Resize(, 2) from a 2 to a 3. Then, like you've already done, put the Me.Combobox1.Text in the Array().

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1