Choose from different balances in VBA

**Jonathan78** · 05-01-2012, 11:14 AM

The result should be as follows:

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

**jaslake** · 05-01-2012, 11:19 AM

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

**jaslake** · 05-01-2012, 11:32 AM

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