Running Balance Calculation

AsifShah

New Member
Joined
Feb 12, 2021
Messages
15
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
I was Created a userform for invoice control. in this userform i was created 3 Textbox.
Textbox1 For Old Running Balance
Textbox 2 For Current Bill
Textbox 3 For Result,
If Customer running Balance in Debit then Texbox1 Value + Textbox2 Value show result in Textbox3 and if Customer Running Balance is Credit then Textbox1 Value - Textbox2 Value and show the Result in Textbox3. Please Help
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

AlphaFrog

MrExcel MVP
Joined
Sep 2, 2009
Messages
16,425
Cross-posted
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
What do you mean by "Running Balance in Debit" and "Running Balance in Credit"???
Does it means Running Balance is + and Running Balance is -, respectively???
 

AsifShah

New Member
Joined
Feb 12, 2021
Messages
15
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows

ADVERTISEMENT

What do you mean by "Running Balance in Debit" and "Running Balance in Credit"???
Does it means Running Balance is + and Running Balance is -, respectively???
Running Balance in Debit = " - "
Running Balance in Debit" = " + "
 

AsifShah

New Member
Joined
Feb 12, 2021
Messages
15
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Running Balance in Debit = " - "
Running Balance in Credit = " + "
What do you mean by "Running Balance in Debit" and "Running Balance in Credit"???
Does it means Running Balance is + and Running Balance is -, respectively???
 

fadee2

Active Member
Joined
Nov 7, 2020
Messages
367
Office Version
  1. 2019
Platform
  1. Windows
Replace your code with the one below and see if it works


VBA Code:
Private Sub Code_AfterUpdate()
'Lookup values based on first control
With Me
.Dist = Application.WorksheetFunction.VLookup(CLng(Me.Code), Sheet3.Range("Subdealer"), 2, 0)
.TextBox2 = Application.WorksheetFunction.VLookup(CLng(Me.Code), Sheet3.Range("Subdealer"), 5, 0)

End With
If TextBox1.Text = "" And TextBox2.Text < 0 Then
    TextBox3.Text = TextBox2.Text
Else
    If TextBox1.Text = "" And TextBox2.Text > 0 Then
        TextBox3.Text = TextBox2.Text
    Else
        If TextBox2.Text < 0 Then
            Label186.Caption = "Old Credit"
            TextBox3.Text = Val(TextBox1.Text) + Val(Abs(TextBox2.Text))
        Else
            Label186.Caption = "Old Debit"
            TextBox3.Text = Val(TextBox1.Text) - Val(TextBox2.Text)
        End If
    End If
End If
End Sub
 
Solution

AsifShah

New Member
Joined
Feb 12, 2021
Messages
15
Office Version
  1. 2016
  2. 2013
Platform
  1. Windows
Replace your code with the one below and see if it works


VBA Code:
Private Sub Code_AfterUpdate()
'Lookup values based on first control
With Me
.Dist = Application.WorksheetFunction.VLookup(CLng(Me.Code), Sheet3.Range("Subdealer"), 2, 0)
.TextBox2 = Application.WorksheetFunction.VLookup(CLng(Me.Code), Sheet3.Range("Subdealer"), 5, 0)

End With
If TextBox1.Text = "" And TextBox2.Text < 0 Then
    TextBox3.Text = TextBox2.Text
Else
    If TextBox1.Text = "" And TextBox2.Text > 0 Then
        TextBox3.Text = TextBox2.Text
    Else
        If TextBox2.Text < 0 Then
            Label186.Caption = "Old Credit"
            TextBox3.Text = Val(TextBox1.Text) + Val(Abs(TextBox2.Text))
        Else
            Label186.Caption = "Old Debit"
            TextBox3.Text = Val(TextBox1.Text) - Val(TextBox2.Text)
        End If
    End If
End If
End Sub
Thanks Alot Brother :) Its Work for me :)
 

Forum statistics

Threads
1,136,787
Messages
5,677,731
Members
419,716
Latest member
MPunt

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top