Userform to Sheet Row\Columns

[Evapar18]

I am battling to figure out how to make this easier. I need to take the data from a multi-page of "Comboboxs" and "Textboxs" to a specific sheet. I have code that works, but its going to be a lot of work and I have 2 tabs I have to do this for.

Private Sub CommandButton2_Click()

Worksheets("BOM").Activate
Range("B2:D77").ClearContents
Range("G2").Value = TextBox222

If TextBox222 = "" Then
   MsgBox ("Missing Total Engines, please enter the total QTY of engines.")
   
ElseIf MultiPage1.Value = 0 Then

With Me
'Oil Filter
    'Part number
    Range("B2").Value = TextBox111
    'Descrip
    Range("C2").Value = ComboBox14 & " Oil Filter"
    'Code
    Range("D2").Value = TextBox115
  
'Alternator
    Range("B3").Value = TextBox141
    Range("C3").Value = "Alternator " & ComboBox18
    Range("D3").Value = TextBox140

End With

End If

End Sub

Here is the Userform (D18 Stage V pg1 & 2) and Sheet I am trying to paste it into: https://www.screencast.com/t/soKEa9ZlSfu

 

[DanteAmor]

You can create a procedure and execute it every time you require it. You must pass as parameters: the sheet, the range, the multimpage number, the cells, textbox and combobox.

Private Sub CommandButton2_Click()
  [COLOR=#0000ff]Call Process[/COLOR]([COLOR=#b22222]"BOM"[/COLOR], [COLOR=#daa520]"B2:D77[/COLOR]", [COLOR=#ff0000]0[/COLOR], [COLOR=#0000cd]Array("G2", "B2", "C2", "D2", "B3", "C3", "D3")[/COLOR], _
    A[COLOR=#008000]rray(textbox222.Value, TextBox111.Value, ComboBox14.Value & " Oil Filter", _[/COLOR]
[COLOR=#008000]    TextBox115.Value, TextBox141.Value, "Alternator " & ComboBox18.Value, TextBox140.Value)[/COLOR])
End Sub
    
Sub [COLOR=#0000ff]Process[/COLOR]([COLOR=#b22222]sh[/COLOR], [COLOR=#daa520]rng[/COLOR], [COLOR=#ff0000]mPage[/COLOR], [COLOR=#0000cd]cells[/COLOR], [COLOR=#008000]texts[/COLOR])
  Worksheets(sh).Activate
  Range(rng).ClearContents
  If texts(0) = "" Then
     MsgBox ("Missing Total Engines, please enter the total QTY of engines.")
  ElseIf MultiPage1.Value = mPage Then
    For i = 0 To UBound(cells)
      Range(cells(i)) = texts(i)
    Next
  End If
End Sub

 

[Evapar18]

Thank you for your reply!!

What do you mean by "must pass parameters", are you saying I need to define the specifics for the sheet, the range, the multimpage number, the cells, textbox and combobox?

 

[DanteAmor]

Let's do it differently, put all your code and I help you simplify it.

 

[Evapar18]

Thank you!
Here is the code from my command button. Hopefully I can repeat what your doing for the other 4 tabs that I still have to setup.

Private Sub CommandButton2_Click()Worksheets("BOM").Activate
Range("B2:D77").ClearContents
Range("O2").Value = TextBox222

If TextBox222 = "" Then
   MsgBox ("Missing Total Engines, please enter the total QTY of engines.")
   
ElseIf MultiPage1.Value = 1 Then

With Me

'Oil Filter
    'Part number
    Range("B2").Value = TextBox111
    'Descrip
    Range("C2").Value = ComboBox14 & " Oil Filter"
    'Code
    Range("D2").Value = TextBox115
  
'Alternator
    Range("B3").Value = TextBox141
    Range("C3").Value = "Alternator " & ComboBox18
    Range("D3").Value = TextBox140


'Crankshaft Pulley
    Range("B4").Value = TextBox146
    Range("C4").Value = ComboBox19
    Range("D4").Value = TextBox145


'PTO Assy
    Range("B5").Value = TextBox148
    Range("C5").Value = ComboBox20
    Range("D5").Value = TextBox147


'DOC + DPF
    Range("B6").Value = TextBox156
    Range("C6").Value = ComboBox21
    Range("D6").Value = TextBox155


'Additional Crankshaft Pulley
    Range("B7").Value = TextBox158
    Range("C7").Value = ComboBox22
    Range("D7").Value = TextBox157


'Additional Fuel Filter\Pump
    Range("B8").Value = TextBox172
    Range("C8").Value = ComboBox23
    Range("D8").Value = TextBox171


'Oil Gauge
    Range("B9").Value = TextBox112
    Range("C9").Value = TextBox173
    Range("D9").Value = TextBox116
'Oil Tube
    Range("B10").Value = TextBox113
    Range("C10").Value = TextBox114
    Range("D10").Value = TextBox117


'PTO Cover
    Range("B11").Value = TextBox150
    Range("C11").Value = TextBox151
    Range("D11").Value = TextBox149


'Fuel Filter
    Range("B12").Value = TextBox154
    Range("C12").Value = TextBox152
    Range("D12").Value = TextBox153


'Air Filter
    Range("B13").Value = TextBox124
    Range("C13").Value = "Air Filter " & ComboBox17
    Range("D13").Value = TextBox123


'Bracket\Hoses
    Range("B14").Value = TextBox126
    Range("C14").Value = TextBox125
    Range("D14").Value = TextBox127


    Range("B15").Value = TextBox129
    Range("C15").Value = TextBox128
    Range("D15").Value = TextBox130
    
    Range("B16").Value = TextBox132
    Range("C16").Value = TextBox131
    Range("D16").Value = TextBox133


    Range("B17").Value = TextBox135
    Range("C17").Value = TextBox134
    Range("D17").Value = TextBox136


    Range("B18").Value = TextBox144
    Range("C18").Value = TextBox142
    Range("D18").Value = TextBox143
    
'Starter
    Range("B19").Value = TextBox138
    Range("C19").Value = TextBox139
    Range("D19").Value = TextBox137


'Belt Drive System
    Range("B20").Value = TextBox161
    Range("C20").Value = TextBox160
    Range("D20").Value = TextBox159


    Range("B21").Value = TextBox163
    Range("C21").Value = TextBox162
    Range("D21").Value = TextBox164
    
    Range("B22").Value = TextBox167
    Range("C22").Value = TextBox166
    Range("D22").Value = TextBox165


    Range("B23").Value = TextBox169
    Range("C23").Value = TextBox168
    Range("D23").Value = TextBox170


'Multi-Page Tab 2 D18 Stage V
'Radiator
    Range("B24").Value = TextBox175
    Range("C24").Value = "Radiator " & ComboBox24
    Range("D24").Value = TextBox174
    
'Radiator Hoses and Pipes
    Range("B25").Value = TextBox180
    Range("C25").Value = TextBox179
    Range("D25").Value = TextBox181
    
    Range("B26").Value = TextBox183
    Range("C26").Value = TextBox182
    Range("D26").Value = TextBox184
    
    Range("B27").Value = TextBox186
    Range("C27").Value = TextBox185
    Range("D27").Value = TextBox187
    
    Range("B28").Value = TextBox189
    Range("C28").Value = TextBox188
    Range("D28").Value = TextBox178
    
    Range("B29").Value = TextBox193
    Range("C29").Value = TextBox190
    Range("D29").Value = TextBox194
    
    Range("B29").Value = TextBox192
    Range("C29").Value = TextBox195
    Range("D29").Value = TextBox191


'Cooling Fan
    Range("B30").Value = TextBox177
    Range("C30").Value = ComboBox25 & " Fan"
    Range("D30").Value = TextBox176
    
'Flywheel4
    Range("B31").Value = TextBox197
    Range("C31").Value = ComboBox26 & " Flywheel"
    Range("D31").Value = TextBox198
    
'Flywheel Housing
    Range("B32").Value = TextBox200
    Range("C32").Value = TextBox201
    Range("D32").Value = TextBox199
    
'Spacer
    Range("B33").Value = TextBox203
    Range("C33").Value = TextBox204
    Range("D33").Value = TextBox202
    
'Pilot Bearing
    Range("B34").Value = TextBox218
    Range("C34").Value = TextBox219
    Range("D34").Value = TextBox217


'Front Legs
    Range("B35").Value = TextBox205
    Range("C35").Value = TextBox206
    Range("D35").Value = TextBox207
    
    Range("B36").Value = TextBox208
    Range("C36").Value = TextBox209
    Range("D36").Value = TextBox210


'Rear Legs
    Range("B37").Value = TextBox211
    Range("C37").Value = TextBox212
    Range("D37").Value = TextBox213


    Range("B38").Value = TextBox214
    Range("C38").Value = TextBox215
    Range("D38").Value = TextBox216


'Crossbar Bracket
    Range("B39").Value = TextBox221
    Range("C39").Value = TextBox196
    Range("D39").Value = TextBox220
    
'Engine Part number
    Range("O10").Value = Textbox230
    Range("P10").Value = Standard & " " & Engine_Liter
    Range("Q10").Value = "-"
    
    
End With


End If
End Sub

 

[DanteAmor]

Thanks for sharing your code,
You can put the code of another of the 4 tabs, to find a pattern and be able to work with that.