VBA/Userform to use combo boxes as criteria for repeating.

[vhdhfox]

Hi,

I'm creating a multiple userform worksheet that inputs data to the worksheet from the data input in to the userform.

when the data in entered in the userform there are 5 combo boxes with a list of names that will always be unique, I want all the data except for the names to repeat using the combo boxes as the criteria of how many times to repeat, column "F" is the column with the unique name values.

so far I've only been able to get the data to repeat using a textbox in the userform with a number of times to repeat.

Private Sub RunBtn_Click()
Dim lrow As Long, RWS As Long
Dim ws As Worksheet
Dim EntryForm As UserForm

Set ws = Worksheets("Planning")
lrow = ws.Cells(Rows.Count, "F").End(xlUp).Offset(1, 0).Row
        On Error Resume Next
                    If DateBox1.Value = "" Then
            MsgBox "Missing Date"
        Else
    With Worksheets("Planning")
        RWS = RepeatData.Value
        ws.Cells(lrow, 2).Resize(RWS).Value = DateBox1.Value
        ws.Cells(lrow, 3).Resize(RWS).Value = StoreBox1.Value
        ws.Cells(lrow, 4).Resize(RWS).Value = AgencyBox1.Value
        ws.Cells(lrow, 5).Resize.Value = NameBoxA1.Value
        ws.Cells(lrow + 1, 5).Resize.Value = NameBoxA2.Value
        ws.Cells(lrow + 2, 5).Resize.Value = NameBoxA3.Value
        ws.Cells(lrow + 3, 5).Resize.Value = NameBoxA4.Value
        ws.Cells(lrow + 4, 5).Resize.Value = NameBox1.Value
        ws.Cells(lrow, 6).Resize(RWS).Value = ContainerBox1.Value
        ws.Cells(lrow, 7).Resize(RWS).Value = TaskBox1.Value
        ws.Cells(lrow, 8).Resize(RWS).Value = ClientBox1.Value
        ws.Cells(lrow, 12).Resize(RWS).Value = StartBox1.Value
        ws.Cells(lrow, 13).Resize(RWS).Value = EndBox1.Value
        ws.Cells(lrow, 18).Resize.Value = "1"
        ws.Cells(lrow + 1, 18).Resize.Value = "2"
        ws.Cells(lrow + 2, 18).Resize.Value = "3"
        ws.Cells(lrow + 3, 18).Resize.Value = "4"
        ws.Cells(lrow + 4, 18).Resize.Value = "5"
        ws.Cells(lrow, 1).Resize.Value = C1ID.Value
       
       
    End With
    End If
End Sub

 

[diddi]

assuming your combobox is RepeatCombo...

RWS = Val(RepeatCombo.Value)

otherwise you can reference the combobox by the .listindex which tells you the row selected

 

[vhdhfox]

Hi Diddi,

Thanks for the response, by using the combo boxes as a reference I meant it as sort of a countif statement. if labourboxA1-A3 have a value and LabourBoxA4 has no value then count LabourBoxA1-A3 as repeat row input 3 times.
hopefully that makes sense if not ill try and explain a bit better.