How to empty row sheet depending on combobox value

[N323100]

Good Day,

I have this userform that if the user select Status (ComboBox4)
"Pending - Team Meeting"
"Pending - 1st Break"
"Pending - Lunch Break"

then the Row 7 and 10 should be empty. here is my code that I'm trying to add under submit button:

If Me.ComboBox4.Value = "Pending - Team Meeting" Then
  .Offset(RowCounter, 7).Value = ""
  .Offset(RowCounter, 10).Value = ""
  End If

and here are the code for submit button:

Private Sub CommandButton4_Click()
If VERIFY_ENTRY = False Then Exit Sub
Dim RowCounter  As Long
Dim rowCount  As Long
Dim ctrl  As Control
Dim Score  As Double
Dim num As String

RowCounter = 0
Score = 1

For Each ctrl In Me.Controls
  Select Case TypeName(ctrl)
  Case Is = "CheckBox"
  If Me.Controls(ctrl.Name).Value = True Then Score = Score - GETSCORE(Me.Controls(ctrl.Name).Name)
  End Select
Next ctrl
Me.TextBox6.Value = Format(Score, "Percent")

If MsgBox("Submit RFP results?", vbQuestion + vbYesNo, "") = vbNo Then GoTo endmacro


rowCount = Worksheets("Quality Database").Range("A1").CurrentRegion.Rows.Count
With Worksheets("Quality Database").Range("A" & rowCount + 1)
  'Form to Database
  .Offset(RowCounter, 0).Value = Now()
  .Offset(RowCounter, 1).Value = Me.TextBox2.Value
  .Offset(RowCounter, 2).Value = Me.ComboBox1.Value
  .Offset(RowCounter, 3).Value = Me.ComboBox2.Value
  .Offset(RowCounter, 4).Value = Me.ComboBox3.Value
  .Offset(RowCounter, 6).Value = "Excel to Word"
  
  .Offset(RowCounter, 9).Value = Me.ComboBox4.Value
  
  .Offset(RowCounter, 10).Value = Round(Score * 100, 2)
  .Offset(RowCounter, 11).Value = Format(Me.TextBox3.Value, "hh:mm:ss")  'Start Time
  .Offset(RowCounter, 12).Value = Format(Me.TextBox4.Value, "hh:mm:ss")  'End Time
  .Offset(RowCounter, 13).Value = Format(Me.TextBox5.Value, "hh:mm:ss")  'Time Spent
  .Offset(RowCounter, 13).NumberFormat = "hh:mm:ss"
  
  
  
  ' Attributes Target
  For Each ctrl In Me.Controls
  Select Case TypeName(ctrl)
  Case Is = "CheckBox"
  If Me.Controls(ctrl.Name).Value = True Then
  
  If Me.Controls(ctrl.Name).Caption <> "Other" Then
  .Offset(RowCounter, 7).Value = vbCrLf & Me.Controls(ctrl.Name).Caption
  RowCounter = RowCounter + 1
  
  Else
  num = Mid(ctrl.Name, 9)
  .Offset(RowCounter, 7).Value = vbCrLf & Me.Controls(ctrl.Name).Caption
  .Offset(RowCounter, 8).Value = Me.Controls("Textbox" & num).Value
  RowCounter = RowCounter + 1
  End If
  End If
  End Select
  Next ctrl
  
  If RowCounter = 0 Then .Offset(RowCounter, 7).Value = "Everything was Completed Satisfactory!"
End With
'MessageBox
 MsgBox "Data added", vbOKOnly + vbInformation, ""
endmacro:
'Clear Data
INIT_FORM
Me.TextBox2.SetFocus

'save workbook
ActiveWorkbook.Save
End Sub

Thank you

 

[N323100]

My only problem here is, I wasn't able to empty the sheet for RowCounter, 7

 

[N323100]

Resolved! Thank you!

 

[Derek McGill]

Resolved! Thank you!

For the forum can you post your solution ?

 

[N323100]

For the forum can you post your solution ?

here is the updated code

For Each ctrl In Me.Controls
  Select Case TypeName(ctrl)
  Case Is = "CheckBox"
  If Me.Controls(ctrl.Name).Value = True Then
 
  If Me.Controls(ctrl.Name).Caption <> "Other" Then
  .Offset(RowCounter, 7).Value = vbCrLf & Me.Controls(ctrl.Name).Caption
  RowCounter = RowCounter + 1
 
  Else
  num = Mid(ctrl.Name, 9)
  .Offset(RowCounter, 7).Value = vbCrLf & Me.Controls(ctrl.Name).Caption
  .Offset(RowCounter, 8).Value = Me.Controls("Textbox" & num).Value
  RowCounter = RowCounter + 1
  End If
  End If
  End Select
  Next ctrl
 
  If RowCounter = 0 Then .Offset(RowCounter, 7).Value = "Everything was Completed Satisfactory!"
 
  If Me.ComboBox4.Value = "Pending - Team Meeting" Then
  .Offset(RowCounter, 7).Value = ""
  .Offset(RowCounter, 10).Value = ""
  End If
  If Me.ComboBox4.Value = "Pending - 1st Break" Then
  .Offset(RowCounter, 7).Value = ""
  .Offset(RowCounter, 10).Value = ""
  End If
  If Me.ComboBox4.Value = "Pending - Lunch Break" Then
  .Offset(RowCounter, 7).Value = ""
  .Offset(RowCounter, 10).Value = ""
  End If
  If Me.ComboBox4.Value = "Pending - 2nd Break" Then
  .Offset(RowCounter, 7).Value = ""
  .Offset(RowCounter, 10).Value = ""
  End If
  If Me.ComboBox4.Value = "Pending - Coaching" Then
  .Offset(RowCounter, 7).Value = ""
  .Offset(RowCounter, 10).Value = ""
  End If
End With

Thank you

 

[NoSparks]

Interesting....
you write to offset(rowcounter, 7) using a loop that increments rowcounter each time, presumably to write to more rows,
and your resolution clears rowcounter only.

Am I missing something ?

 

[N323100]

Yes it should clear the RowCounter 7 depending on the value of combobox4.