VBA I need some help.
-
The problem I am having is I need to enter values in a text box on a form and transfer them to an Excel sheet. I need to be able to input up to 25 "scores" For example I submit 77 then click submit it runs the following code For i = 1 To 25 'count = count + 1 'score = txtscore.Text 'Sheet5.Cells(i, "a").Value = score 'Next i The problem is it enters score for all values A1:A25 I need the score to be entered in A1 then input another number into txtscore which is my text box. Then after I click the submit button I need that value to be placed in A2 and so on and so forth. Any suggestions? Thanks
I can only open the door... I can't walk you through it.
-
The problem I am having is I need to enter values in a text box on a form and transfer them to an Excel sheet. I need to be able to input up to 25 "scores" For example I submit 77 then click submit it runs the following code For i = 1 To 25 'count = count + 1 'score = txtscore.Text 'Sheet5.Cells(i, "a").Value = score 'Next i The problem is it enters score for all values A1:A25 I need the score to be entered in A1 then input another number into txtscore which is my text box. Then after I click the submit button I need that value to be placed in A2 and so on and so forth. Any suggestions? Thanks
I can only open the door... I can't walk you through it.
I don't know VBA, but
J-c-K wrote:
'Sheet5.Cells(i, "a").Value = score
it seems clear to me that when i is 1, it will go into A1, when it's 2, it will go in to A2, etc.
Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
-
I don't know VBA, but
J-c-K wrote:
'Sheet5.Cells(i, "a").Value = score
it seems clear to me that when i is 1, it will go into A1, when it's 2, it will go in to A2, etc.
Christian Graus - Microsoft MVP - C++ "also I don't think "TranslateOneToTwoBillion OneHundredAndFortySevenMillion FourHundredAndEightyThreeThousand SixHundredAndFortySeven()" is a very good choice for a function name" - SpacixOne ( offering help to someone who really needed it ) ( spaces added for the benefit of people running at < 1280x1024 )
Thanks. It does go into A2 and so. However, I need the value in the text box to go into A1. Then I need the value/variable cleared then once another value is entered in the text box I want that value in A2 then cleared then A3 etc. Private Sub CommandButton1_Click() 'txtscore.Text = "" For i = 1 To 25 ' hours = Cells(i, "b").Value ' rate = Cells(i, "c").Value count = count + 1 score = txtscore.Text Sheet5.Cells(i, "a").Value = score txtcount = Sheet5.Range("B2") txtmean = Sheet5.Range("B3") txtstand = Sheet5.Range("B4") Next i End Sub
I can only open the door... I can't walk you through it.
-
Thanks. It does go into A2 and so. However, I need the value in the text box to go into A1. Then I need the value/variable cleared then once another value is entered in the text box I want that value in A2 then cleared then A3 etc. Private Sub CommandButton1_Click() 'txtscore.Text = "" For i = 1 To 25 ' hours = Cells(i, "b").Value ' rate = Cells(i, "c").Value count = count + 1 score = txtscore.Text Sheet5.Cells(i, "a").Value = score txtcount = Sheet5.Range("B2") txtmean = Sheet5.Range("B3") txtstand = Sheet5.Range("B4") Next i End Sub
I can only open the door... I can't walk you through it.
You're not going to use any kind of For/Next loop at all in this. You simply keep track of the row you're in in a simple integer variable. Every time you click Submit, or whatever you're calling your button, you put the textbox values in the cells, then increment the variable to point it at the next row. Check the variable to see if it's gone too far and adjust as you see fit. Then, you do nothing... The whole process starts over again on the next button click.
A guide to posting questions on CodeProject[^]
Dave Kreskowiak Microsoft MVP Visual Developer - Visual Basic
2006, 2007