Excel: Need Macro to run when value entered

Solved/Closed
beka0422 Posts 5 Registration date Monday March 22, 2010 Status Member Last seen March 24, 2010 - Mar 22, 2010 at 01:29 PM
aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 - Mar 24, 2010 at 09:36 AM
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

beka0422 Posts 5 Registration date Monday March 22, 2010 Status Member Last seen March 24, 2010
Mar 22, 2010 at 01:49 PM
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!
0
aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 491
Mar 22, 2010 at 03:17 PM
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
0
beka0422 Posts 5 Registration date Monday March 22, 2010 Status Member Last seen March 24, 2010
Mar 23, 2010 at 07:18 AM
this didn't work either... but thank you!
0
aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 491
Mar 23, 2010 at 07:25 AM
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
0
beka0422 Posts 5 Registration date Monday March 22, 2010 Status Member Last seen March 24, 2010
Mar 23, 2010 at 03:59 PM
0
aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 491
Mar 23, 2010 at 04:45 PM
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 :)

0
beka0422 Posts 5 Registration date Monday March 22, 2010 Status Member Last seen March 24, 2010
Mar 24, 2010 at 07:29 AM
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!
0
aquarelle Posts 7119 Registration date Saturday April 7, 2007 Status Moderator Last seen October 18, 2022 491
Mar 24, 2010 at 09:36 AM
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. :)
0