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.
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,077
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,077
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
30,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
30,544
Office Version
365
Platform
Windows
Glad to help & thanks for the feedback
 

Forum statistics

Threads
1,078,461
Messages
5,340,440
Members
399,375
Latest member
alwayssunny

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top