ByVal Target as Range looking at a formula

[ElBB23]

Hi all,

I am using the below code which is working perfectly if the value is typed into the target address. However, if possible I want to change the target cell to a formula that pulls through from another sheet.

So target address is N2 which is a formula "=sheet1!B2"

Private Sub Worksheet_Change(ByVal target As Range)
Application.ScreenUpdating = False



'5
    
    If target.Address = "$N$2" Then
    If Range("N2").Value = "5" Then
    Columns("P:JM").EntireColumn.Hidden = False
    Union(Range( _
    "BH:BH,BB:BB,AU:AU,AO:AO,AH:AH,AB:AB,U:U,JH:JH,JB:JB,IU:IU,IO:IO,IH:IH,IB:IB,HU:HU,HO:HO,HH:HH,HB:HB,GU:GU,GO:GO,GH:GH,GB:GB,FU:FU,FO:FO,FH:FH,FB:FB,EU:EU,EO:EO,EH:EH,EB:EB,DU:DU,DO:DO,DH:DH" _
    ), Range("DB:DB,CU:CU,CO:CO,CH:CH,CB:CB,BU:BU,BO:BO")).Select
    Selection.EntireColumn.Hidden = True
    
    Range("P7").Select
    
'4
ElseIf Range("N2").Value = "4" Then
    msg = "Are you sure
    ans = MsgBox(msg, vbYesNo)
    If ans = vbYes Then
    
    Range("JC7:JE207,JI7:JK207").ClearContents
    
    Columns("P:IZ").EntireColumn.Hidden = False
    Columns("JC:JM").EntireColumn.Hidden = True
    Union(Range( _
    "BH:BH,BB:BB,AU:AU,AO:AO,AH:AH,AB:AB,U:U,IU:IU,IO:IO,IH:IH,IB:IB,HU:HU,HO:HO,HH:HH,HB:HB,GU:GU,GO:GO,GH:GH,GB:GB,FU:FU,FO:FO,FH:FH,FB:FB,EU:EU,EO:EO,EH:EH,EB:EB,DU:DU,DO:DO,DH:DH" _
    ), Range("DB:DB,CU:CU,CO:CO,CH:CH,CB:CB,BU:BU,BO:BO")).Select
    Selection.EntireColumn.Hidden = True
    
    Range("IP7").Select
End If

This code repeats itself a number of times until it reaches cell value 0. I have been searching online for a while but am struggling, I do not think I will be able to transfer the code into sheet one (as I have seen in some examples) as I have three sheets that all do exactly the same thing above which, if I can find a way around the code not recognising the formula will all point to a certain cell on sheet 1.


thanks in advance for any help.

 

[dmt32]

Hi,
As you are aware, with Worksheet_Change event where a cell contains a formula, this is not included in the Target argument of the event & does not trigger when formula value changes.
To test if a cell containing a formula has been changed, a couple of possible options to consider, one is to use the Calculation event (but this has no Target argument) where you can call the change event (or another procedure) but this involves a bit of trickery like this

Private Sub Worksheet_Calculate()
    With Me.Range("N2")
    If .Value <> .ID Then
        .ID = .Value
        Application.EnableEvents = False
        Call Worksheet_Change(Me.Range("N2"))
        Application.EnableEvents = True
    End If
    End With
End Sub

By using the ID property of the cell as a placeholder, this ensures that the event is only called when the specified cell has changed.

Note: If you use this method & the change event is only to be called by your formula change then consider moving code to standard module & renaming it.


OR, you can test for the precedent cells of the formula cell within the Change Event.

There is a really good post about this here:https://www.ozgrid.com/forum/forum/...8728-firing-procedure-when-cell-value-changed

#post 3 - contributor goes on to provide a good explanation how this works.

Hope Helpful

Dave.

 

[ElBB23]

Hi dmt32,

sorry for the late reply on this, have only just managed to log back in.

Thanks for the reply, the solution worked perfectly

 

[dmt32]

Hi dmt32,

sorry for the late reply on this, have only just managed to log back in.

Thanks for the reply, the solution worked perfectly

No worries glad solution helped

many thanks for feedback

Dave