changing display in a UserForm TextBox

P

palaeontology

Active Member
Joined
May 12, 2017
Messages
444
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
Something like this
Code: 
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
 
Last edited:
Upvote 0
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 ...

Code: 
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
 
Last edited:
Upvote 0
Try
Code: 
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
 
Upvote 0
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
 
Upvote 0
When you exit textbox3 does textbox2 get updated with the correct value?
 
Upvote 0
yes

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

Code: 
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.
 
Last edited:
Upvote 0
Are these 2 lines
Code: 
Option Explicit
Dim Rng2 As Range
The very first lines in the module (ie before any code)?
 
Upvote 0
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 ....

Code: 
Set Rng = Range(TextBox3.Value)

in the following code ...

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
 
Upvote 0
Try adding
Code: 
Dim Rng As Range
under the 2 existing Dim statements in the code
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Userform code to open UK post code within Google Maps
2
Replies
11
Views
271
ipbr21054
I
C
  • Question Question
WHY IS THIS CODE IS RANDOMLY POSTING CHANGES TO THE WRONG CELL ROW IN THE SHEET FROM THE USERFORM ?
Replies
6
Views
279
offthelip
O
I
  • Question Question
Advice to remove old code for a new code
Replies
2
Views
212
ipbr21054
I
I
  • Question Question
Paste value from userform TextBox to another Textbox
Replies
4
Views
568
ipbr21054
I
S
  • Solved
Help Required With Barcode Being Read Into A Userform...
Replies
3
Views
2K
NoSparks
N

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top