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

dss28

Board Regular
Joined
Sep 3, 2020
Messages
67
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

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,327
Office Version
  1. 2010
Platform
  1. Windows
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
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
58,246
Office Version
  1. 365
Platform
  1. Windows
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
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,651
Office Version
  1. 2013
Platform
  1. Windows
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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,911
Messages
5,655,907
Members
418,250
Latest member
Jebacmakro

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
Top