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

 

[silverfang]

@Joe4

I'm trying to hide/unhide rows based on the cell values from selected list in another sheet. My scenario : I have 2 sheets FSSAI and Checklist.
From FSSAI , i need to select from drop down list from cells D13 , F13 and D15, then once these values are selected , they go to Checklist's D3 , D4 and D5 respectively.
As they are going to D3,D4 and D5 , the Rows between (9:38) should be keep on hiding the way. Any combination of D3,D4 and D5 should yield in what i intend to do hide.
And when i select "--", all the fields should be un-hidden.

The above code was to run the formula in cells in CheckList sheet so that they take in values from the drop down list.

See both the images of sheet 1(FSSAI ) and Sheets 2(Checklist)

 

[Joe4]

You didn't answer the second question I had:

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

Namely, I need to know the name of the VB module you have placed the Worksheet_Calculate code in.

 

[silverfang]

You didn't answer the second question I had:

Namely, I need to know the name of the VB module you have placed the Worksheet_Calculate code in.

Oops sorry.
The Procedures are in the checklist(Sheet2).
The code as well the worksheet_calculate() event both are in sheet's "view Code" and not in a module.

 

[Joe4]

i need to select from drop down list from cells D13 , F13 and D15, then once these values are selected

Are D13, F13, and D15 on the "FSSAI WS" worksheet manually updated (by someone manually selecting an option from the drop-down list)?
If so, then use a Worksheet_Change event procedure on the "FSSAI WS" sheet instead, i.e.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Only run when cell F13 is updated
    If Target.Address(0, 0) = "F13" Then
    
        Select Case Target.Value
            Case Is = "Manufacturer": Sheets("Checklist").Rows("10:14").EntireRow.Hidden = True
            Case Is = "Trader": Sheets("Checklist").Rows("16:18").EntireRow.Hidden = True
            Case Is = "Relabeller & Repackers": Sheets("Checklist").Rows("20:25").EntireRow.Hidden = True
            Case Is = "--": Sheets("Checklist").Rows("10:30").EntireRow.Hidden = False
        End Select
        
    End If
    
End Sub