Add button to increment textbox value by 1

[ipbr21054]

Here is my userform code.

The textbox called CustomerID is where the number is stored.
I enter a number & hit enter on my keyboard then the values are shown in the other textboxes.

How can i add a button on the userform so when clicked it either adds 1 to the current value or takes away 1 from the current value & load its values.

Currently im doing it all manully & its very time consuming

Thanks

Private Sub CustomerID_AfterUpdate()
    Dim id As Variant, rowcount As Integer, foundcell As Range
    
    id = CustomerID.Value
    
    rowcount = Sheets("G INCOME").Cells(Rows.Count, 13).End(xlUp).Row ' THIS IS COLUMN NUMBER WHERE EMP ID LOCATED
    
    With Worksheets("G INCOME").Range("M1:M" & rowcount) ' THIS IS CELL REFERENCE OF WHERE THE TEXT EMP ID IS LOCATED
        Set foundcell = .Find(what:=id, LookIn:=xlValues)
        
        If Not foundcell Is Nothing Then
            TextBox1.Value = .Cells(foundcell.Row, 2)
            TextBox2.Value = .Cells(foundcell.Row, 3)
            TextBox3.Value = .Cells(foundcell.Row, 4)
            TextBox4.Value = .Cells(foundcell.Row, 5)
            TextBox5.Value = .Cells(foundcell.Row, 6)
        
         Else
          MsgBox "CUSTOMER'S ID IS INCORRECT", vbCritical, "CUSTOMER ID IS INCORRECT MESSAGE"
        
    End If
    End With
End Sub
    Private Sub TransferValues_Click()
    Dim Lastrow        As Long, i As Long
    Dim wsGIncome      As Worksheet
    Dim arr(1 To 5)    As Variant
    Dim Prompt         As String
   
    Set wsGIncome = ThisWorkbook.Worksheets("G INCOME")
   
    For i = 1 To UBound(arr)
        arr(i) = Choose(i, TextBox1.Value, TextBox2.Value, TextBox3.Value, TextBox4.Value, TextBox5.Value)
       
        If Len(arr(i)) = 0 Then
            MsgBox "YOU MUST COMPLETE ALL THE FIELDS", vbCritical, "USERFORM FIELDS EMPTY MESSAGE"
            Exit Sub
        End If
    Next i
   
    Application.ScreenUpdating = False
   
    With wsGIncome
        Lastrow = .Cells(.Rows.Count, "N").End(xlUp).Row + 1
       
       With .Cells(Lastrow, 14).Resize(, UBound(arr))
            .Value = arr
            .Font.Name = "Calibri"
            .Font.Size = 11
            .Font.Bold = True
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlCenter
            .Borders.Weight = xlThin
            .Interior.ColorIndex = 6
           
            .Cells(1, 1).HorizontalAlignment = xlLeft
            Application.ErrorCheckingOptions.BackgroundChecking = False
       End With
           With Sheets("G INCOME")
           If .AutoFilterMode Then .AutoFilterMode = False
        x = .Cells(.Rows.Count, 1).End(xlUp).Row
        .Range("N4:S" & x).Sort Key1:=Range("N4"), Order1:=xlAscending, Header:=xlGuess
           End With
         Unload SAMECUSTOMER
         .Range("N4").Select
       End With
       Application.ScreenUpdating = True
End Sub

 

[eiloken]

maybe you should try SpinButton with SpinUp and SpinDown event:

Private Sub SpinButton1_SpinDown()
    CustomerID.Value = CustomerID.Value - 1
End Sub

Private Sub SpinButton1_SpinUp()
    CustomerID.Value = CustomerID.Value + 1
End Sub

 

[ipbr21054]

UPDATE
Currently to scroll through the ID on the form i have to do this.
I enter 1 in the textbox then press enter on keyboard,now customers values are shows for ID 1
I can then enter 16 in the textbox then press enter on the keyboard, now the values are shown for ID 16

I have now added a command button on the userform & applied this code which increments the textbox value by 1 each time item is pressed

Private Sub CommandButton1_Click()
Me.CustomerID.Value = Me.CustomerID.Value + 1
End Sub

maybe you should try SpinButton with SpinUp and SpinDown event:

Private Sub SpinButton1_SpinDown()
    CustomerID.Value = CustomerID.Value - 1
End Sub

Private Sub SpinButton1_SpinUp()
    CustomerID.Value = CustomerID.Value + 1
End Sub

 

[eiloken]

so you resolved your issue? just add or subtract 1 to textbox value and used textbox change event to reupdate value by ID, i saw no problem here

 

[ipbr21054]

so you resolved your issue? just add or subtract 1 to textbox value and used textbox change event to reupdate value by ID, i saw no problem here

50 %

Yes i know can add or subtract 1 BUT i need to add enter in vba like when i do manually i enter ID 33 then press enter on keyboard.

Now i need to do same on userform.
I press +1 command button to get to required ID BUT i need to code for enter option so ID and its values are shown

 

[Artik]

Using the solution suggested by eiloken, there should still be a connection between the TextBox and the SpinButton.
ipbr21054, remove the CustomerID_AfterUpdate procedure for now, and insert instead:

Private Sub CustomerID_Change()
    Dim id As Variant, rowcount As Long, foundcell As Range
    Dim lRow As Long
    Dim i As Long
    
    id = CustomerID.Value
    SpinButton1.Value = id

    rowcount = Sheets("G INCOME").Cells(Rows.Count, 13).End(xlUp).Row    ' THIS IS COLUMN NUMBER WHERE EMP ID LOCATED


    With Worksheets("G INCOME").Range("M1:M" & rowcount)    ' THIS IS CELL REFERENCE OF WHERE THE TEXT EMP ID IS LOCATED
        Set foundcell = .Find(what:=id, LookIn:=xlValues)
    
        If Not foundcell Is Nothing Then
            lRow = foundcell.Row
            For i = 1 To 5
                Me.Controls("TextBox" & i).Value = .Cells(lRow, i + 1)
            Next i

        Else
            'MsgBox "CUSTOMER'S ID IS INCORRECT", vbCritical, "CUSTOMER ID IS INCORRECT MESSAGE"
            For i = 1 To 5
                Me.Controls("TextBox" & i).Value = vbNullString
            Next i
        End If
    End With

End Sub

SpinButton1_SpinDown and SpinButton1_SpinDown are of course also needed.

Artik

 

[ipbr21054]

Im confused even more now.
I HAVE GOT SPIN BUTTON ON MY FORM.
I asked how to put it on my form

 

[ipbr21054]

Im now getting somewhere leave it with me 2 moments please

 

[ipbr21054]

Looks to be working all ok now apart from 1 issue.

I have a command button which clears values in Textboxes.

This was the code in use when using the code above you said to change.

Private Sub ClearValues_Click()

CustomerID = ""
TextBox1 = ""
TextBox2 = ""
TextBox3 = ""
TextBox4 = ""
TextBox5 = ""
CustomerID.SetFocus

End Sub

With the new code advise in post 6 i now receive a RTE Type Mismatch

When i decug the issue this line of code is shown in Yellow
SpinButton1.Value = id

 

[ipbr21054]

Also with new code in post 6 i see now if the ID number is not found as opposed to seeing a message box i just get a RTE 380
See code below.

As opposed to seeing the line of code in Red for msgbox i am just taken to the line of code in BLUE

Private Sub CustomerID_Change()
    Dim id As Variant, rowcount As Long, foundcell As Range
    Dim lRow As Long
    Dim i As Long
    
    id = CustomerID.Value
    SpinButton1.Value = id

    rowcount = Sheets("G INCOME").Cells(Rows.Count, 13).End(xlUp).Row    ' THIS IS COLUMN NUMBER WHERE CUSTOMER ID IS LOCATED


    With Worksheets("G INCOME").Range("M1:M" & rowcount)    ' THIS IS CELL REFERENCE OF WHERE THE TEXT CUSTOMER ID IS LOCATED
        Set foundcell = .Find(what:=id, LookIn:=xlValues)
    
        If Not foundcell Is Nothing Then
            lRow = foundcell.Row
            For i = 1 To 5
                Me.Controls("TextBox" & i).Value = .Cells(lRow, i + 1)
            Next i

        Else
            'MsgBox "CUSTOMER'S ID IS INCORRECT", vbCritical, "CUSTOMER ID IS INCORRECT MESSAGE"
            For i = 1 To 5
                Me.Controls("TextBox" & i).Value = vbNullString
            Next i
        End If
    End With

End Sub