Help with Change event to call macro based on IF THEN

[garmenta]

So what I want to do is enter yes or no into a cell and have YES answer run a macro and have NO answer run a macro. ...

I am oh so close, the problem is that I can't get the code to run on the cell i enter yes or no into as once I hit enter the "ActiveCell" is now my new cell. What am i doing wrong?

Private Sub Worksheet_Change(ByVal Target As Range)
'...is ths right? orshould it be selectchange?

Dim ACTCEL As String

Curr_Addr = ActiveCell.Address
Range(Curr_Addr).Select
ACTCEL = ActiveCell

here is where I run into problems. As soon as I hit enter the "ActiveCell" value changes and the rest of the code starts using the current cell. For instance if i type Yes into A1, as soon as I hit Enter and go into cell A2 the the "ActiveCell" is now A2...where I havne't entered anything into.​

If ACTCEL = "yes" Then
Run "Show_donkey1" 'shows donkey picture.
Range(Curr_Addr).Select
Application.EnableEvents = True
ElseIf ACTCEL = "no" Then
...will run anotehr macro
End If
End Sub

 

[jproffer]

There are other ways but since you're that far along already, how bout this:

ACTCEL = ActiveCell.Offset(-1, 0)

where you have it bold above.

I can't remember at the moment which reference is the row and which is the column, so it may be Offset(0, -1), the help that pops up will tell you.

 

[gauntletxg]

Will the cell you want to check for yes or no change? You could just refer to that cell in your code or use a named range.

Another option is Tools > Options > Edit > uncheck Move selection after enter. But, you would still encounter problems if you leave the cell through tab or an arrow key.

 

[garmenta]

Basically i'm setting up a spreadsheet for 20 questions with 2 teams. 1 team one column. 20 rows

I will be typing yes or no in a cell and having a picture auto pop based on teh answer...hence the SHOW DONKEY portion of the code.

I want to be able to type yes, hit enter. and have code run checking the value of the cell i just typed into and then running an if then to determine if the value is yes or no.

 

[VoG]

Make use of Target which is the cell that was changed, for example

Private Sub Worksheet_Change(ByVal Target As Range)
With Target
    Select Case (UCase(.Value))
        Case "YES"
            'do if Yes
        Case "NO"
            'do if No
        Case Else
            MsgBox "Invalid response"
    End Select
End With
End Sub

 

[garmenta]

Thank you Vog II this worked PERFECTLY!