Avatar of xrayroom
xrayroom asked on

Excel VB for loop

If I have some comboBox's and textbox's etc and I want to click a CommandButton and display the values from the various comboBox's and textbox's in certain cells, for example, I want the value of ComboBox1 to display in A20 and the value of textbox1 to display in B20. I can do this without a problem, however, if I want to click the same CmmandButton except this time it will display the values in A21 and B21 and the next time in A22 and B22 etc, i can not do this.
Visual Basic ClassicMicrosoft Excel

Avatar of undefined
Last Comment

8/22/2022 - Mon


Please upload your workbook.


You could use code in the Click event of the ComboBoxes or TextBoxes.
For example, for ComboBox1:
If cell A20 is empty, it will put the value of ComboBox1 there, otherwise, it will put it in the next empty cell below it. I have assumed that there is nothing below cell A20 for this.

Private Sub ComboBox1_Click()
    If Sheet1.Range("A20").Value = "" Then
        Sheet1.Range("A20").Value = Me.ComboBox1.Value
        Sheet1.Range("A65536").End(xlUp).Offset(1, 0).Value = Me.ComboBox1.Value
    End If
End Sub

Open in new window


Ideally I would like to use a loop and a variable for the row number that increments by 1 everytime the CommandButton is clicked and it is reset by another CommandButton
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question

That works perfectly, thanks