Selected Answer
Mussa
CommandButton5 acts on the values selected/ entered on your UserForm so I have replaced your code:
Private Sub CommandButton5_Click()
Dim i As Long
Dim sheetname As String
For i = 1 To 4
With Me.Controls("OptionButton" & i)
If .Value Then sheetname = .Caption: Exit For
End With
Next i
With Worksheets(sheetname)
'.Range("e5").MergeArea = Me.TextBox1.Value
'.Range("e8").MergeArea = Me.TextBox2.Value
For i = 1 To 11 'This is unmodified
'' Debug.Print "TextBox_" & i, Cells(i + 1, 1).Address(0, 0)
.Cells(i + 1, 1).Value = Me.Controls("TextBox" & i)
Next i
For i = 12 To 44
'' Debug.Print "TextBox_" & i, Range("F2").Offset((i - 12) Mod 11, Int((i - 12) / 11)).Address(0, 0)
.Range("F2").Offset((i - 12) Mod 11, Int((i - 12) / 11)).Value = Me.Controls("Textbox" & i)
Next i
For i = 2 To 45
'' Debug.Print "ComboBox_" & i, Range("B2").Offset((i - 2) Mod 11, Int((i - 2) / 11)).Address(0, 0)
.Range("B2").Offset((i - 2) Mod 11, Int((i - 2) / 11)).Value = Me.Controls("ComboBox" & i)
Next i
End With
TextBox15.Value = Val(Me.TextBox7.Value) * Val(Me.TextBox11.Value)
TextBox16.Value = Val(Me.TextBox8.Value) * Val(Me.TextBox12.Value)
TextBox17.Value = Val(Me.TextBox9.Value) * Val(Me.TextBox13.Value)
TextBox18.Value = Val(Me.TextBox10.Value) * Val(Me.TextBox14.Value)
End Sub
with this (using a single loop to write and non-blank rows to the selected sheet), some changes shown in bold:
Private Sub CommandButton5_Click()
Dim i As Long
Dim sheetname As String
Dim lRw As Long ' for Last Row
' count to 2 not 4 (since there are only two buttons at present)
For i = 1 To 2
With Me.Controls("OptionButton" & i)
If .Value Then sheetname = .Caption: Exit For
End With
Next i
' check a option was selected
If sheetname = "" Then
MsgBox "Please select an option button first"
Exit Sub
End If
With Worksheets(sheetname)
' loop down UserForm controls
For i = 1 To 11
' see if a Code was set
If Me.Controls("ComboBox" & i + 1).Text <> "" Then
'if Code set, find last row in B
lRw = .Cells(Rows.Count, 2).End(xlUp).Row
' then write values
.Cells(lRw + 1, 1).Value = Me.Controls("TextBox" & i)
.Cells(lRw + 1, 2).Value = Me.Controls("ComboBox" & i + 1)
.Cells(lRw + 1, 3).Value = Me.Controls("ComboBox" & i + 12)
.Cells(lRw + 1, 4).Value = Me.Controls("ComboBox" & i + 23)
.Cells(lRw + 1, 5).Value = Me.Controls("ComboBox" & i + 34)
.Cells(lRw + 1, 6).Value = Me.Controls("TextBox" & i + 11)
.Cells(lRw + 1, 7).Value = Me.Controls("TextBox" & i + 22)
.Cells(lRw + 1, 8).Value = Me.Controls("TextBox" & i + 33)
' clear Qty and Total controls (at least)
Me.Controls("TextBox" & i + 11) = ""
Me.Controls("TextBox" & i + 33) = ""
Else
' do nothing
End If
Next i
End With
End Sub
Note that I tested for blank rows on the UserForm using the Code control since, in my testing, I saw that your form did not always set a value for Item (I guess you're yet to replicate the code for the first few rows).
You'll see that it writes all values below the last used row on the chosen sheet (but you get a warning if you don't select one via an OptionButton). The Qty, Price and Total values are copied (if you added Qty and Price to the form). After the data is copied, the Qty and Price controls are set to nothing (to indicate that something happened).
Hope this is what you wanted.
