Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim wsCaseNo As Worksheet
Dim wsDate As Worksheet
Dim wsCredit As Worksheet
Dim wsBalance As Worksheet
Dim wsRemarks As Worksheet
Dim lastRow, lastRowSummary As Long
Set ws = ThisWorkbook.Sheets("Summary")
Set wsCaseNo = ThisWorkbook.Sheets("Case No")
Set wsDate = ThisWorkbook.Sheets("Date")
Set wsCredit = ThisWorkbook.Sheets("Credit")
Set wsBalance = ThisWorkbook.Sheets("Balance")
Set wsRemarks = ThisWorkbook.Sheets("Remarks")
lastRow = wsCaseNo.Cells(wsCaseNo.Rows.Count, 1).End(xlUp).Row + 1
lastRowSummary = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
wsCaseNo.Cells(lastRow, 1).Value = txtCaseNo.Value
wsDate.Cells(lastRow, 1).Value = txtDate.Value
wsCredit.Cells(lastRow, 1).Value = txtCredit.Value
'wsBalance.Cells(lastRow, 1).Value = txtBalance.Value
wsRemarks.Cells(lastRow, 1).Value = txtRemarks.Value
With ws
.Cells(lastRowSummary, 1).Value = txtCaseNo.Value
.Cells(lastRowSummary, 2).Value = txtDate.Value
.Cells(lastRowSummary, 6).Value = txtCredit.Value
'.Cells(lastRowSummary, 11).Value = txtBalance.Value
.Cells(lastRowSummary, 12).Value = txtRemarks.Value
End With
Call CalculateBalance
ws.Cells(lastRowSummary, 11).Value = txtBalance.Value
wsBalance.Cells(lastRow, 1).Value = txtBalance.Value
If CInt(Me.txtBalance.Value) < 0 Then
Call AddCredit
End If
Call SetDisbursalAmount
Call ClearForm
MsgBox "Data submitted successfully.", vbInformation
End Sub
Sub ClearForm()
txtCaseNo.Value = ""
txtDate.Value = ""
txtCredit.Value = ""
txtAmount.Value = ""
txtBalance.Value = ""
txtRemarks.Value = ""
cmdDisbursalParty.Clear
End Sub
Private Sub txtAmount_Change()
On Error Resume Next
Me.txtBalance.Value = Me.txtCredit.Value - Me.txtAmount
' If Me.txtBalance.Value < 0 Then
' Call Me.CalculateBalance
' End If
End Sub
'Private Sub txtBalance_Change()
'
' If Right(Me.txtBalance.Value, 1) = "-" Then
' Call CalculateBalance
' End If
'
'End Sub
Private Sub txtCredit_Change()
Dim summarySheet As Worksheet
Dim lastRow As Integer
Set summarySheet = ThisWorkbook.Sheets("Summary")
lastRow = summarySheet.Cells(summarySheet.Rows.Count, 1).End(xlUp).Row
If lastRow <= 2 Then
Me.txtBalance.Value = Me.txtCredit.Value
End If
Me.txtBalance.Value = Me.txtCredit.Value
End Sub
Private Sub UserForm_Initialize()
Dim summarySheet As Worksheet
Dim valueRange As Range
Dim valueArray As Variant
Dim i, lastRow As Integer
Set summarySheet = ThisWorkbook.Sheets("Summary")
Set valueRange = summarySheet.Range("G2:J2")
valueArray = valueRange.Value
lastRow = summarySheet.Cells(summarySheet.Rows.Count, 1).End(xlUp).Row
Me.cmdDisbursalParty.Clear
For i = LBound(valueArray, 2) To UBound(valueArray, 2)
Me.cmdDisbursalParty.AddItem valueArray(1, i)
Next i
If lastRow <= 2 Then
Me.Label3.Caption = "Initial Credit"
Me.txtCredit.Enabled = True
Else
Me.txtCredit.Enabled = False
Me.txtCredit.Value = summarySheet.Range("K" & lastRow).Value
End If
End Sub
Sub SetDisbursalAmount()
Dim selectedValue As String
Dim p1Sheet, p2Sheet, p3Sheet, p4Sheet, summary As Worksheet
Dim lastRow, lastRowSummary As Long
Set p1Sheet = ThisWorkbook.Sheets("Disbursal to P1")
Set p2Sheet = ThisWorkbook.Sheets("Disbursal to P2")
Set p3Sheet = ThisWorkbook.Sheets("Disbursal to P3")
Set p4Sheet = ThisWorkbook.Sheets("Disbursal to P4")
Set summary = ThisWorkbook.Sheets("Summary")
selectedValue = Me.cmdDisbursalParty.Value
lastRowSummary = summary.Cells(summary.Rows.Count, 1).End(xlUp).Row
If selectedValue = "P1" Then
lastRow = p1Sheet.Cells(p1Sheet.Rows.Count, 1).End(xlUp).Row + 1
p1Sheet.Cells(lastRow, 1).Value = Me.txtAmount.Value
summary.Cells(lastRowSummary, 7).Value = Me.txtAmount.Value
ElseIf selectedValue = "P2" Then
lastRow = p2Sheet.Cells(p2Sheet.Rows.Count, 1).End(xlUp).Row + 1
p2Sheet.Cells(lastRow, 1).Value = Me.txtAmount.Value
summary.Cells(lastRowSummary, 8).Value = Me.txtAmount.Value
ElseIf selectedValue = "P3" Then
lastRow = p3Sheet.Cells(p3Sheet.Rows.Count, 1).End(xlUp).Row + 1
p3Sheet.Cells(lastRow, 1).Value = Me.txtAmount.Value
summary.Cells(lastRowSummary, 9).Value = Me.txtAmount.Value
ElseIf selectedValue = "P4" Then
lastRow = p4Sheet.Cells(p4Sheet.Rows.Count, 1).End(xlUp).Row + 1
p4Sheet.Cells(lastRow, 1).Value = Me.txtAmount.Value
summary.Cells(lastRowSummary, 10).Value = Me.txtAmount.Value
End If
End Sub
Sub CalculateBalance()
Dim lastRow As Long
Dim ws As Worksheet
Dim i As Long
Dim sumP As Double
Set ws = ThisWorkbook.Sheets("Summary")
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
sumP = ws.Cells(lastRow, 7).Value + ws.Cells(lastRow, 8).Value + ws.Cells(lastRow, 9).Value + ws.Cells(lastRow, 10).Value
' If (ws.Cells(lastRow, 6).Value - sumP) < 0 Then
'
' MsgBox "Add Credit!", vbInformation
' Call AddCredit
' Exit Sub
'
' End If
ws.Cells(lastRow, 11).Value = ws.Cells(lastRow, 6).Value - sumP
'ws.Cells(lastRow + 1, 6).Value = ws.Cells(lastRow, 11).Value
End Sub
Sub AddCredit()
Dim ws, wsCredit, wsBalance As Worksheet
Dim lastRow, lastRowCredit, lastRowBalance As Long
Dim inputValue As Long
Dim currentValue As Long
Dim temp As Long
Set ws = ThisWorkbook.Sheets("Summary")
Set wsCredit = ThisWorkbook.Sheets("Credit")
Set wsBalance = ThisWorkbook.Sheets("Balance")
lastRow = ws.Cells(Rows.Count, 1).End(xlUp).Row
lastRowCredit = wsCredit.Cells(Rows.Count, 1).End(xlUp).Row
lastRowBalance = wsBalance.Cells(Rows.Count, 1).End(xlUp).Row
currentValue = ws.Range("F" & lastRow).Value
inputValue = InputBox("Enter a value:")
If Not IsNumeric(inputValue) Then
MsgBox "Invalid input. Please enter a numeric value.", vbExclamation
Exit Sub
End If
ws.Range("F" & lastRow).Value = currentValue + inputValue
temp = ws.Range("K" & lastRow).Value
ws.Range("K" & lastRow).Value = ws.Range("K" & lastRow).Value + inputValue
wsCredit.Range("A" & lastRowCredit + 1).Value = inputValue
wsBalance.Range("A" & lastRowBalance + 1).Value = temp + inputValue
End Sub