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

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
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.

VBA Code:
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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

diddi

Well-known Member
Joined
May 20, 2004
Messages
3,301
Office Version
  1. 2010
Platform
  1. Windows
assuming your combobox is RepeatCombo...

VBA Code:
RWS = Val(RepeatCombo.Value)
otherwise you can reference the combobox by the .listindex which tells you the row selected
 

vhdhfox

New Member
Joined
Aug 6, 2020
Messages
36
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,928
Messages
5,639,041
Members
417,066
Latest member
rhenman

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