Run macro when enter a value in a cell

[SMILE]

Hi everyone

I need a help ..
In my worksheet "EDIT" if I enter any value in A1, I want a macro (mymacro) to run automatically

Can someone help in coding..
Thanks
Tom

[Mordred]

Hi SMILE, try

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Address = "$A$1" Then
    '''Your code here'''
    End If
End Sub

EDIT: Sorry for skipping this part but the previous code needs to go into your worksheet module in the VBE.

[SMILE]

Hi..Mordred
Thanks for the reply but I am getting the following error
compile error:
Ambiguous name detected:Worksheet_Change

Is it because I have another codes in that worksheet as below

Private Sub Worksheet_Change(ByVal Target As Range)

Const sTRIGGER_CELL = "e11"

If Not Intersect(Target, Range(sTRIGGER_CELL)) Is Nothing Then
  Select Case UCase(Range(sTRIGGER_CELL).Value)
      Case "Y"
          yes
       Case "N"
           no
   End Select
End If

End Sub

Hope you will sort it out for me.
Thanks
Tom

[Mordred]

Yes, it is because of that code. You can get around this by either:
1. erase the other code
2. add the if statement from my solution to your existing code

Private Sub Worksheet_Change(ByVal Target As Range)

Const sTRIGGER_CELL = "e11"

If Target.Address = "$A$1" Then
    '''Your code here'''
    End If


If Not Intersect(Target, Range(sTRIGGER_CELL)) Is Nothing Then
  Select Case UCase(Range(sTRIGGER_CELL).Value)
      Case "Y"
          yes
       Case "N"
           no
   End Select
End If

End Sub

or you can use the double click event

Private Sub Worksheet_BeforeDoubleClick(ByVal _
    Target As Range, Cancel As Boolean)
    If Target.Address = "$A$1" Then
    '''Your code here'''
    End If
End Sub

[SMILE]

Hi Mordred
I am really sorry to bother you again...
Your code is working perfect..but I need to add one more condition
My existing code is as below

Private Sub Worksheet_Change(ByVal Target As Range)
Const sTRIGGER_CELL = "e11"

    If Target.Address = "$a$1" Then
    '''Your code here'''
    
        Range("B13:e18").Select
       Selection.clearcontents
    Range("e11").Select
  
    End If

If Not Intersect(Target, Range(sTRIGGER_CELL)) Is Nothing Then
  Select Case UCase(Range(sTRIGGER_CELL).Value)
      Case "Y"
          yes
       Case "N"
           no
   End Select
End If

End Sub

I need the following condtion also to be added
IF I go to A1 and press back space (no value) and enter, then I just need the following cell to select.

 Range("e11").Select

If any other value enter in A1, then I need the following as my previous code

        Range("B13:e18").Select
       Selection.clearcontents
    Range("e11").Select

Thanks
tom

[mudraker]

just add code to test value of a1

if range("a1").value = "" then exit sub

[Mordred]

Hi Tom, try the following out

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Const sTRIGGER_CELL = "e11"

    If Target.Address = "$A$1" And Target.Value <> "" Then
        '''Your code here'''
        Application.EnableEvents = False
        Range("B13:e18").ClearContents
        Range("E11").Select
        Application.EnableEvents = True
    ElseIf Target.Address = "$A$1" And Target.Value = "" Then
        Application.EnableEvents = False
        Range("E11").Select
        Application.EnableEvents = True
    End If

    'If Not Intersect(Target, Range(sTRIGGER_CELL)) Is Nothing Then
    '  Select Case UCase(Range(sTRIGGER_CELL).Value)
    '      Case "Y"
    '          yes  <<---|
    '       Case "N"     |-------What defines these?
    '           no  <<---|
    '   End Select
    'End If

End Sub

Note: I commented out the existing code because I couldn't run it and am honestly confused as to how the variables yes and no are defined or known! At any rate, see if the code works the way it is and then remove the single quotes for the other code.

Regards:

[SMILE]

Thank u Mordred.. Its working perfect...