VBA Help deleting contents of a cell on worksheet change event

[ASanders]

Any help much appreciated. I have a spreadsheet and a worksheet change event which checks Column B and show's a message box when either "Personal" or "Corporate" is entered in the cell. It uses an OldValue variable to ensure the macro doesnt fire when the change is occurring to a currently blank cell.

What I also need the macro to do is to also delete the contents of columns D, E and F in the row corresponding to the cell whenever "Personal" or "Corporate" is entered in the cell. Code below.

Public OldValue As String
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("B2:BF" & Rows.Count)) Is Nothing Then Exit Sub

With Application
On Error GoTo ErrHandler
.EnableEvents = False
.Undo
OldValue = Target.Cells(1).Value
.Undo
.EnableEvents = True
ActiveCell.Offset(0, 1).Select

End With

With Target
If OldValue = "Personal" Then MsgBox "Please note products available are specific to either Personal or Corporate applications."
If OldValue = "Corporate" Then MsgBox "Please note products available are specific to either Personal or Corporate applications."

End With

ErrHandler:
Application.EnableEvents = True
If Err.Number <> 0 Then
MsgBox "Error: " & Err.Number & vbNewLine & Err.Description
End If
End Sub

 

[ASanders]

Thanks Tom.

Say for instance I had another column 7 with separate validation (a list of countries) which i wanted to fire the change event for also, deleting cells alongside in column 8 and 9. Is it simply a matter of extending the "If .Column <> 2 Or .Cells.Count > 1 Then Exit Sub" to incorporate "...Or If .Column <> 7..." into the code and putting in another 'Case'? Would I need to list every possible country value in the second Case code Tom?

 

[Tom Urtis]

First, with the code I posted that you said works, this line got in there and should not have, so please dlete it:
If Intersect(Target, Range("B2:BF" & Rows.Count)) Is Nothing Then Exit Sub

Next, I would structure the extra scenario with column G to be such that on some dedicated sheet which you could hide, list all countries in column A, name that list, and refer to the name as the data validation List formula. Maybe you already did that.

Let's say you named the list "Countries" on a dedicated sheet named Sheet3, again, in column A. Here is how I would restructure the Change event to handle it all.

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
If .Column <> 2 And .Column <> 7 Then Exit Sub
If .Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
 
Dim OldVal$, NewVal$
NewVal = .Value
 
With Application
.EnableEvents = False
.Undo
OldVal = Target.Value
Target.Value = NewVal
.EnableEvents = True
End With
 
.Offset(0, 1).Activate
 
If .Column = 2 Then
Select Case OldVal
Case "Personal", "Corporate"
MsgBox "Please note products available are specific to either Personal or Corporate applications.", , "FYI"
Range(Cells(.Row, 4), Cells(.Row, 6)).ClearContents
End Select
 
ElseIf .Column = 7 Then
Dim varFind As Variant
varFind = Application.Match(OldVal, Worksheets("Sheet1").Columns(1), 0)
If IsError(varFind) = True Then
Err.Clear
Range(Cells(.Row, 8), Cells(.Row, 9)).ClearContents
End If
 
End If
 
End With
End Sub

 

[ASanders]

Thanks Tom. Really really apreciate your help on this. Am learning more and more each time you post. OK almost there. From what I read of the 'Match' formula, if I validate using the country field using a named range, Countries in Sheet 3, will I ever get an error. I.e. I have data validation on the cell referencing a range named "Countries" in worksheet 3. If a user changes the existing cell value from say Australia to New Zealand (both valid entries on the named range), from what I understand thats ok so the 'isError (varFind)' code won't fire will it? Also in the code you pasted, where you had

varFind = Application.Match(OldVal, Worksheets("Sheet1").Columns(1), 0)

, did you mean

varFind = Application.Match(OldVal, Worksheets("Sheet3").Columns(1), 0)