nested if else statment not working via target cell

Question

Hi I have a private sub (worksheet by change) which will run one of 26 macro's based on a cells value. All these do is hide or unhide specific row's based on the value from a cell which has a formula in it.

Now when I manually change the value of the cell the worksheet change event and macro's all work as they are meant to, BUT they don't work when the cell value is change via the formula, or rather bits of them do.

I have set the worksheet, and range variables in each macro, and likewise the target cell within the worksheet change event properly (dim r as range, etc.)

The code for the worksheet change event is below - wondered if anyone can spot or help as to why this doesn't seem to work when running without the manual cell value change

If I can avoid it I don't want to programmatically change the value in the cell (if "a condition" then r.value="A value" 26 times!)

here is the worksheet code

Dim r As Range, r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range, r6 As Range, r7 As Range, r8 As Range
Dim r9 As Range, r10 As Range, r11 As Range, r12 As Range, r13 As Range, r14 As Range, r15 As Range, r16 As Range


Private Sub Worksheet_Change(ByVal Target As Range)    

'Set range for selecting the region
Set r = Range("O19")

'Set facility ranges

Set r1 = Rows("22:24"): Set r2 = Rows("25:27"): Set r3 = Rows("28:30"): Set r4 = Rows("31:33")

'set product line ranges
Set r5 = Rows("37"): Set r6 = Rows("38"): Set r7 = Rows("39"): Set r8 = Rows("40"): Set r9 = Rows("41"): Set r10 = Rows("42"): Set r11 = Rows("43:45")
Set r12 = Rows("46:48"): Set r13 = Rows("49"): Set r14 = Rows("51:52"): Set r15 = Rows("36"): Set r16 = Rows("50")

'Hiding facility Rows based on product line

If r.Value = 1 Then ' Select Facility & all cells hidden
    Application.Run ("Select_Facility")
    ElseIf r.Value = 2 Then ' This is for North America & no Facility
    Application.Run ("NA_NoFacility")
    ElseIf r.Value = 3 Then ' This is for Breen Only
    Application.Run ("Breen")
    ElseIf r.Value = 4 Then ' This is for Conroe Only
    Application.Run ("Conroe")
    ElseIf r.Value = 5 Then ' This is for Lafayette Only
    Application.Run ("Lafayette")
    ElseIf r.Value = 6 Then ' This is for Breen & Conroe Only
    Application.Run ("Breen_Conroe")
    ElseIf r.Value = 7 Then ' This is for Breen & Lafayette Only
    Application.Run ("Breen_Lafayette")
    ElseIf r.Value = 8 Then ' This is for Conroe & Lafayette
    Application.Run ("Conroe_Lafayette")
    ElseIf r.Value = 9 Then ' This is for All North America
    Application.Run ("All_NA")
    ElseIf r.Value = 10 Then ' This is for Europe and no facility
    Application.Run ("Europe_NoFacility")
    ElseIf r.Value = 11 Then 'This is for Gateshead only
    Application.Run ("Gateshead")
    ElseIf r.Value = 12 Then 'This is for Kintore only
    Application.Run ("Kintore ")
    ElseIf r.Value = 13 Then 'This is for Kintore & Gateshead only
    Application.Run ("All_Europe")
    ElseIf r.Value = 14 Then ' This is for Middle East and no facility
    Application.Run ("Europe_NoFacility")
    ElseIf r.Value = 15 Then 'This is for Dubai only
    Application.Run ("Dubai")
    ElseIf r.Value = 16 Then 'This is for Saudi only
    Application.Run ("Saudi")
    ElseIf r.Value = 17 Then 'This is for Dubai and Saudi only
    Application.Run ("Dubai_Saudi")
    ElseIf r.Value = 18 Then ' This is for Far East & no Facility
    Application.Run ("FE_NoFacility")
    ElseIf r.Value = 19 Then ' This is for Loyang Only
    Application.Run ("Loyang")
    ElseIf r.Value = 20 Then ' This is for Tuas Only
    Application.Run ("Tuas")
    ElseIf r.Value = 21 Then ' This is for Perth Only
    Application.Run ("Perth")
    ElseIf r.Value = 22 Then ' This is for Loyang & Tuas Only
    Application.Run ("Loyang_Tuas")
    ElseIf r.Value = 23 Then ' This is for Loyang & Perth Only
    Application.Run ("Loyang_Perth")
    ElseIf r.Value = 24 Then ' This is for Tuas and Perth Only
    Application.Run ("Tuas_Perth")
    ElseIf r.Value = 25 Then ' This is for All far East facilities
    Application.Run ("All_FE")
    ElseIf r.Value = 26 Then ' This is for Global
    Application.Run ("All_Global")
End If

The user could select a number of combinations of facilities which give me 26 variants - hence the 26 options above - originally the hide row's instructions were inside each ElseIF option but its wouldn't work there either.

The way the form works is the user selects the region, and then will select the relevant facilities - this will change the target cell twice, and also amend the hidden and visible rows. from which the user will select yes or no.

This event does not occur when cells change during a recalculation — BrakNicku
Possible duplicate of VBA code doesn't run when cell is changed by a formula — Sam

Dominique Dominique · Accepted Answer · 2019-03-04T12:53:18

First, you need to work with Worksheet_Calculate() event, as already mentioned by Sam. But there's something more: why are you using a 26-conditions if-clause?

Create a next worksheet (Application_Sheet), with two columns, something like this:

R_Value      Application
      1   Select_Facility
      2       No_Facility
    ...               ...

Instead of the complicated if-clause, do something like this (not tested):

Application.Run(Range(Application_Sheet!B1).Offset(r.Value).Value)

nested if else statment not working via target cell

2 Answers