Code:
Private Sub CommandButton1_Click()
For Each ctl In Frame1.Controls
If TypeName(ctl) = "OptionButton" Then
If ctl.Value = False Then x = x + 1
End If
Next
If x = 3 Then MsgBox "Select a machine before you write data": Exit Sub
For Each ctl In Frame2.Controls
If TypeName(ctl) = "OptionButton" Then
If ctl.Value = False Then y = y + 1
End If
Next
If y = 2 Then MsgBox "Select Internal or External before you write data": Exit Sub
'check user input
For Each ctl In Me.Controls
If ctl.Tag <> vbNullString And ctl.Enabled Then
If ctl.Value = vbNullString Then MsgBox ctl.Tag: Exit Sub
End If
Next
For i = 1 To 3
If Me("OptionButton" & i) Then whatsheet = Me("OptionButton" & i).Caption
Next
'write data to worksheet
With Sheets(whatsheet)
.Unprotect Password:="abc"
.Cells(.Cells(.Rows.Count, "B").End(xlUp).Row + 1, 2).Resize(, 17) = Array(ComboBox1.Value, , , , , , , , _
ComboBox2.Value, ComboBox3.Value, ComboBox4.Value, TextBox2.Text, IIf(OptionButton4, TextBox1.Text, ""), _
ComboBox5.Value, ComboBox6.Value, TextBox3.Text, IIf(OptionButton5, TextBox1.Text, ""))
.Protect Password:="abc"
End With
'Clear all fields
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then ctl.Value = vbNullString
If TypeName(ctl) = "OptionButton" Then ctl.Value = False
Next
For i = 2 To 6
Me("Combobox" & i).Enabled = False
If i = 2 Or i = 3 Then Me("Textbox" & i).Enabled = False
Next
End Sub
Bookmarks