Have to click on cell again for it to update

[mystiedx]

Hello to all.

I have the following code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'when not to do anything

    If Target.Column <> 4 And Target.Column <> 5 And Target.Column <> 6 Then Exit Sub
    If Target.Count > 1 Then Exit Sub
    If Not IsNumeric(Target.Value) Then
        MsgBox "Please, enter numbers only."
        Target.Select
        Exit Sub
    End If
   
    Dim fndRng As Range

    With ActiveSheet.Range("H:H")  ' <~~ the sheet with the list
    Set fndRng = .Find(What:=Target.Value, _
                       After:=.Cells(.Cells.Count), _
                       LookIn:=xlValues, _
                       LookAt:=xlWhole, _
                       SearchOrder:=xlByRows, _
                       SearchDirection:=xlNext, _
                       MatchCase:=False)
    If Not fndRng Is Nothing Then
        Application.EnableEvents = False
        Target.Value = fndRng.Offset(0, 1).Value
        Application.EnableEvents = True
    Else
        Target.Select
    End If
End With
End Sub

Form Teacher Remarks 1.xlsm
	A	B	C	D	E	F	G	H	I
7	1							1	trying
8	2							2	Is self-confident and has excellent manners.
9	3			trying	trying			3	Sets an example of excellence in behaviour and cooperation.
10	4							4	Is a self-motivated student.
11	5			Is a pleasant, respectful, and well-behaved student.	Has shown noticeable improvement in her behaviour. Must keep it up.			5	Is a pleasant, respectful, and well-behaved student.
12	6							6	Is a nice and attentive student.
13	7			1	trying			7	Should be more responsible and disciplined in her behaviour.
14	8							8	Has shown noticeable improvement in her behaviour. Must keep it up.
15	9			nope	hello are you sure			9	Improvement of attitude towards work required.
16	10			trying		trying		10	Is too often absent.
17	11			trying	trying			11	Is too often late.
18	12			Is a pleasant, respectful, and well-behaved student.	Sets an example of excellence in behaviour and cooperation.			12	try
19	13							13	nope
20	14							14	hello are you sure
21	15							15
22	16
7 GRATITUDE

When I press a number (example 1), it should display "trying" in the cell. However, this doesn't happen instantly. I must click the cell again for "trying" to appear. What I want is any of the test appears immediately when I press corresponding number.

 

[Alex Blakenburg]

I am not entirely clear on what you are trying to achieve but I suspect a lot of you issue centres around using
• Worksheet_SelectionChange
I suspect that you really want
• Worksheet_Change

Do you want to give that a try ?

 

[mystiedx]

I am not entirely clear on what you are trying to achieve but I suspect a lot of you issue centres around using
• Worksheet_SelectionChange
I suspect that you really want
• Worksheet_Change

Do you want to give that a try ?

Works perfect.

I guess I'm still not clear about when to use SelectionChange v/s Change.

Anyway, thanks a lot Alex. ?

 

[Alex Blakenburg]

No problem, glad I could help.
FYI
SelectionChange is when you move around the worksheet (eg from cell to cell). It would probably be unusual to need that.

When you enter something into a cell or copy/paste etc, that triggers the Change event.
This is the most commonly used worksheet event.

 

[Gokhan Aycan]

SelectionChange works a treat with modeless userforms to auto update depending on the row you are on.

 

[mystiedx]

No problem, glad I could help.
FYI
SelectionChange is when you move around the worksheet (eg from cell to cell). It would probably be unusual to need that.

When you enter something into a cell or copy/paste etc, that triggers the Change event.
This is the most commonly used worksheet event.

Ah ok.. Now I get it. I always thought that SelectionChange was used when the value in a cell changes.

Thanks again Alex.