Choose from different balances in VBA

**Jonathan78** · 05-01-2012, 02:36 AM

Hello Dave,

This is an example workbook

Book1.xlsm

**Jonathan78** · 05-01-2012, 08:01 AM

anyone????

**jaslake** · 05-01-2012, 10:44 AM

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

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

Hello John,

Thanks! it works for both samples I gave in my first post.
It does not work correctly however when I enter an amount less then the amount given in box1.