Macro doesn't call other macro

[Fernando14]

I had this macro working before, but for whatever reason it no longer calls the desired macro. I made changes to the other macro, but none to the "trigger" macro. Here is the trigger macro :

Option Explicit
Public FLAGNUM As Integer


Sub worksheet_change(ByVal Target As Range)
Set Target = Range("B13")
FLAGNUM = Target.Value
If Target.Value > 0 Then
 Call Flags
End If


End Sub

Here is the macro that gets triggered if it helps:

Option Explicit
Public FLAGNUM As Integer
Public FLAGSW() As Double
Public FLAGSL() As Double
Public FLAGSNORP() As String




Sub Flags()
'
' Flags Macro
'

ReDim FLAGSW(FLAGNUM)
ReDim FLAGSL(FLAGNUM)
ReDim FLAGSNORP(FLAGNUM)
Dim i As Integer

For i = 1 To FLAGNUM
   FLAGSW(i) = InputBox("What is flag " & i & "'s width?")
    FLAGSL(i) = InputBox("What is flag " & i & "'s length?")
    FLAGSNORP(i) = InputBox("Is flag " & i & " nylon or polyester? (Enter N or P)")

Next i

End Sub

 

[Fernando14]

Nothing happens when I change the target cell's value. I've tried referencing it different ways with no success. Any help is appreciated.

 

[Joe4]

What is the name of the module the "worksheet_change" procedure is in?

 

[Fernando14]

What is the name of the module the "worksheet_change" procedure is in?

Sheet9, whereas the other macro has module9.

 

[Fluff]

Remove this

Public FLAGNUM As Integer

from the sheet module. All Global variables must be in a standard module.

 

[Fernando14]

Remove this

Public FLAGNUM As Integer

from the sheet module. All Global variables must be in a standard module.

Thank you!

 

[Joe4]

Sheet9, whereas the other macro has module9.

OK, you have put it in the correct place

This code of yours looks a little funny too:

Option Explicit
Public FLAGNUM As Integer


Sub worksheet_change(ByVal Target As Range)
Set Target = Range("B13")
FLAGNUM = Target.Value
If Target.Value > 0 Then
 Call Flags
End If


End Sub

In these type of procedures, you usually do NOT set the Target value - the Target value, by default, is the cell that was just updated.
You don't need to set it, it is automatic.

 

[Fernando14]

OK, you have put it in the correct place

This code of yours looks a little funny too:

Option Explicit
Public FLAGNUM As Integer


Sub worksheet_change(ByVal Target As Range)
Set Target = Range("B13")
FLAGNUM = Target.Value
If Target.Value > 0 Then
 Call Flags
End If


End Sub

In these type of procedures, you usually do NOT set the Target value - the Target value, by default, is the cell that was just updated.
You don't need to set it, it is automatic.

Is this why everytime I change any cell the macro gets triggered? How would I fix this?

 

[Joe4]

Is this why everytime I change any cell the macro gets triggered? How would I fix this?

Yes.

If you only want it to run when B13 is updated, try this:

Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("B13").Address Then
        FLAGNUM = Target.Value
        If Target.Value > 0 Then Call Flags
    End If

End Sub

 

[Fernando14]

Yes.

If you only want it to run when B13 is updated, try this:

Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = Range("B13").Address Then
        FLAGNUM = Target.Value
        If Target.Value > 0 Then Call Flags
    End If

End Sub

This worked, thank you!