Sub worksheet_change(ByVal target As Range)

[hrayani]

hello,

i am using this code to call macros based on cell value.

Sub worksheet_change(ByVal target As Range)
Set target = Range("A1")
If target.Value = "1" Then
 Call onepo
End If
If target.Value = "2" Then
Call twopo
End If
If target.Value = "3" Then
Call threepo
End If
If target.Value = "4" Then
Call fourpo
End If
If target.Value = "5" Then
Call fivepo
End If
If target.Value = "6" Then
Call sixpo
End If


End Sub

Only problem i am having is whenever any other work i do on the sheet the macro triggers.
Whereas i only want the macros to triger when the value of cell A1 changes. Like when i enter anything
in cell (lets say) b2 and as soon as i press enter the macro triggers

Secondly cell A1 has a data validation list. i want to call the macro (sixpo) when the cell is blank.
i dont know how to enter it in the above code.

i would be grateful if anyone can help.

Thanks

Humayun

 

[Joe4]

You do not set "Target" in these procedures, "Target" is automatically the cell that was changed that is calling the procedure.
So in your case here, we just need to see if the Target cell (the cell being changed), is cell A1.

Here is how we can write the code to do all that you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Count = 1 And Target.Address = Range("A1").Address Then
        Select Case Target.Value
            Case "1"
                Call onepo
            Case "2"
                Call twopo
            Case "3"
                Call threepo
            Case "4"
                Call fourpo
            Case "5"
                Call fivepo
            Case "6"
                Call sixpo
            Case ""
                Call sixpo
        End Select
    End If

End Sub

 

[hrayani]

Bunch of Thanks Joe4

Both problem solved

 

[Joe4]

You are welcome.

 

[Joe4]

Since your new question is an entirely new question (independent of this one), I have moved it to its own thread here: https://www.mrexcel.com/forum/excel-questions/1071105-vba-show-specific-row-top.html

That way it shows up on the board as a new, unanswered question, and many more people will see it.

 

[hrayani]

Yes u r right Joe

Thanks