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.

Answers


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

That's it.

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 ...

Perfect, Thanks!

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

Need Your Help

Create a script to git pull/checkout from specific branch

git deployment

I'm trying to write a script to deploy our webapp easily from git. We have now two servers ("production" and "development").

R Loop - Adding rows without predefined dimensions

r loops matrix

I want to store the results of a test within an R for loop inside a matrix or data.frame with (in this case) two columns, but an indeterminate number of rows, because the number of rows will depend...