Choose from different balances in VBA

Hello All,

What is the best way to code the following

textbox1 shows the remaining hours of box1 [i.e. 48]
textbox2 shows the remaining hours of box2 [i.e. 32]
textbox3 shows the remaining hours of box3 [i.e. 8.5]

The user can put a number of hours in textbox4 that need to be extracted from the above boxes.

First box1 needs to be empties then 2 then 3.

So if the user puts 100 in textbox4 the new values will be as follows:

textbox1 shows the remaining hours of box1 [i.e. 0]
textbox2 shows the remaining hours of box2 [i.e. 0]
textbox3 shows the remaining hours of box3 [i.e. -11.5]

if the user puts 50 in textbox4 the new values will be as follows:

textbox1 shows the remaining hours of box1 [i.e. 0]
textbox2 shows the remaining hours of box2 [i.e. 30]
textbox3 shows the remaining hours of box3 [i.e. 8,5]

etc.

the 3 new values will be stored on sheet1 in column K below each other
Sheets("sheet1").Range("K" & LastRow).Value =
Sheets("sheet1").Range("K" & LastRow).Value =
Sheets("sheet1").Range("K" & LastRow).Value =

Any ideas?

It would be best to supply a sample workbook showing what your layout looks like.

Hi Jonathan
This code appears to work with your sample data

Private Sub CommandButton1_Click()
    Dim LastRow As Long
    Dim x As Double
    Dim y As Double
    Dim z As Double
    Dim zz As Double

    x = Me.TextBox4 - Me.TextBox1

    If x >= 0 Then
        y = x
        Me.TextBox1 = 0
    Else
        Me.TextBox1 = Me.TextBox1 - x
    End If

    If y >= 0 And y >= Me.TextBox2 Then
        z = Me.TextBox2 - y
        zz = z
        Me.TextBox2 = 0
    Else
        Me.TextBox2 = Me.TextBox2 - y
    End If
    
    If zz <= 0 Then
        Me.TextBox3 = Me.TextBox3 + zz
    End If
    
    LastRow = Sheets("Sheet1").Range("K65536").End(xlUp).Row + 1

    Sheets("Sheet1").Range("K" & LastRow).Value = Me.TextBox1.Value
    Sheets("Sheet1").Range("K" & LastRow + 1).Value = Me.TextBox2.Value
    Sheets("Sheet1").Range("K" & LastRow + 2).Value = Me.TextBox3.Value

End Sub

Hi Jonathan

Hold off on that...needs a small correction!!!

Add these few lines of code

Option Explicit

Private Sub CommandButton1_Click()
    Dim LastRow As Long
    Dim x As Double
    Dim y As Double
    Dim z As Double
    Dim zz As Double

    '-------------------------------------
    ' Add these lines of code
    If Me.TextBox4 <= Me.TextBox1 Then
        Me.TextBox1 = Me.TextBox1 - Me.TextBox4
        Exit Sub
    End If
    '-------------------------------------
     
    x = Me.TextBox4 - Me.TextBox1

    If x >= 0 Then
        y = x
        Me.TextBox1 = 0
    Else
        Me.TextBox1 = Me.TextBox1 - x
    End If

    If y >= 0 And y >= Me.TextBox2 Then
        z = Me.TextBox2 - y
        zz = z
        Me.TextBox2 = 0
    Else
        Me.TextBox2 = Me.TextBox2 - y
    End If

    If zz <= 0 Then
        Me.TextBox3 = Me.TextBox3 + zz
    End If

    LastRow = Sheets("Sheet1").Range("K65536").End(xlUp).Row + 1

    Sheets("Sheet1").Range("K" & LastRow).Value = Me.TextBox1.Value
    Sheets("Sheet1").Range("K" & LastRow + 1).Value = Me.TextBox2.Value
    Sheets("Sheet1").Range("K" & LastRow + 2).Value = Me.TextBox3.Value

End Sub

Hi Jonathan

Try this instead

Option Explicit

Private Sub CommandButton1_Click()
    Dim LastRow As Long
    Dim x As Double
    Dim y As Double
    Dim z As Double
    Dim zz As Double

    '-------------------------------------
    ' Add these lines of code
    If Val(Me.TextBox4) <= Val(Me.TextBox1) Then
        Me.TextBox1 = Me.TextBox1 - Me.TextBox4
        GoTo EarlyExit
    End If
    '-------------------------------------

    x = Me.TextBox4 - Me.TextBox1

    If x >= 0 Then
        y = x
        Me.TextBox1 = 0
    Else
        Me.TextBox1 = Me.TextBox1 - x
    End If

    If y >= 0 And y >= Me.TextBox2 Then
        z = Me.TextBox2 - y
        zz = z
        Me.TextBox2 = 0
    Else
        Me.TextBox2 = Me.TextBox2 - y
    End If

    If zz <= 0 Then
        Me.TextBox3 = Me.TextBox3 + zz
    End If

    '-------------------------------------
    ' Add these lines of code
EarlyExit:
    '-------------------------------------

    LastRow = Sheets("Sheet1").Range("K65536").End(xlUp).Row + 1

    Sheets("Sheet1").Range("K" & LastRow).Value = Me.TextBox1.Value
    Sheets("Sheet1").Range("K" & LastRow + 1).Value = Me.TextBox2.Value
    Sheets("Sheet1").Range("K" & LastRow + 2).Value = Me.TextBox3.Value

End Sub

Hi Jonathan

You're welcome...glad to be of help. If that satisfies your requirement, please mark your Thread as solved.