move the values in textboxes that are in a userform up and down

[sdoppke]

hi everyone my title pretty much sums it up. i have seen a lot on how to do this with a listbox, but i need to do it with textboxes. i have 30 textboxes in order and would like to have two buttons (1 For move up and 1 for move down) to move the value from the current textbox in focus (active) and swap it up or down.


thanks a ton for any help, or direction.

sd

 

[tlowry]

Here is my take on what you want....

Form: has:

1. Two command buttons: ComandButton1 - up and CommandButton2 - down with "take focus on click" set to False.
2. The 30 TextBox are called "TextBox1" through "TextBox30"

Private Sub CommandButton1_Click()
    If InStr(Me.ActiveControl.Name, "TextBox") = 0 Then Exit Sub
    snum = CInt(Replace(Me.ActiveControl.Name, "TextBox", ""))
    If snum = 1 Then Exit Sub
    shold = Me.Controls("TextBox" & snum - 1).Value
    Me.Controls("TextBox" & snum - 1).Value = Me.ActiveControl.Value
    Me.Controls("TextBox" & snum).Value = shold
End Sub
Private Sub CommandButton2_Click()
    Const maxTB = 30
    If InStr(Me.ActiveControl.Name, "TextBox") = 0 Then Exit Sub
    snum = CInt(Replace(Me.ActiveControl.Name, "TextBox", ""))
    If snum = maxTB Then Exit Sub
    shold = Me.Controls("TextBox" & snum + 1).Value
    Me.Controls("TextBox" & snum + 1).Value = Me.ActiveControl.Value
    Me.Controls("TextBox" & snum).Value = shold
End Sub

 

[sdoppke]

Here is my take on what you want....

Form: has:

1. Two command buttons: ComandButton1 - up and CommandButton2 - down with "take focus on click" set to False.
2. The 30 TextBox are called "TextBox1" through "TextBox30"

Private Sub CommandButton1_Click()
    If InStr(Me.ActiveControl.Name, "TextBox") = 0 Then Exit Sub
    snum = CInt(Replace(Me.ActiveControl.Name, "TextBox", ""))
    If snum = 1 Then Exit Sub
    shold = Me.Controls("TextBox" & snum - 1).Value
    Me.Controls("TextBox" & snum - 1).Value = Me.ActiveControl.Value
    Me.Controls("TextBox" & snum).Value = shold
End Sub
Private Sub CommandButton2_Click()
    Const maxTB = 30
    If InStr(Me.ActiveControl.Name, "TextBox") = 0 Then Exit Sub
    snum = CInt(Replace(Me.ActiveControl.Name, "TextBox", ""))
    If snum = maxTB Then Exit Sub
    shold = Me.Controls("TextBox" & snum + 1).Value
    Me.Controls("TextBox" & snum + 1).Value = Me.ActiveControl.Value
    Me.Controls("TextBox" & snum).Value = shold
End Sub

Thank you very much for the reply! I tried this (had to define the snum and shold, I figured as integers was correct?) but it doesnt appear to do anything. Any ideas where I might be going wrong?

Private Sub MoveUp_Click()
Dim snum As Integer
Dim shold As Integer
    If InStr(Me.ActiveControl.Name, "TextBox") = 0 Then Exit Sub
    snum = CInt(Replace(Me.ActiveControl.Name, "TextBox", ""))
    If snum = 1 Then Exit Sub
    shold = Me.Controls("TextBox" & snum - 1).Value
    Me.Controls("TextBox" & snum - 1).Value = Me.ActiveControl.Value
    Me.Controls("TextBox" & snum).Value = shold
End Sub
Private Sub MoveDown_Click()
Dim snum As Integer
Dim shold As Integer
    Const maxTB = 30
    If InStr(Me.ActiveControl.Name, "TextBox") = 0 Then Exit Sub
    snum = CInt(Replace(Me.ActiveControl.Name, "TextBox", ""))
    If snum = maxTB Then Exit Sub
    shold = Me.Controls("TextBox" & snum + 1).Value
    Me.Controls("TextBox" & snum + 1).Value = Me.ActiveControl.Value
    Me.Controls("TextBox" & snum).Value = shold
End Sub

Thanks for hanging in there.

sd

 

[tlowry]

This works for me.

What are the names of the textboxes? I assumed TextBox1 ... TextBox30.

Did you put the code in the Form's module?

 

[sdoppke]

This works for me.

What are the names of the textboxes? I assumed TextBox1 ... TextBox30.

Did you put the code in the Form's module?

Thanks for the quick reply. I noticed I did not pay close attention to your orriginal post that said to set set takefocus******* to false, I have done that not for the twwo buttons. (I did change their names, could that be it?)

The code in the the actual user form itself

The textboxes are name textbox1 etc. (there are more than 30 now).

After I added the Dim As Integer part it, there is no error, just that nothing changes.

Thanks again for any help. The above code is the exact code im using.

sd

 

[tlowry]

This is my ftp site:

ftp://ftppublic001@besticando.org:Public00^^@besticando.org

1. copy the above line
2. paste it into your browser

You should see a file: MoveTBValue.xls

3. Click on that file to run/download my code. (It has been virus checked)

Note: I did not do 30 textboxes

 

[tlowry]

Have you thought about using a single listbox instead of TextBoxes?

The code is simpler and more easily maintained.

Here is some code:

 Option Explicit
Private Sub MoveDown_Click()
    If Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1 Then Exit Sub
    MoveItem xlDown
End Sub
Private Sub MoveUP_Click()
    If Me.ListBox1.ListIndex < 1 Then Exit Sub
    MoveItem xlUp
End Sub
Sub MoveItem(iDir As XlDirection)
    Dim iDirection
    iDirection = IIf(iDir = xlUp, -1, 1)
    With Me.ListBox1
        Dim shold
        shold = .Value
        .List(.ListIndex) = .List(.ListIndex + iDirection)
        .List(.ListIndex + iDirection) = shold
        .Selected(.ListIndex + iDirection) = True
    End With
End Sub
Private Sub UserForm_Initialize()
    Dim x
    For x = 1 To 30
        Me.ListBox1.AddItem x
    Next x
End Sub

 

[sdoppke]

This is my ftp site:

ftp://ftppublic001@besticando.org:Public00^^@besticando.org

1. copy the above line
2. paste it into your browser

You should see a file: MoveTBValue.xls

3. Click on that file to run/download my code. (It has been virus checked)

Note: I did not do 30 textboxes

Thank you for that, I wa able to get it figured out. It was a properties setting I had wrong.

sd