Excel: Need Macro to run when value entered

[Solved/Closed]
Report
Posts
5
Registration date
Monday March 22, 2010
Status
Member
Last seen
March 24, 2010
-
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
-
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!

3 replies

Posts
5
Registration date
Monday March 22, 2010
Status
Member
Last seen
March 24, 2010

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!
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
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
Posts
5
Registration date
Monday March 22, 2010
Status
Member
Last seen
March 24, 2010

this didn't work either... but thank you!
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
Hi,
Please, could you post an exemple of your workbook (without personal data) in a deposite files website like https://authentification.site and after copy/paste the created link into your next message.
See you
Posts
5
Registration date
Monday March 22, 2010
Status
Member
Last seen
March 24, 2010

Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
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 :)

Posts
5
Registration date
Monday March 22, 2010
Status
Member
Last seen
March 24, 2010

I only seem to be missing one thing, it's still running the macros repeatedly so that the data flashes. I might have missed setting a value somewhere... ? Other than that, it looks great! Thank you so much for your help!
Posts
7098
Registration date
Saturday April 7, 2007
Status
Moderator
Last seen
June 10, 2021
488
Hi,
I forget to tell you that in my workbook (above link) I have also deleted and changed some rows into your two macros thus normally there are no flashed data . Compare your workbook and mine. :)