Would like Target.Address to only run from one module

[ArnMan]

Hello,
I have several modules housing different code. Each Code runs in the same Range Area. I have one module that gives a place to put a value in J5. And from there is uses Worksheet_Change to run a separate Module. I've added the "If Target.address section" after a Select Case setup. This works the way it is. But if I put another module that gives a value, or a user inputs a Value into J5 the Worksheet_Change fires off the Target address and runs the original Module that it was designed for. Giving the result I am looking for something else.
I am sure I've left something out, if I have I apologize. I figure if I can reword the Worksheet_Change Target.Address to only look for $J$5 coming out of 1 module I'd be happy unless there is another place I can stick that.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("B2")) Is Nothing Then
    Select Case Range("B2")
        Case "Normal Test Point": ExampleNormalTestRow
        Case "Blank Line": ExampleBlankLine
        Case "Section Title": ExampleSectionTitleRow
        Case "Accuracy": ExampleAccuracyRow
    End Select
End If

If Target.Address = "$J$5" Then
Call LCB2
Call HCB2
End If

End Sub

 

[mole999]

that module should only be on the sheet you want to monitor, or am I missing something

 

[mumps]

Although it might be a little bothersome, you could be prompted asking whether you want to run the macros or not.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Select Case Range("B2")
            Case "Normal Test Point": ExampleNormalTestRow
            Case "Blank Line": ExampleBlankLine
            Case "Section Title": ExampleSectionTitleRow
            Case "Accuracy": ExampleAccuracyRow
        End Select
    End If
    If Target.Address = "$J$5" Then
        If MsgBox("Do you want to call LCB2 and HCB2?", vbYesNo) = vbYes Then
            Call LCB2
            Call HCB2
        End If
    End If
End Sub

 

[ArnMan]

that module should only be on the sheet you want to monitor, or am I missing something

Well I had to made a seperate module for those two calls because there werent updating automatically. Lets say I entered a value in J5 and wanted the answer to be in I9. when it was entered, because I needed NumberFormat ran it wouldnt update unless I reran the Sub, leaving the value in place then it would update I9. And if I needed to change the value in J5, it wouldnt autoupdate, so i needed it to be WorkSheet_Change.
I dont know if I explained that correctly or not. If I havent I apologize.

I was having that not updating problem in my other post
https://www.mrexcel.com/forum/excel-questions/1021806-setting-numberformat-if-statement-2.html

Which with the help of Rick and dmt32, I was able to fix that.

 

[ArnMan]

Well there are going to be alot of other people using this and I was hoping to streamline my Worksheet as much as possible. I dont want people getting confused.
If all else fails, I will just program everything from not using the J5 cell except for the original module.

Although it might be a little bothersome, you could be prompted asking whether you want to run the macros or not.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("B2")) Is Nothing Then
        Select Case Range("B2")
            Case "Normal Test Point": ExampleNormalTestRow
            Case "Blank Line": ExampleBlankLine
            Case "Section Title": ExampleSectionTitleRow
            Case "Accuracy": ExampleAccuracyRow
        End Select
    End If
    If Target.Address = "$J$5" Then
        If MsgBox("Do you want to call LCB2 and HCB2?", vbYesNo) = vbYes Then
            Call LCB2
            Call HCB2
        End If
    End If
End Sub