Pls help how to Add multiple rows with same value of combobox through userform

Sany_s11

New Member
Joined
Nov 10, 2019
Messages
7
I have created a userform
Which have a combobox and a text box, save button.

Now when i select a value say"ABCD" in combo box, i will mention say number as "5".
Now when i click on save button, then 5 rows of value "ABCD" should be created in excel cells in the column i specify. And next time i select any other value or the same value it should repeat for the next available cell in the same column. and so on.

Please help with the code in VB to achieve this.
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,722
Office Version
2013
Platform
Windows
So you want to always enter the value 5 times?
If not how do you plan to tell the script how many times?

And you said:
excel cells in the column i specify

How do you plan to specify what column?
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,722
Office Version
2013
Platform
Windows
Assuming you want a Inputbox to ask how many times.
And assuming you want these values entered into column A

And assuming you plan to select the values from a list of values in the ComboBox1

Try this:

Put this script in the ComboBox

Code:
Private Sub ComboBox1_Change()
'Modified  11/10/2019  8:21:44 AM  EST
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 1
Dim ans As Long
ans = InputBox("How Many Times")
Cells(Lastrow, 1).Resize(ans).Value = ComboBox1.Value
End Sub
 

Sany_s11

New Member
Joined
Nov 10, 2019
Messages
7
Thank you, it did help by adding rows as expected. But i don`t want a new dialog box to pop up. I have both combobox and input box in the same user form. So i have added a text field below the combobox, after selecting the value in the combobox i will have to mention the number in the text box and once i click save button in the same user form the values should be populated in cells.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,544
Office Version
365
Platform
Windows
How about
Code:
Private Sub CommandButton1_Click()
    Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Me.TextBox1.Value).Value = Me.ComboBox1.Value
End Sub
 

Sany_s11

New Member
Joined
Nov 10, 2019
Messages
7
How about
Code:
Private Sub CommandButton1_Click()
    Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(Me.TextBox1.Value).Value = Me.ComboBox1.Value
End Sub

Superb, It worked.... Thanks a lot. I am a new learner may be need help for any more queries.. Thanks a lot
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
35,544
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Forum statistics

Threads
1,089,319
Messages
5,407,545
Members
403,152
Latest member
Junaid Azhar

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top