change event not triggering macro

[cmccabe]

I have a macro that when run reformats data and a dropdown box is put into cell A2. Th user then selects the case number and this change is supposed to trigger another macro (calculations), but it does not. If I manually run calculations I get a result, but can not get the change_event to work.

Here are the codes I have tried:
VB

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
    ' if A2 has changed then call the Calculations sub
        Call Calculations(Range("A2").Value)
    End If
End Sub

VB

 Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target.Range("A2").Value
    Case "Calculations"
        Call Calculations
    End Select
End Sub

The way it is supposed to work is the user selects case 64 from the dropdown and the vlookup value is "Comprehensive Epilepsy". This change in A2 triggers the calculations macro (the formula is not working as expected either, but that is another issue). Thank you.

[daffodil11]

You don't need to state the target range twice. Try this:

 Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A2")) Is Nothing Then
    ' if A2 has changed then call the Calculations sub
        Call Calculations
    End If
End Sub

[cmccabe]

I changed the code to that with the same result. I can manually call calculations but the change event does not seem to work. Thank you.

[daffodil11]

I've used a very similar procedure in the past, including the user of a dropdown menu. Maybe try just using a simplified targeting, instead of intersect:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$G$2" Then Call SuperFilter

End Sub

Sub SuperFilter()

Application.ScreenUpdating = False

Sheets("Report - Room").Range("A5:J164").ClearContents
Sheets("Filter").Range("A4:I200").ClearContents

Sheets("Master INFO").Range("A1:I36").AdvancedFilter _
    Action:=xlFilterCopy, _
    CriteriaRange:=Sheets("Filter").Range("A1:I2"), _
    CopyToRange:=Sheets("Filter").Range("A4"), _
    Unique:=False
        
    Dim LR As Long, i As Long, n As Long

        With Sheets("Filter")
            LR = .Range("B" & .Rows.Count).End(xlUp).Row
    
            n = 5
    
            For i = 5 To LR
                .Range("B" & i).Copy
                Sheets("Report - Room").Activate
                Sheets("Report - Room").Range("A" & n & ":" & "A" & n + 3).Select
                Sheets("Report - Room").Paste
                
                .Range("C" & i).Copy
                Sheets("Report - Room").Activate
                Sheets("Report - Room").Range("B" & n & ":" & "B" & n + 3).Select
                Sheets("Report - Room").Paste
                
                .Range("D" & i).Copy
                Sheets("Report - Room").Activate
                Sheets("Report - Room").Range("C" & n & ":" & "C" & n + 3).Select
                Sheets("Report - Room").Paste
                
                n = n + 4
                
            Next i
        End With

Application.ScreenUpdating = True
        
End Sub

[cmccabe]

I attached a copy of the workbook. I changed the code and put a breakpoint in on the first line of Sub Calculations and it turned red.

The change in A2 produces a vlookup value. That value has a select case forumula that is supposed to run. In the attached if you run the classify macro and then in the dropdown in A2 select case 1, the vlookup will be Comprehensive Epilepsy. The Calculations sub is suppose to run automatically but currently I manual go in and run it. Thank you.

[daffodil11]

Really had to go line by line and test all the expression in the immediate window, but I think I found the issue.

Case 1 is working as intended, but Case 2 has a tiny little typo.

Const myMarfan As String = "=IF(SUMPRODUCT(--(Panel!$B$25610:$B$29333=$Q$5),--(Panel!$C$25610:$C$29333<=$R$5),--(Panel!$D$25610:$D$D29333>$R$5)),VLOOKUP($R$5,Panel!$C$25610:$E$29333,3,1),""No"")"

[cmccabe]

The change event doesnt seem to be working (triggering calculations) with either of the two codes, but I can run calculations manually. Thanks.

[daffodil11]

I'm not sure what's supposed to be happening, other than the autopopulation of formulas in AQ.

Every time I clear column AQ, change A2, and go back the VLOOKUP formulas have reappeared. Is something else supposed to be happening?

[Speshul]

Change the code to Public, instead of Private?

I just did something on Worksheet_Change and it worked when I changed it to Public. Also I noticed the Worksheet_Change wouldn't work in a separate sub, had to be in the Sheet. I could be missing something though, I'm bad at VBA.

[daffodil11]

I was even able to force Case 2 to go off as well, by changing CG to Marfan Disorder, and changing A2 again.

Sure, a lot of AQ turns into errors because the sample dataset doesn't support Case 2 but the formulas are correct for Case 2's output.

[cmccabe]

Here is the change event:
VB

 Option Explicit

Public Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$2" Then Call Calculations

End Sub

I think maybe the problem is calculations doesnt exist on the sheet, rater in PERSONNAL.XLSB!.

Does it need to be called a different way? Your description of what should be happening is correct. Thanks.

[daffodil11]

Ahhh, the missing information. If the macro you're calling doesn't even exist in the same workbook as the change event you'll have to call it an entirely different way.

Googled: Excel vb call macro from another workbook

Result:

Application.Run ("PERSONALL.xlsm!Calculations")

This will only work if the workbook you're calling it from is open as well.


Applied:

 Option Explicit

Public Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$2" Then Application.Run ("PERSONALL.xlsm!Calculations")

End Sub

[cmccabe]

I moved the Calculations macro into the this workbook and the change_event is on the annovar sheet (where the A2 change happens): but I have to run calculations manually to get the values. Thanks.

VB

Option Explicit

Public Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$A$2" Then Call Calculations

End Sub

[cmccabe]

It is working now, I had the Personal macro open, once I hide that everything is fine. Thank you for your help .