Thread: Clear Contents on Update when other code exists in the Worksheet_Change thingy

Hi everyone,

I have a data collection spreadsheet where Column H uses a data validation list to restrict the values a user can select. Column I then populates a drop down list based on the selection in column H using VB in the Private Sub Worksheet_Change(ByVal Target As Range) area. I would like Column I and Column J to clear contents when Column H is updated, but I can't figure out how to make that happen. I don't really know VB and have borrowed code to make the magic happen so far.

I've attached a workbook with what I've got so far as I'm generally terrible at describing what I'm trying to do. You may notice that the worksheet includes the same scenario with Columns K, L, & M too.

I think I need to add some code in the Private Sub Worksheet_Change(ByVal Target As Range) to .ClearContents or something like that, but I can't find the right spot to stick the code in and/or I am not using the correct command to clear contents only when Column H is updated.

Thanks for your help and sorry for not knowing all the right terminology!

perhaps this will help

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
Dim oldVal As String, newVal As String

On Error GoTo exitHandler
With Application
    If Intersect(Target, Cells.SpecialCells(xlCellTypeAllValidation)) Is Nothing Then Exit Sub
    .EnableEvents = False
    newVal = Target.Value: .Undo: oldVal = Target.Value
    Target.Value = newVal

    Select Case Target.Column
        Case 8, 11
            Target.Offset(, 1).Resize(, 2).ClearContents 'I would like Column I and Column J to clear contents when Column H is updated
        Case 9, 12
            If oldVal <> "" Then
                If newVal <> "" Then
                    If InStr(1, oldVal, newVal) > 0 Then
                        If Right(oldVal, Len(newVal)) = newVal Then
                            Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2)
                        Else
                            Target.Value = Replace(oldVal, newVal & ", ", "")
                        End If
                    Else
                        Target.Value = oldVal & ", " & newVal
                    End If
                End If
            End If
        Case 10, 13
            If oldVal <> "" Then
                If newVal <> "" Then Target.Value = oldVal & ", " & newVal
            End If
    End Select

exitHandler:
    .EnableEvents = True
End With
End Sub

Wow. 1) I am in awe, and 2) Thank You so very much. This is amazing.

In the interest of learning, I hope that you'll be game to answer a few newbie questions...

What does this mean and/or why does this work? newVal = Target.Value: .Undo: oldVal = Target.Value
I've never seen the use of colons after anything other than an "=" to declare a variable.

Also, why is it ok to use "case" in Excel but not in Access?

Thank you again!