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]

Something like this

Option Explicit
Dim Rng As Range

Private Sub Go_Back_A_Student_Click()
   Set Rng = Rng.Offset(-1)
   tb2.Text = Rng.Value
End Sub

Private Sub Enter_Results_Cognitive_Click()     'Skip_A_Student will be the same as this
   Set Rng = Rng.Offset(1)
   tb2.Text = Rng.Value
End Sub

Private Sub tb3_AfterUpdate()
   Set Rng = Range("A" & Range(tb3.Text).Row)
   tb2.Text = Rng.Value
End Sub

Where tb2 & tb3 are the names of your textboxes

 

[palaeontology]

Hi Fluff,

thanks so much for those codes.

The code going into tb3 works perfectly, thankyou so much for that, but the codes for the other three buttons are coming up with error messages, most likely because I've added your codes onto the ends of the codes I already had in each of them ... codes that did other things.

These are the three coded command buttons (which currently operate without error) that I'd like to add your suggested codes to ...

Private Sub Go_Back_A_Student_Click()
    Dim Cnt As Long
    Dim NxtRw As Long
    
    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
    
    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
    
    NxtRw = Cells(Rows.Count, "W").End(xlUp).Offset(1).Row
    Cnt = WorksheetFunction.Count(Range("W2:W" & NxtRw))
    Range("W" & NxtRw).Value = Date


Dim Rng As Range
    
    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

Can you please show me how to add your suggested codes to the corresponding command buttons .... when I add your codes in, I'm getting error messages.

Very kind regards,

Chris

 

[Fluff]

Try

Option Explicit
Dim Rng2 As Range

Private Sub tb3_AfterUpdate()
   Set Rng2 = Range("A" & Range(tb3.Text).Row)
   tb2.Text = Rng2.Value
End Sub

Private Sub Go_Back_A_Student_Click()
    Dim Cnt As Long
    Dim NxtRw As Long
    
   Set Rng2 = Rng2.Offset(-1)
   tb2.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


Private Sub Skip_A_Student_Click()
    Dim Cnt As Long
    Dim NxtRw As Long
    
   Set Rng2 = Rng2.Offset(1)
   tb2.Text = Rng2.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
    
   Set Rng2 = Rng2.Offset(1)
   tb2.Text = Rng2.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

 

[palaeontology]

Hi Fluff,

thankyou again for putting the time in to make the code for me, but it's returning a run-time error ..... Run-time error '424' Object Required

If I click the 'Enter_Results_Cognitive command button, it highlights the line .... Set Rng2 = Rng2.Offset(1)

If I click the 'Go_Back_A_Student' command button, it highlights the line .... Set Rng2 = Rng2.Offset(-1)

If I click the 'Skip_A_Student' command button, it highlights the line .... Set Rng2 = Rng2.Offset(1)

I copy/pasted your codes directly, including the lines ...

Option Explicit
Dim Rng2 As Range

Any idea what I've missed ?

Very kind regards,

Chris

 

[Fluff]

When you exit textbox3 does textbox2 get updated with the correct value?

 

[palaeontology]

yes

though your code was referring to column A which I had to change to column B ...

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

as u can see, I've also added another TextBox ... TextBox4.

However that was after the error messages.

Of the four codes you provided, the one above is the only one not to return an error message related to Set Rng2.

 

[Fluff]

Are these 2 lines

Option Explicit
Dim Rng2 As Range

The very first lines in the module (ie before any code)?

 

[palaeontology]

It wasn't but is now ... and that particular error message no longer appears, but a new one does, for a different part of my code ...

I get a 'Variable not defined' error message for this line ....

Set Rng = Range(TextBox3.Value)

in the following code ...

Private Sub Enter_Results_Cognitive_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).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

If you recall ...

I need the value in F2 to be sent to the cell location that the user had typed into TextBox3.
I need the value in F3 to be sent to the cell location one column to the right of the location that the user had typed into TextBox3.
I need the value in F4 to be sent to the cell location two columns to the right of the location that the user had typed into TextBox3.

I had used Rng for this before we started to use Rng2, so I don't know how to adjust the code for what I need.

Kind regards,

Chris

 

[Fluff]

Try adding

Dim Rng As Range

under the 2 existing Dim statements in the code