command inside userform, need help with code

curious2 · June 4th, 2013, 09:02 PM

i created a userform with 2 textboxes that stores data in sheet
and then i want the program to loop so that user can add new data t o the sheet i also want the range to change to the next row and add a new row everytime the user adds a new record
right now the code loops and keep on asking to add a new record and the text box only displayes the first time now the add new record program loops all the time why is that and how can it be fixed? thank you very much for your help!!!!!!!!

the following code is inside userform1
Public 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

Shasur · June 5th, 2013, 07:52 AM

Hi

You can try something like

Code:

 
 
Do
 
'your statements
 
Range("a2") = Me.TextBox1.Value
Range("b2") = Me.TextBox2.Value
 
Loop Until (MsgBox("Wish To Continue", vbYesNo) = vbNo)

curious2 · June 5th, 2013, 04:24 PM

Thank you for being willing to help me i greatly appreciate it!! yes your code is much better!
However the code doesnt work right, it loops without asking me for input
can i
code if vbyesno = vbno
then unload form1
endif /code is the syntax right, yet it doesnt make a difference? what is wrong here?

Shasur · June 6th, 2013, 08:22 AM

Hi

I had tried the following . Hope it helps

Code:

 
Private Sub CommandButton1_Click()
Do
ActiveSheet.Range("E1").Value = TextBox1.Text
If (MsgBox("Wish to Enter more value", vbYesNo) = vbNo) Then Unload Me
b1 = 1
Loop Until b1 = 1
End Sub

Cheers
Shasur