Worksheet_Change function will not work!

[Godfather]

I have seen this type of question before on this and other forums, but have yet to see a solution. I have some code that is supposed to clear the contents of a cell whenever another cell is changed. The cell that changes is a drop down data validation list. This code works fine in another workbook, but does nothing in the current one and I have done too much other work to start over now. However, if I type in one of the drop down selection instead of selecting it from the list or set the "calculation" to manual in the tool bar, the function works perfectly. But, if I have the function turn calculations to manual, it still does nothing. Why is this happening and how can I fix it? This is the code I am using.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$C$14" Then
ThisWorkbook.ActiveSheet.Range("C15").ClearContents
End If

Thank you for your help!

 

[BrianB]

Often we can solve problems like this by using the Worksheet_Calculate() event instead.

Changing the dropdown does not in itself force calculation, so we add =NOW() to a cell somewhere.

It may help to temporarily put a check on what you are getting for the cell address by putting MsgBox(Target.Address) line.

 

[xenou]

Hi Brian:
How often (or when) will now() force recalculation?

I have also seen this done with a formula outside the dropbox that references the dropbox...for instance, if your dropbox has a value in cell B2, then in cell C2 Formula is =B2&B2 ... and reference C2 in your calculate event...

Regards.

 

[BrianB]

How often (or when) will now() force recalculation?

As far as I know any time something is done in the sheet. This is mostly used with Data Validation when selecting a value does not run a Change event.

To see, try temporarily adding a messagebox :-

Private Sub Worksheet_Calculate()
    MsgBox ("Calculated")
    ' etc
End Sub