Excel Userform - Update value based on multiple textboxes on command button click

RahulM · Apr 16th 2016

I have a userform to conduct a multiple choice test. Test will have 20 questions. It is working fine when questions are populated in a definate order. But I want to pick random questions from set of 20.

I am able to resolve the part of generating random numbers. I am generating 20 numbers in random order from series of 1 to 20 in 20 individual textboxes on excel userform. (these boxes will be hidden)

I want to get value in a textbox from multiple textboxes but one at a time on click of a command button.

Example;
Textbox41 to pick value from textbox21 on click of Start button.
On click of Next, Textbox41 to pick value from textbox22. And everytime next button is clicked Textbox41 to pick value from next textbox.

Any suggestions on the best way to do this? Does anyone have a good example I can replicate or feel like coming up with some code?

Any help someone could offer would be very much appreciated!

SamDsouza · Apr 18th 2016

Re: Excel Userform - Update value based on multiple textboxes on command button click

Rahulji

Namaste

i've not gone through your file, I tried with 5 textboxes with the below code

Below

Quote

Val(.Caption) + 1

indicates the number of counts of Next Button or CommandButton1

Code

Sub CommandButton1_Click()
With CommandButton1
Select Case Val(.Caption) + 1
   Case 1
   .Caption = Val(.Caption) + 1 & " Next"
   TextBox1.Text = TextBox1.Text
   
   Case 2
  .Caption = Val(.Caption) + 1 & " Next"
   TextBox1.Text = TextBox2.Text
   
Case 3
  .Caption = Val(.Caption) + 1 & " Next"
   TextBox1.Text = TextBox3.Text
Case 4
  .Caption = Val(.Caption) + 1 & " Next"
   TextBox1.Text = TextBox4.Text
Case 5
  .Caption = Val(.Caption) + 1 & " Next"
   TextBox1.Text = TextBox5.Text
  End Select
End With




Private Sub UserForm_Initialize()
TextBox1.Text = "Jack"
TextBox2.Text = "Jill"
TextBox3.Text = "Humpty"
TextBox4.Text = "Dumpty"
TextBox5.Text = "Twinkle Twinkle"




End Sub

Display More

HTH

SamD

RahulM · Apr 18th 2016

Re: Excel Userform - Update value based on multiple textboxes on command button click

Dear SamD,

Thank you for your reply. I have tried your code and it's working the way I wanted. That's Excellent!

Meantime, since I was in urgent need of a solution, I have worked on my code and found a work around for this. On similar lines of number of counts of CommandButton, I have included an extra textbox to auto update number incremented by 1 on each click of CommandButton.
Then used "if" condition to refer to number in textbox and then pick value from appropriate textbox. It worked and running smoothly.

Thanks though.

Excel Userform - Update value based on multiple textboxes on command button click

Participate now!

Share