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

 

[palaeontology]

done ... and worked brilliantly ... thankyou.

I have a different TextBox that's causing some trouble.

The following code ...

Private Sub TextBox3_AfterUpdate()
   Set Rng2 = Range("B" & Range(TextBox3.Text).Row)
   TextBox2.Text = Rng2.Value
End Sub

Is what I was using to cause TextBox2 to display a student's name (from column B), which would change each time TextBox3 was updated.

I also wanted TextBox4 to change whenever TextBox3 was updated. I need TextBox 4 to display whatever is in column AC (and the row that is updated in TextBox 3, so I thought changing the code to this would work ...

Private Sub TextBox3_AfterUpdate()
   Set Rng2 = Range("B" & Range(TextBox3.Text).Row)
   TextBox2.Text = Rng2.Value
   Set Rng2 = Range("AC" & Range(TextBox3.Text).Row)
   TextBox4.Text = Rng2.Value
End Sub

but I'm getting incorrect displays ...

TextBox2 displays the correct information from column B on the first change, but from then on, it displays from column AC, which is what TextBox4 is supposed to do. And TextBox 4 displays correctly the first time, but doesn't change at all after that.

Any ideas ?

 

[Fluff]

That's because you are moving Rng2 from col B to col AC
try

TextBox4.Text = Rng2.offset(,27).Value

 

[palaeontology]

Thankyou, so much.

Those are all working now.

However, when I click the commandbutton called 'Go_Back_A_Student, which is meant to undo the last entry made in column W (from bottom up), the word ClearContents is highlighted as an error ... Compile Error : Variable not defined

Private Sub Go_Back_A_Student_Click()
    Dim Cnt As Long
    Dim NxtRw As Long
    
   Set Rng2 = Rng2.Offset(-1)
   TextBox2.Text = Rng2.Value
   
   NxtRw = Cells(Rows.Count, "W").End(xlUp).Offset(1).Row
   Cnt = WorksheetFunction.Count(Range("W2:W" & NxtRw))
   Range("W" & NxtRw - 1) = ClearContents
    
End Sub

This had been working several days ago, but of course, I've adjusted quite a few other codes since then, so I'm guessing one or more of those changes has impacted on this code .... or is this error unrelated to all that ?

Kind regards,

Chris

 

[Fluff]

That error in unrelated to the other code changes.
Have a look at post#9 & see how you cleared contents of F2:F4

 

[palaeontology]

Ahaaa, thankyou.

I honestly don't recall putting an = sign there. It definitely used to have a dot.

Anyway, I have one final question (I swear this is the last) ...

In the following code ...

Private Sub TextBox3_AfterUpdate()
   Set Rng2 = Range("B" & Range(TextBox3.Text).Row)
   TextBox2.Text = Rng2.Value
   TextBox4.Text = Rng2.Offset(, 27).Value
End Sub

TextBox4 is currently returning a value from column AC which is 27 columns to the right of column B, but it won't always be column AC I need to reference.

It will, however, always be 10 columns to the right of whatever column the user references in TextBox3.

So if they reference cell E4, then TextBox4 would want to look at column O

If they referenced cell Z8, then TextBox4 would want to look at column AJ

How would I change this part of the code ...

   TextBox4.Text = Rng2.Offset(, 27).Value

Thankyou, so very much for all your help on all of my questions, I truly do appreciate it.

Very kind regards,

Chris

 

[Fluff]

How about

TextBox4.Text = Range(textbox3.Text).Offset(, 10).Value

 

[palaeontology]

unfortunately that didn't work.

It was displaying correctly at first, but didn't change from that display when updates occured.

Is there some way it can take advantage of the updates that involve TextBox3 ?

 

[Fluff]

Textbox4 should change whenever textbox3 changes

 

[palaeontology]

I've just noticed, even though TextBox2 updates, so one student name is replaced with another student's name, as it should, it seems TextBox3 is staying unchanged. So, if the user inputs Z8 into TextBox3, then causes an update, TextBox2 changes, but TextBox 3 just keeps displaying Z8.

Hence TextBox4 won't change.

Here are my current codes, can you see why TextBox3 isn't changing from (for example) Z8 to Z9 to Z10 etc etc etc ???

Option Explicit
Dim Rng2 As Range


Private Sub ClearEverything_Click()
Call ClearAll
End Sub
Private Sub ClearLast_Click()
Range("F" & Rows.Count).End(xlUp).ClearContents
End Sub


Private Sub CommandButton100_Click()
Unload Me
Columns("W:W").Select
    Selection.ClearContents
    Range("W2").Select
TouchPadForB_PSMT.Show
End Sub


Private Sub CommandButton101_Click()
Unload Me
Columns("W:W").Select
    Selection.ClearContents
    Range("W2").Select
TouchPadForC_CAJ.Show
End Sub




Private Sub TextBox3_AfterUpdate()
   Set Rng2 = Range("B" & Range(TextBox3.Text).Row)
   TextBox2.Text = Rng2.Value
   TextBox4.Text = Range(TextBox3.Text).Offset(, 10).Value
End Sub


Private Sub Go_Back_A_Student_Click()
    Dim Cnt As Long
    Dim NxtRw As Long
    
   Set Rng2 = Rng2.Offset(-1)
   TextBox2.Text = Rng2.Value
   TextBox4.Text = Range(TextBox3.Text).Offset(, 10).Value
   
   NxtRw = Cells(Rows.Count, "W").End(xlUp).Offset(1).Row
   Cnt = WorksheetFunction.Count(Range("W2:W" & NxtRw))
   Range("W" & NxtRw - 1).ClearContents
    
End Sub




Private Sub Skip_A_Student_Click()
    Dim Cnt As Long
    Dim NxtRw As Long
    
   Set Rng2 = Rng2.Offset(1)
   TextBox2.Text = Rng2.Value
   TextBox4.Text = Range(TextBox3.Text).Offset(, 10).Value
      
   NxtRw = Cells(Rows.Count, "W").End(xlUp).Offset(1).Row
   Cnt = WorksheetFunction.Count(Range("W2:W" & NxtRw))
   Range("W" & NxtRw + 1).Value = Date


End Sub






Private Sub Enter_Results_Cognitive_Click()


    Dim Cnt As Long
    Dim NxtRw As Long
    Dim Rng As Range
    
   Set Rng2 = Rng2.Offset(1)
   TextBox2.Text = Rng2.Value
   TextBox4.Text = Range(TextBox3.Text).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
   
   Set Rng = Range(TextBox3.Value)
   Rng.Offset(Cnt).Value = Range("F2").Value
   Rng.Offset(Cnt, 1).Value = Range("F3").Value
   Rng.Offset(Cnt, 2).Value = Range("F4").Value
   
   Range("F2:F4").ClearContents


End Sub

 

[Fluff]

can you see why TextBox3 isn't changing from (for example) Z8 to Z9 to Z10 etc etc etc ???

Yup, you haven't told it to change?
I'll have a look at that shortly.