Excel Userform with Textbox, how to toggle through values in range of textbox
Purpose: Click on a cell in a range (Range: Column K:K on excel worksheet). Once you click on a specific cell in column K, userform pops up with cell value using following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("K:K")) Is Nothing Then Credit_Information.TextBox1.Value = Target.Value Credit_Information.Show End If End Sub
My question, is depending on where I click on column K, I want to use two buttons on my userform (Previous and Next) to move up and down column K and see the values of the cell dynamically change on my userform. Is this possible? Please let me know if any clarification is needed.
Just add the two command buttons to your userform.
Name one of the buttons cmdNext and give it a caption of "Next".
Name the other button cmdPrev and give it a caption of "Previous".
Then, in the userform code module, place these routines:
Private Sub cmdNext_Click() ActiveCell(2).Select End Sub Private Sub cmdPrev_Click() If ActiveCell.Row > 1 Then ActiveCell(0).Select End Sub
Note: if you want you can add code to ensure that the ActiveCell is in column K before allowing the new selections:
If ActiveCell.Column = 11 Then ...
I also found out that using Offset worked for me too in this manner. I'm not sure however if I'm breaking any conventions by doing this.
Private Sub CommandButton1_Click() ActiveCell.Offset(-1).Activate End Sub Private Sub CommandButton2_Click() ActiveCell.Offset(1).Activate End Sub
It is possible, but I would create another procedure for that. What you could do is declare a public variable in your userform & set it equal to the range Target. Then you could call another procedure from the userform on each button click and redefine the selected range after each click.
So, at the top of your userform do this:
Public selected_cell as Range
Then for the up button:
Private Sub ButtonUp.Click() If selected_cell.Row < 2 Then Exit Sub selected_cell.Rows(0).Select Set selected_cell = selected_cell.Rows(0) me.TextBox1.Value = selected_cell End Sub
And the down button would be:
Private Sub ButtonDown.Click() selected_cell.Rows(2).Select Set selected_cell = selected_cell.Rows(2) me.TextBox1.Value = selected_cell End Sub
Now let's make your code like this:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) On Error Resume Next If Target.Cells.Count > 1 Then Exit Sub If Not Intersect(Target, Range("K:K")) Is Nothing Then With Credit_Information Set .selected_cell = target .TextBox1.Value = Target.Value .Show End With End If End Sub