Copy data from userform to a sheet multiple times as per combobox value

dss28

Board Regular
Joined
Sep 3, 2020
Messages
165
Office Version
  1. 2007
Platform
  1. Windows
i have a userform in which i want to copy textbox1, 2, 3 data multiple times as per the combobox1 values to sheet1 as follows:

combobox1 value = 1 - copy textbox1 values in cell C3 , copy textbox2 values in cell C4 , copy textbox3 values in cell C5
combobox1 value = 2 - copy textbox1 values in cell C3 , copy textbox2 values in cell C4 , copy textbox3 values in cell C5
- copy textbox1 values in cell H3 , copy textbox2 values in cell H4 , copy textbox3 values in cell H5
combobox1 value = 3 - copy textbox1 values in cell C3 , copy textbox2 values in cell C4 , copy textbox3 values in cell C5
- copy textbox1 values in cell H3 , copy textbox2 values in cell H4 , copy textbox3 values in cell H5
- copy textbox1 values in cell M3 , copy textbox2 values in cell M4 , copy textbox3 values in cell M5

can some one suggest a code
thanks in advance.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
VBA Code:
Sub Combobox1_Change()
    If combobox1.Value < 1 Then Exit Sub
    
    Cells(3, 3) = Textbox1
    Cells(4, 3) = Textbox2
    Cells(5, 3) = Textbox3
    Select Case combobox1.Value
        Case 2
            Cells(3, 8) = Textbox1
            Cells(4, 8) = Textbox2
            Cells(5, 8) = Textbox3
        Case 3
            Cells(3, 8) = Textbox1
            Cells(4, 8) = Textbox2
            Cells(5, 8) = Textbox3
            Cells(3, 13) = Textbox1
            Cells(4, 13) = Textbox2
            Cells(5, 13) = Textbox3
    End Select
End Sub
 
Upvote 0
Another option
VBA Code:
Private Sub CommandButton2_Click()
   Dim i As Long
   If Me.ComboBox1.Value = "" Then Exit Sub
   
   For i = 1 To Me.ComboBox1.Value
      With Sheets("Sheet1").Range("C3:C5").Offset(, (i - 1) * 5)
         .Value = Application.Transpose(Array(Me.TextBox1, Me.TextBox2, Me.TextBox3))
      End With
   Next i
End Sub
 
Upvote 0
Here is how I would do it:
VBA Code:
Private Sub ComboBox1_Change()
'Modified  3/19/2021  8:55:34 AM  EDT
Dim ans As Long
Dim i As Long
ans = ComboBox1.Value
    For i = 3 To ans * 5 Step 5
        Cells(3, i).Value = TextBox1.Value
        Cells(4, i).Value = TextBox2.Value
        Cells(5, i).Value = TextBox3.Value
    Next
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top