Excel: Need Macro to run when value entered Solved/Closed

Question

Hi!  It's been a long time since I used VB.  I seem to be a bit rusty, and I can't figure this out.

What I need is for a specific macro to run when a corresponding number is entered into cell A2.  There will be about 100 different numbers with 100 matching macros to run.

For example, when "11027" is entered in cell A2, I need macro "macroGA11027" to run.  When "97047" is entered in cell A2, I need macro "macroGA97047" to run.  However, if cell A2 is cleared, I need all information from the macros to be cleared.

So far I have:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$2" Then

If Range("A2").Value = "11207" Then

Call macroGA11207

End If

End Sub

This seems to be very wrong because the information starts flashing (running the macro over and over).  Can you please help?

Thank you!

beka0422 · Answer

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("A2").Value = "11027" Then

Call macroGA11207

End If

End Sub

Is working better, but I can't seem to get all the information to clear when call A2 is cleared.
Thank you!

aquarelle · Answer

Hello,

May be you could try with the macro written like this :

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Target.Address = "$A$2") Then
        Select Case Range("A2").Value
            Case Is = 11027
                Call macroGA11027
            Case Is = 97047
                Call macroGA97047
            Case Else
        End Select
    End If
End Sub

Best regards

aquarelle · Answer

Hi,

Here is the new file : http://www.cijoint.fr/cj201003/cijIqkQ6Au.xls

I added a macro to clear the cells content and corrected a mistake concerning the number and the name of the macro : it is not macroGA11027 but macroGA11207. I did a mistake when I typed my above macro I reversed two numbers.

Tell me back if it is OK :)

Excel: Need Macro to run when value entered

3 responses

Similar discussions

Subscribe To Our Newsletter!