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]

try

Option Explicit
Dim Rng2 As Range
Dim Rng3 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 Rng3 = Range(textbox3.Text)
   Set Rng2 = Range("B" & Rng3.Row)
   textbox2.Text = Rng2.Value
   TextBox4.Text = Rng3.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)
   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 - 1).ClearContents
    
End Sub

Private Sub Skip_A_Student_Click()
    Dim Cnt As Long
    Dim NxtRw As Long
    
   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 + 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)
   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
   
   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]

I copy/pasted everything you provided there, but on clicking the 'Enter_Results_Cognitive command button, I'm getting a runtime error message ... Run-time error '1004' : Method 'Range' of object '_Global' failed

on this line ...

Set Rng = Range(TextBox3.Value)

from this code ...

Private Sub Enter_Results_Cognitive_Click()
    Dim Cnt As Long
    Dim NxtRw As Long
    Dim Rng As Range
    
   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
   
   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]

Try changing it to

   NxtRw = Cells(Rows.Count, "W").End(xlUp).Offset(1).Row
   Cnt = WorksheetFunction.Count(Range("W2:W" & NxtRw))
   Range("W" & NxtRw).Value = Date
   
   Rng3.Offset(Cnt).Value = Range("F2").Value
   Rng3.Offset(Cnt, 1).Value = Range("F3").Value
   Rng3.Offset(Cnt, 2).Value = Range("F4").Value

 

[palaeontology]

ok, not getting any error messages anymore, but several things are wrong ..

1) after entering the initial cell reference into TextBox3 (eg: Z8) the TextBox goes blank after the first update, and stays blank from then on ... I was hoping it was going to change form Z8 to Z9 to Z10 etc
2) the three values in F2, F3 and F4 should be sent to (in this example) Z8, AA8, and AB8, then the next three entered should be going to Z9, AA9, and AB9, etc etc etc but the first three are going to Z9, AA9, and AB9, while the second three are going to Z11, AA1, AB11, and a third three are going to Z13, AA13, AB13. This part was working correctly prior to the last change you made in Post #23 but is now completely out of whack.

 

[Fluff]

OK this will correct the Textbox3 value

 textbox3.Text = Rng3.Address(False, False)

You'll need to change it on all 3 buttons. Not sure if it will clear the 2nd problem, if not let me know

 

[palaeontology]

definitely fixed the first problem, but no change in the second problem

 

[Fluff]

Ok, try

   Rng3.Value = Range("F2").Value
   Rng3.Offset(, 1).Value = Range("F3").Value
   Rng3.Offset(, 2).Value = Range("F4").Value

 

[palaeontology]

we're so close.

That last change you made is now throwing everything just one row lower than it should be ...

if I type z8 into TextBox3, the F2, F3 and F4 values are being sent to Z9, AA9, and AB9 ... but they should be going to Z8, AA8 and AB8

the next updated values of F2, F3 and F4 are being sent to Z10, AA10, and AB10 ... but they should be going to Z9, AA9 and AB9

etc etc etc

 

[Fluff]

In you op you said

[TABLE="class: cms_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]
[/TR]
</tbody>[/TABLE]

You subsequently said that textbox3 should mirror that change.
Therefore when you click the button Z8 becomes Z9 & the data is entered on that row.
Before I make any further changes, could you confirm that all other buttons are working as expected & that the only change needed, is to adjust the placement of these 3 values.

 

[palaeontology]

I feared that might have caused confusion.

What I was trying to say was ...

* on entering 'B23' into TextBox3, TextBox2 should display contents of B23
* then if the user presses the command button called 'Enter_Results_Cognitive', TextBox3 should change to displaying 'B24' and TextBox2 should display the contents of B24
* etc etc etc

I apologise if I didn't make that very clear.

In answer to your other question, yes, everything else is working absolutely correctly. The only error at the moment is the placement of the 3 values.

Can that be fixed ?

Very kind regards,

Chris