VBA Worksheet_Calculate() Code doesn't run until it is run from VB Editor

[silverfang]

I have a working code, but it doesn't hide until i Run the code from VB Editor :

Maybe those D3,D4 and D5 are formulated , that's why it is not running on its own :
The above code isto run the formula in cells in CheckList sheet so that they take in values from the drop down list.

This code hides only one option from the drop down Sheet "FSSAI WS", but then other ones are not responding until and unless i press the RUN option in VB Editor.

Can someone please help me out......!!!

Sub Formula_Property()

Range("D3").Formula = "='FSSAI WS'!D13"
Range("D4").Formula = "='FSSAI WS'!F13"
Range("D5").Formula = "='FSSAI WS'!D15"

End Sub

------------------------------------------------------------------------------------------------------------------------
Private Sub Worksheet_Calculate()

'Application.ScreenUpdating = False
Application.EnableEvents = False


Dim business As Range
Set business = Sheets("Checklist").Range("D4")

Select Case business
        Case Is = "Manufacturer": Rows("10:14").EntireRow.Hidden = True
        Case Is = "Trader": Rows("16:18").EntireRow.Hidden = True
        Case Is = "Relabeller & Repackers": Rows("20:25").EntireRow.Hidden = True
        Case Is = "--": Rows("10:30").EntireRow.Hidden = False

'Application.ScreenUpdating = True
Application.EnableEvents = True

End Select
End Sub

 

[Logit]

Have you tried it using :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

or

Private Sub Worksheet_Change(ByVal Target As Range)

 

[Joe4]

Two things to consider:

1. This line of code here:

Application.EnableEvents = False

temporarily disables events while the code is being run, so the code doesn't make changes, call itself, and get caught in an infinite loop.
You turn it back on at the end with this line:

Application.EnableEvents = True

However, if in the process of working on it, it shuts it off, and then gets interrupted, and never gets to the line at the end to turn it back on, it won;t work automatically for you. You need to turn it back on. You can do that by manually running this:

Sub TurnEventsOn()
    Application.EnableEvents = True
End Sub

2. This runs when a calculation is done on your sheet. However, if you have calculations set to manual, then it won't run until you manually run a Calculation.

 

[silverfang]

Yes Sir, i tried with them both.
Those don't change a thing at all.
Since

Maybe those D4 is formulated , that's why it is not running on its own.
My code was running until yesterday, today idk(bad luck i suppose).

 

[Joe4]

My code was running until yesterday, today idk(bad luck i suppose).

So that begs the question, what did you change on it?

 

[silverfang]

@Joe4 - Sir, there was no error in my code until yesterday. today i have tried all combinations and still nothing comes up correctly.

 

[silverfang]

So that begs the question, what did you change on it?

Change - I just closed the excel sheet and when i finally tried showing it to my senior, it was a flop show.

 

[Logit]

" My code was running until yesterday, "

Confused ... did you change the formulas or are things still the way they were a few days ago ?
If things remain the same ... something else is going on than your code.

????

 

[silverfang]

" My code was running until yesterday, "

Confused ... did you change the formulas or are things still the way they were a few days ago ?
If things remain the same ... something else is going on than your code.

????

I haven't changed a bit of the code.

 

[Joe4]

What is your intention, of which the code should run?

The two procedures you posted in your first post, which modules are those found in?