Erasing cells when the validation dropdown choice changes

[Bob]

I have a validation dropdown box in cell D11. Cells D16 and D17 are
"regular" cells. Each time a user selects a different choice in D11, I need
to have the contents in D16 & D17 erased (i.e., revert to being blank cells).

Being relatively new to VBA, I have no idea how to program this. Can anyone
help me?

Thanks,
Bob

 

[Gord Dibben]

Can be done, but how do D16 and D17 get filled to start with?

Once cleared, what's to clear if another choice is made from the DV
dropdown?

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("D11")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Me.Range("D1617").ClearContents
endit:
Application.EnableEvents = True
End Sub

Right-click on the sheet tab and "View Code"

Copy/paste the code into that module.

Alt + q to return to Excel.


Gord Dibben MS Excel MVP

 

[Bob]

Gord,

Your code works exactly the way I want it to. Thanks!

To answer your questions:

Cell D11 = Change Type (e.g., Add, Drop, Move)
Cell D16 = Reason Code
Cell D17 = Explanation

D16 and D17 get filled in by manual entry AFTER a choice has been made in
D11. If a user changes their mind and goes back to D11 to select a different
choice, then D16 and D17 should be erased since the previously inputted
Reason Code and Explanation no longer apply to the newly selected choice.

One final question: if I want cell D21 erased as well, would I simply add
the following line:

Me.Range("D21").ClearContents

Thanks again for all your help.
Bob

 

[Gord Dibben]

Just add it into the existing range.

Me.Range("D1617,D21").ClearContents

Thanks for answering my questions. I thought it might be something like
that.


Gord

 

[Bob]

Gord,

Thanks again for your help. I sincerely appreciate it.

Regards,
Bob

 

[Bob]

Gord,

Please forgive me, but may I impose on you again?

In cell D5, the user inputs their full name. Is there a way to modify your
code below such that after the user has inputted their name and moved to a
another cell, the PROPER function can somehow be employed to change the
inputted name in cell D5 to Title Case format (i.e., john smith or JOHN SMITH
is automatically changed to John Smith)?

Thanks,
Bob

 

[Gord Dibben]

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Me.Range("D5")
If Intersect(Target, Me.Range("D11")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Me.Range("D1617,D21").ClearContents
rng.Formula = Application.Proper(rng.Formula)
endit:
Application.EnableEvents = True
End Sub


Gord