Worksheet Change Event and Selection change not working

[moogthemoog]

Hi

I have a suggestions log, and I would like to keep a track of Likes and Dislikes.

I have an Excel table with a specific column to vote, but entering "L" or "D" (for Like and Dislike). I have code which will tally this up and identify net Like or Dislike.

However, to call this macro, I'm trying to use a Worksheet_Change event. This only appears to work when the user enters L or D by pressing Return.
If the user uses the cursor to navigate away from the cell (having typed L or D but not pressing Return), then the code doesn't work.

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo ErrHandler
    
    If Intersect(Target, Range("Table1[[#Data],[Vote Like or Dislike]]")) Is Nothing Then Exit Sub
    
    If Target.Text = "L" Or Target.Text = "D" Then Call Voting
   
ErrHandler:
    Application.EnableEvents = True
    Exit Sub
    
End Sub

So I have also added in a Worksheet Selection change event:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.EnableEvents = False
    On Error GoTo ErrHandler
    
    If Intersect(Target, Range("Table1[[#Data],[Vote Like or Dislike]]")) Is Nothing Then Exit Sub
    
    If Target.Text = "L" Or Target.Text = "D" Then Call Voting
   
ErrHandler:
    Application.EnableEvents = True
    Exit Sub
    
End Sub

However, the macro is only called if, when I type L then navigate Cursor Down, I immediately navigate Cursor Up to the cell.

I'd prefer to use an ActiveX Control (radio button) to vote for each suggestion, but this would presumably need a radio button on each table row (and I'd have to come up with code to add the radio button each time a new row is added to the table; and to delete the radio button if a table row is deleted).

Please can someone advise where I'm going wrong?

Thanks
Jon

 

[Arithos]

I had no issues with this code:

Private Sub Worksheet_Change(ByVal Target As Range)    Application.EnableEvents = False
    On Error GoTo ErrHandler
    
    If Intersect(Target, Range("A:B")) Is Nothing Or Target.Cells.Count > 1 Then
    Exit Sub
    Else
        If Target.Value = "L" Or Target.Value = "D" Then
            Target.Value = "called voting" ' replace with call macro
        End If
    End If
   
ErrHandler:
    Application.EnableEvents = True
    Exit Sub
    
End Sub

It might have to do with your range beeing a table?

 

[moogthemoog]

Thanks Sturia, I think the issue is that my Macro coding (see below) uses the row number of the Activecell to register the row with a vote in it.
If I enter a vote on Row 7 and Cursor Down to row 8, then the activecell will be row 8, which doesn't have a vote entered, so won't trigger the Change Event.

any idea how I can obtain the cell moved from, rather than to?

Thanks
Jon

Sub Voting()

Dim vintRow, vintLike, vintDislike As Integer
Dim vstrVote As String

vintRow = ActiveCell.Row
vstrVote = Range("K" & vintRow).Value
vintLike = Range("H" & vintRow).Value
vintDislike = Range("I" & vintRow).Value

Select Case vstrVote
    Case "L"
        Range("H" & vintRow).Value = vintLike + 1
    Case "D"
        Range("I" & vintRow).Value = vintDislike + 1
    Case Else
        Exit Sub
End Select

Range("K" & vintRow).Value = ""

End Sub

 

[Arithos]

Making macros off activecells overcomplicates things in my oppineon.

What are you trying to accomplish with this macro? Keep a count of likes/dislikes for the sheet/table in question?
Are you inputting another value?

Maybe add your Voting code into the worksheet_change macro, istead of calling a sub?

 

[moogthemoog]

Hi Sturla

Sorry for the delay in replying.

Each row contains one suggestion, and I am trying to keep a count of likes and dislikes for each suggestion.

I tried putting the code in the event, rather than having a separate macro to call. But still the same result.

Thanks
Jon