VBA to clear textbox if the value of another combobox meets a certain criteria

[d3stijl]

Hi,

I have built an excel user form which captures data.

I have a combobox called 'ClosedCB' with the options 'yes' and 'no'.

If the value in ClosedCB is 'no' I would like it to clear another textbox called 'ReviewTB'.

Here is my code so far...

Private Sub SaveBTN_Click()


If Me.SprintRecord_RowNumber <> "" Then
Call SaveRecord(Me.SprintRecord_RowNumber.Value)
Else
Call SaveRecord

End If


End Sub
Function SaveRecord(Optional iRow As Long)


Dim ws As Worksheet
Set ws = Worksheets("WorkLog")
Const ForReading = 1, ForWriting = 2
Dim fso, f

Racf = Environ("USERNAME")
Name1 = Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 5)

If NameCB.Value = "" Or SprintTB.Value = "" Or QCRefTB = "" Or ReviewDateTB = "" Then
MsgBox "You have not completed the required fields, please check and try again!", vbInformation + vbOKOnly, "Attention!"
Exit Function
Else

If
ClosedCB.Value = "Closed" Then DateRaisedTB.Value = ""

End If

If iRow = 0 Then
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row 'find first empty row on WS
End If

'inserts date into log
ws.Cells(iRow, 1).Value = "" & NameCB.Value
'inserts phone number into log
ws.Cells(iRow, 2).Value = "" & SprintTB.Value
'inserts Policy number into log
ws.Cells(iRow, 3).Value = "" & QCRefTB.Value
'inserts Campaign into log
ws.Cells(iRow, 4).Value = "" & DateRaisedTB.Value
'inserts Outcome into log
ws.Cells(iRow, 5).Value = "" & ReviewDateTB.Value
'inserts username into log
ws.Cells(iRow, 6).Value = "" & CheckBox1.Value
'inserts username into log
ws.Cells(iRow, 7).Value = "" & CheckBox2.Value
'inserts username into log
ws.Cells(iRow, 8).Value = "" & CheckBox3.Value
'inserts username into log
ws.Cells(iRow, 9).Value = "" & CheckBox4.Value
'inserts username into log
ws.Cells(iRow, 10).Value = "" & CheckBox5.Value
'inserts username into log
ws.Cells(iRow, 11).Value = "" & CheckBox6.Value
'inserts username into log
ws.Cells(iRow, 12).Value = "" & CheckBox7.Value
'inserts username into log
ws.Cells(iRow, 13).Value = "" & CheckBox8.Value
'inserts username into log
ws.Cells(iRow, 14).Value = "" & CheckBox9.Value
'inserts username into log
ws.Cells(iRow, 15).Value = "" & CheckBox10.Value
'inserts username into log
ws.Cells(iRow, 16).Value = "" & CheckBox11.Value
'inserts username into log
ws.Cells(iRow, 17).Value = "" & CheckBox12.Value
'inserts username into log
ws.Cells(iRow, 18).Value = "" & CheckBox13.Value
'inserts username into log
ws.Cells(iRow, 19).Value = "" & CheckBox14.Value
'inserts username into log
ws.Cells(iRow, 20).Value = "" & CheckBox15.Value
'inserts username into log
ws.Cells(iRow, 21).Value = "" & CheckBox16.Value
'inserts username into log
ws.Cells(iRow, 22).Value = "" & CheckBox17.Value
'inserts username into log
ws.Cells(iRow, 23).Value = "" & CheckBox18.Value
'inserts username into log
ws.Cells(iRow, 24).Value = "" & CheckBox19.Value
'Open Closed
ws.Cells(iRow, 25).Value = "" & Me.ClosedCB.Value
End If

'Saves the Workbook
ThisWorkbook.Save


MsgBox ("This Record has been logged.")
'hides the log call page

'Clears contents in Userform
Dim ctl As MSForms.Control


For Each ctl In Me.Controls
Select Case TypeName(ctl)
Case "TextBox"
ctl.Text = ""
Case "CheckBox", "OptionButton", "ToggleButton"
ctl.Value = False
Case "ComboBox", "ListBox"
ctl.ListIndex = -1
End Select
Next ctl


Me.Hide
MainMenu.Show

'brings back to main menu


End Function


Any help will be much appreciated