VBA Code to Clear/Blank Value in a Cell with Formula in Another Cell

[wcm69]

I’m hoping someone can help me with what I hope is a simple VBA code to clear/blank the contents of a cell if the input from a (“Yes “or “No”) drop down list = “No”.

I have an Excel Table with (“Yes” or “No”) drop down lists in Cell Ranges J2:J50. I have a pop up calendar (to enter the desired dates) in Cell Ranges K2:K50. I would like if the dates entered into Cells K2:K50 are automatically cleared or blanked when the drop down lists (in Cells J2:J50) option “No” is chosen.

I’ve tried various Nested If() statements in the validation Box and directly in the cell in the worksheet with No success. Finally after some investigation it appears you cannot delete a value in a cell with a formula in another cell.

It seems the answer lies in VBA. Unfortunately I have little knowledge of coding, so I’m hoping someone can provide the necessary directions and codes that I can copy and paste directly into my worksheet/module.

My thanks in advance for any help.

 

[My Aswer Is This]

Try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("J2:J50")) Is Nothing Then
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Value = "No" Then Target.Offset(0, 1).Value = ""
End If
End Sub

 

[wcm69]

Many thanks for the code.

But it comes back with a compile error - 'Ambiguous name detected: Worksheet_Change' when I Debug before I run it.

I followed your instructions and pasted it into the worksheet. Any idea where I'm going wrong?

Your help and patience is greatly appreciated.

 

[wcm69]

Hooray!

I just managed to figure out where I was going wrong. I already had a similar change event in the worksheet which I've added it to, and it now clears the data in cells K2:K50 - Many thanks.

Just one little thing - with this code I'm having to click back onto the Cell (J2:J50) which contains the word "No" (after it's been picked from the drop down list) in order for Cell K2:K50 to clear - rather than clearing instantly/automatically when the "No" option is chosen from the list.

I wondered if there's a little tweak in the code that can achieve this. I'd rather keep going forward to the next cell than having to go back to clear (as I suppose I could just use the delete button to clear the cell if so). Not the biggest thing in the world, but if a tweak is possible it would make the mission complete.

As usual, any help and/or advice is greatly appreciated.

Thanks in advance.

 

[My Aswer Is This]

The code works perfect for me without having to click back into the cell.

Post all the code you have in your sheet here so I can look at it. I'm sure it's because of other code in your sheet and the way you have it all layed out.

 

[wcm69]

Thank you for your continued help, advice and patience with me on this.

The full VB Worksheet code I’m using to blank the cells with dates in my table is:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Application.Intersect(Range("D2:D50, K2:K50"), Target) Is Nothing And Range("J" & Target.Row) <> "No" Then

frmCalendar.Show
End If

If Not Intersect(Target, Range("J2:J50")) Is Nothing Then

If Target.Value = "No" Then Target.Offset(0, 1).Value = ""
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub

End If
End Sub

Note: I’m using the first set of codes to clear the (non-required) dates in Cells: L2:L50 – These cells adds (+)365 days to the dates entered in Cells: K2:K50. The formula for this in Cell: L2:L50 is:

=IF(ISBLANK(K3),"",IF(J3="No","",IF(J3="Yes",K3+364, IF(J3="","",IF(K3="","","")))))

Cells: J2:J50 – Have the “Yes or No” Drop down list, which I want to use to blank or delete the (date) data in Cells K2:K50 and Cells: L2:L50 when the user picks “No” from the drop down list.

Cells: K2:K50 - Have no formulas as this is where the pop up calendar appears and allows the user to enter the desired date.

Hopefully the issue is an obvious one, unfortunately I don’t have the experience in VB to see it.

Thanks in advance.

 

[My Aswer Is This]

You said in your first post:
I would like if the dates entered into Cells K2:K50 are automatically cleared or blanked when the drop down lists (in Cells J2:J50) option “No” is chosen.

But in your second post you said:

Cells: J2:J50 – Have the “Yes or No” Drop down list, which I want to use to blank or delete the (date) data in Cells K2:K50 and Cells: L2:L50 when the user picks “No” from the drop down list.

So is that the problem. The script is clearing one range but not the other. And sheet change events only activate when a manual change is made to a cell. Not sure about when a pop-up calendar makes a change to a cell.

 

[wcm69]

That's right.

At the moment entering "No" from the drop down lists in Cells: J2:J50 automatically clears/blanks data Cells: L2:L50 but not in Cells: K2:K50 unless I tab back onto Cell: J2:J50 (containing the drop down lists) with "No" entered into the cell at which point it then clears Cells: K2:K50.

Can you see where I'm going wrong.

Many thanks

 

[My Aswer Is This]

I'm sorry someone else here at Mr. Excel may be able to help you.

You have several different things going on here in the same sheet and several sheet change events and I get confused easily.

 

[wcm69]

No problem,

As I said previously - it's not the most important thing. The help you've given is great appreciated.

Thanks again!!