0

I need help in a Userform to add multiple Rows on Sheet1. It looks like this (control names in red): Userform with control names in red

Here is my code:

Dim cNum As Integer
Dim X As Integer
Dim nextrow As Range
Dim rRef As String
Dim ctrl As MSForms.Control

Private Sub UserForm_Initialize()
For Each ctrl In Controls
If ctrl.Name Like "*rec4" Then ctrl = Date
If TypeOf ctrl Is MSForms.ComboBox Then
If ctrl.Name Like "*rec1" Then ctrl.List = Array("Company1", "Company2", "Company3")
If ctrl.Name Like "*rec2" Then ctrl.List = Array("Supplier1", "Supplier2", "Supplier3")
If ctrl.Name Like "*rec5" Then ctrl.List = Array("Wrong Order", "Wrong Company", "Wrong Charge","Internal Deletion")
End If
Next
End Sub

Private Sub Arec5_Change()
Select Case Arec5.Value
Case Is = "Wrong Order"
Arec6.List = Array("Linked to other supplier", "Not Valid", "Missing figure")
Case Is = "Wrong Company"
Arec6.List = Array("Company1", "Company2", "Company3")
Case Is = "Wrong Charge"
Arec6.List = Array("Recupel", "Bebat", "Valorlub")
Case Is = "Internal Deletion"
Arec6.List = Array("Already Handled", "Manual Handling", "Request", "Deleted order")
End Select
End Sub

Private Sub btnClear_Click()
Unload Me
UserForm1.Show
End Sub

Private Sub btnClose_Click()
Unload Me
End Sub

Private Sub btnSubmit_Click()
cNum = 7
rRef = "Arec"
Addme
rRef = "Brec"
Addme
rRef = "Crec"
Addme
rRef = "Drec"
Addme
rRef = "Erec"
Addme
rRef = "Frec"
Addme
rRef = "Grec"
Addme
rRef = "Hrec"
Addme
rRef = "Irec"
Addme
rRef = "Jrec"
Addme
rRef = "Lrec"
Addme
rRef = "Mrec"
Addme
rRef = "Nrec"
Addme
rRef = "Orec"
Addme
rRef = "Prec"
Addme
rRef = "Qrec"
Addme
rRef = "Rrec"
Addme
rRef = "Srec"
Addme
End Sub

Sub Addme()
Set nextrow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
For X = 1 To cNum
nextrow = Me.Controls(rRef & X).Value
Set nextrow = nextrow.Offset(0, 1)
Next
End Sub

What I want to do is:

Apply the same change event (Arec5_Change) to all *rec5 combobox so that each *rec6 combobox will be dependent on the *rec5 combobox on the Left.

How to achieve this?

What I also want to do next is to send row to Sheet2 when "Internal Deletion" is found in *rec5.

Is it possible ?

colidyre
  • 4,170
  • 12
  • 37
  • 53
Dmz
  • 1
  • 2
  • Do you have a fixed number of lines on your userform? If so, then you can just hard-code the event handlers manually. Or are you only using the fixed lines as an example, but with many more to be created at runtime? If this is the case, you'd need to research runtime control event handlers, which can be done using class modules. – Chris Melville Aug 28 '18 at 09:45
  • Hi Chris and thanks. I've 19 lines on my Userform (from Arec to Srec). I thought there'd be a faster way to achieve this task. – Dmz Aug 28 '18 at 09:54
  • 19 lines... ok, as I said. You have 2 options: 1) Manually code event handlers for all of them, or 2) Have a class module to handle the events, and assign all the controls to the class with a loop. It's up to you whether you want a quick fix (1) or to invest the time in researching and learning (2) :) – Chris Melville Aug 28 '18 at 10:03
  • Ok, I'll hardcode the 19 combobox for a quick fix but I really want to learn more about the class module. Where can I start from ? Any idea for my second question ? – Dmz Aug 28 '18 at 10:19
  • I have posted some links in an answer. Regarding your second question, what do you mean by "send row to Sheet2"? And what have you tried? I would suggest asking a second (separate) question for this query, only after you show the code that you have already tried, and why you are having a specific problem doing what you want. – Chris Melville Aug 28 '18 at 11:53

1 Answers1

0

As per the comments - you have stated that for now, you will hard-code the event handlers for 19 controls. However for future reference - and for learning more about VBA - it is possible to create a class event handler to assign to multiple control objects, including those created on a UserForm at runtime.

Check out the following links:

Chris Melville
  • 1,476
  • 1
  • 14
  • 30