changing display in a UserForm TextBox

[palaeontology]

A user enters a cell address (eg: R23) into TextBox3

I would like a different TextBox ... TextBox2 ... to display the contents (a person's name) that would be found in column B of the same row of the cell address that had been entered into TextBox3... so in this example, TextBox2 should display the contents of B23

But then, I would like TextBox2 to change its display depending on what the user presses from then on ...

If user presses ....

* the command button called ... Enter_Results_Cognitive ... TextBox2 should now display the contents of B24, and if pressed again, then it displays the contents of B25, etc etc etc
* the command button called ... Go_Back_A_Student ... TextBox2 should now display the contents of the row (from column B) immediately above what it had just been displaying. So if it had been displaying the contents of B25 at the time 'Enter_Results_Cognitive' was pressed, then it should now display the contents of B24
* the command button called ... Skip_A_Student ... TextBox2 should now display the contents of the row (from column B) immediately below what it had just been displaying. So if it had been displaying the contents of B25 at the time 'Skip_A_Student' was pressed, then it should now display the contents of B26

I expect I've confused you with this description, so here's a table showing an example of what is pressed and what TextBox2 should display if the user enters (let's say) R23 into TextBox3 ...

​

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]User Presses Command Button called ...[/TD]
[TD="align: center"]TextBox2 displayes contents of ....[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]B23[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Enter_Results_Cognitive[/TD]
[TD="align: center"]B24[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Enter_Results_Cognitive[/TD]
[TD="align: center"]B25[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Go_Back_A_Student[/TD]
[TD="align: center"]B24[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
[TR]
[TD="align: center"]Skip_A_Student[/TD]
[TD="align: center"]B25[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[/TR]
</tbody>[/TABLE]

Can someone please help me with the code required for TextBox2 ?

Very kind regards,

Chris

 

[Fluff]

Ok, move the relevant code infront of the line that changes textbox3 value

   Set Rng = Range(textbox3.Value)
   Rng.Value = Range("F2").Value
   Rng.Offset(, 1).Value = Range("F3").Value
   Rng.Offset(, 2).Value = Range("F4").Value

   Set Rng2 = Rng2.Offset(1)
   Set Rng3 = Rng3.Offset(1)
   textbox2.Text = Rng2.Value
   textbox3.Text = Rng3.Value
   TextBox4.Text = Rng3.Offset(, 10).Value
   
   NxtRw = Cells(Rows.Count, "W").End(xlUp).Offset(1).Row
   Cnt = WorksheetFunction.Count(Range("W2:W" & NxtRw))
   Range("W" & NxtRw).Value = Date
   
   Range("F2:F4").ClearContents

End Sub

 

[palaeontology]

ok, that fixed the 'out by one row' issue, but now the display in TextBox3 disappears after the first update, and because there's no display, from that point on the line ....

Set Rng = Range(TextBox3.Value)

comes up as an error

 

[Fluff]

Forgot to include one of the previous amendments

Private Sub Enter_Results_Cognitive_Click()
    Dim Cnt As Long
    Dim NxtRw As Long
    Dim Rng As Range
   
   Set Rng = Range(TextBox3.Value)
   Rng.Value = Range("F2").Value
   Rng.Offset(, 1).Value = Range("F3").Value
   Rng.Offset(, 2).Value = Range("F4").Value

   Set Rng2 = Rng2.Offset(1)
   Set Rng3 = Rng3.Offset(1)
   TextBox2.Text = Rng2.Value
   TextBox3.Text = Rng3.Address(False, False)
   TextBox4.Text = Rng3.Offset(, 10).Value
   
   NxtRw = Cells(Rows.Count, "W").End(xlUp).Offset(1).Row
   Cnt = WorksheetFunction.Count(Range("W2:W" & NxtRw))
   Range("W" & NxtRw).Value = Date
   
   
   Range("F2:F4").ClearContents

End Sub

 

[palaeontology]

That did it .... thankyou so very much for everything you've done.

I am exstremely grateful, I really am.

Very kindest regards,

Chris

 

[Fluff]

Glad to help & thanks for the feedback