VBA code to find and delete cell contents based on userform combobox selections

**anfdrew** · 07-05-2013, 11:49 PM

I have 3 Worksheets that are exactly the same in setup to record dates for different events(For this example you can call them sheet1, sheet2 and sheet3). I have a userform that has two comboboxs and a textbox. The first combobox (combobox1) allows the user to select from a list of names that is in column “a” on each work sheet. Combobox 2 allows the user to select the worksheet. The text box allows the user to enter a date.

What I need is a VBA code that when a user clicks on a delete button it selects the worksheet from combobox 2, finds the row which the name selected in combobox1 is on and finds the date in the textbox within that row and deletes that date only.

I do not have any idea how to set this code up. Please help

Thanks

**AlphaFrog** · 07-06-2013, 02:05 AM

Private Sub CommandButton1_Click()
    
    Dim r As Variant, c As Variant
    
    If Me.ComboBox2.ListIndex = -1 Then
        MsgBox "No sheet selected. ", , "Invalid Entry"
        Exit Sub
    ElseIf Me.ComboBox1.ListIndex = -1 Then
        MsgBox "No name selected. ", , "Invalid Entry"
        Exit Sub
    ElseIf Not IsDate(Me.TextBox1.Value) Then
        MsgBox "Invalid date. ", , "Invalid Entry"
        Exit Sub
    End If
    
    Sheets(Me.ComboBox2.Value).Select
    
    r = Application.Match(Me.ComboBox1.Value, Range("A:A"), 0)
    If IsError(r) Then
        MsgBox "No match for " & Me.ComboBox1.Value, , "No Name Match Found"
        Exit Sub
    End If
    
    c = Application.Match(CLng(CDate(Me.TextBox1.Value)), Rows(r), 0)
    If IsError(c) Then
        MsgBox "No match for date " & Me.TextBox1.Value, , "No Date Match Found"
        Exit Sub
    End If
    
    Cells(r, c).ClearContents
    
End Sub