Cell reference trigger macro

[Abvlecxe]

Hello, I am trying to call macros based on the number in one cell but only the change for numbers 1 & 2 seem to work:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells.Count > 1 Then Exit Sub
If IsNumeric(Target) And Target.Address = "$D$27" Then
Select Case Target.Value
Case 1: Call Macro1
Case 2: Call Maco2
Case 3: Call Macro3
Case 4: Call Macro4
Case 5: Call Macro5
End Select
End If
End Sub

Any help would be apreciated, thanks

 

[mumps]

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("D27")) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If IsNumeric(Target) Then
        Select Case Target.Value
            Case Is = 1: Call macro1
            Case Is = 2: Call macro2
            Case Is = 3: Call macro3
            Case Is = 4: Call macro4
            Case Is = 5: Call macro5
        End Select
    End If
End Sub

If the target is a number, you have to use "Case Is ="

 

[Abvlecxe]

Hi, thanks for the reply unfortunately the same issue, if i change to anything but 1 or 2 nothing happens!

Try:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Intersect(Target, Range("D27")) Is Nothing Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If IsNumeric(Target) Then
        Select Case Target.Value
            Case Is = 1: Call macro1
            Case Is = 2: Call macro2
            Case Is = 3: Call macro3
            Case Is = 4: Call macro4
            Case Is = 5: Call macro5
        End Select
    End If
End Sub

If the target is a number, you have to use "Case Is ="

 

[mumps]

I tried the macro with some dummy data and it worked properly. Is the value in D27 inserted manually or is it the result of a formula? This is probably a stupid question but do macros 3 to 5 exist?

 

[Abvlecxe]

The cell is from a data validation list numbers 1 to 5, i even tried just typing in the number but still nothing, will try removing validation and try on another worksheet to check.

Yea i need 5 as depending on what number is picked will then trigger the appropriate macro to then organise the worksheet.

Thanks

I tried the macro with some dummy data and it worked properly. Is the value in D27 inserted manually or is it the result of a formula? This is probably a stupid question but do macros 3 to 5 exist?

 

[mumps]

Check the values in your data validation list for beginning or trailing spaces for numbers 3, 4 and 5. If there are extra spaces they won't be visible but they will affect the macro.

 

[mikerickson]

Try this, just to make sure that everyone (including VBA) is working with numbers.

Select Case Val(Target.Value)

 

[rlv01]

The code mumps posted works for me as well. By any chance are you using code that turns off events? i.e.

Application.EnableEvents = False

 

[AC PORTA VIA]

Private Sub Worksheet_Change(ByVal Target As Range)
Call Cells(27, 4).Select
If Not Intersect(Target, Range("$d$27")) Is Nothing Then
 Select Case Range("$d$27")
        Case "1": Macro1
        Case "2": Macro2
        Case "3": Macro3
        Case "4": Macro4
        Case "5": Macro5
    End Select
    
End If
End Sub