Worksheet_Change function will not work!

Godfather

New Member
Joined
Jul 2, 2007
Messages
7
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!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

BrianB

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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 :-
Code:
Private Sub Worksheet_Calculate()
    MsgBox ("Calculated")
    ' etc
End Sub
 

Forum statistics

Threads
1,181,416
Messages
5,929,790
Members
436,693
Latest member
BroTr

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top