bostenson
01-27-2021, 02:37 PM
Chalk it up to my still juvenile experience when it comes to vba; I am attempting to build a userform which allows me to record various budget across 5 (five) distinct sheets based on the value in a combobox. But the data is not being saved in the next empty row in the sheet. I'm missing something in my last row code, what is it? Thanks in advance.
Private Sub cmdSaveAddBudItem_Click()
'Validation
If Me.cmbCategory.Value = "" Then
MsgBox "Please enter a budget item Category.", vbCritical
Exit Sub
End If
If Me.txbEnterBudgetItemName.Value = "" Then
MsgBox "Please enter a Budget Item Name.", vbCritical
Exit Sub
End If
Dim Ch As Worksheet
Set Ch = ThisWorkbook.Sheets("Checking")
Dim CC As Worksheet
Set CC = ThisWorkbook.Sheets("CreditCards")
Dim Ex As Worksheet
Set Ex = ThisWorkbook.Sheets("Expenses")
Dim Li As Worksheet
Set Li = ThisWorkbook.Sheets("Liabilities")
Dim Sa As Worksheet
Set Sa = ThisWorkbook.Sheets("Savings")
Dim lr As Long
' Checking for Duplicates
' Checking
If Application.WorksheetFunction.CountIf(Ch.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
Exit Sub
End If
lr = Application.WorksheetFunction.CountA(Ch.Range("A:A"))
'Credit Card
If Application.WorksheetFunction.CountIf(CC.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
Exit Sub
End If
lr = Application.WorksheetFunction.CountA(CC.Range("A:A"))
' Expense
If Application.WorksheetFunction.CountIf(Ex.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
Exit Sub
End If
lr = Application.WorksheetFunction.CountA(Ex.Range("A:A"))
' Liabilities
If Application.WorksheetFunction.CountIf(Li.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
Exit Sub
End If
lr = Application.WorksheetFunction.CountA(Li.Range("A:A"))
' Savings
If Application.WorksheetFunction.CountIf(Sa.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
Exit Sub
End If
lr = Application.WorksheetFunction.CountA(Sa.Range("A:A"))
' Add Budget Item
If Me.cmbCategory.Value = "Checking" Then
Ch.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
Ch.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
ElseIf Me.cmbCategory.Value = "Credit Card" Then
CC.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
CC.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
ElseIf Me.cmbCategory.Value = "Expense" Then
Ex.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
Ex.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
Ex.Range("C" & lr + 1).Value = Me.txbBudgetTotalDue.Value
Ex.Range("D" & lr + 1).Value = Me.cmbTermCode.Value
Ex.Range("E" & lr + 1).Value = Me.cbxAutomaticWithdrawal.Value
ElseIf Me.cmbCategory.Value = "Liability" Then
Li.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
Li.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
Li.Range("C" & lr + 1).Value = Me.txbBudgetTotalDue.Value
Li.Range("D" & lr + 1).Value = Me.cmbTermCode.Value
Li.Range("E" & lr + 1).Value = Me.cbxAutomaticWithdrawal.Value
Else
Me.cmbCategory.Value = "Saving"
Sa.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
Sa.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
End If
'Clear Add Budget Item
Me.cmbCategory.Value = ""
Me.txbEnterBudgetItemName.Value = ""
Me.txbBudgetBeginBal.Value = ""
Me.txbBudgetTotalDue.Value = ""
Me.cmbTermCode.Value = ""
Me.cbxAutomaticWithdrawal.Value = False
MsgBox "Budget Item has been added.", vbInformation
End Sub
Private Sub cmdSaveAddBudItem_Click()
'Validation
If Me.cmbCategory.Value = "" Then
MsgBox "Please enter a budget item Category.", vbCritical
Exit Sub
End If
If Me.txbEnterBudgetItemName.Value = "" Then
MsgBox "Please enter a Budget Item Name.", vbCritical
Exit Sub
End If
Dim Ch As Worksheet
Set Ch = ThisWorkbook.Sheets("Checking")
Dim CC As Worksheet
Set CC = ThisWorkbook.Sheets("CreditCards")
Dim Ex As Worksheet
Set Ex = ThisWorkbook.Sheets("Expenses")
Dim Li As Worksheet
Set Li = ThisWorkbook.Sheets("Liabilities")
Dim Sa As Worksheet
Set Sa = ThisWorkbook.Sheets("Savings")
Dim lr As Long
' Checking for Duplicates
' Checking
If Application.WorksheetFunction.CountIf(Ch.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
Exit Sub
End If
lr = Application.WorksheetFunction.CountA(Ch.Range("A:A"))
'Credit Card
If Application.WorksheetFunction.CountIf(CC.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
Exit Sub
End If
lr = Application.WorksheetFunction.CountA(CC.Range("A:A"))
' Expense
If Application.WorksheetFunction.CountIf(Ex.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
Exit Sub
End If
lr = Application.WorksheetFunction.CountA(Ex.Range("A:A"))
' Liabilities
If Application.WorksheetFunction.CountIf(Li.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
Exit Sub
End If
lr = Application.WorksheetFunction.CountA(Li.Range("A:A"))
' Savings
If Application.WorksheetFunction.CountIf(Sa.Range("A:A"), Me.txbEnterBudgetItemName.Value) > 0 Then
MsgBox "This Budget Item Name already exists, please enter a new name.", vbOKOnly
Exit Sub
End If
lr = Application.WorksheetFunction.CountA(Sa.Range("A:A"))
' Add Budget Item
If Me.cmbCategory.Value = "Checking" Then
Ch.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
Ch.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
ElseIf Me.cmbCategory.Value = "Credit Card" Then
CC.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
CC.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
ElseIf Me.cmbCategory.Value = "Expense" Then
Ex.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
Ex.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
Ex.Range("C" & lr + 1).Value = Me.txbBudgetTotalDue.Value
Ex.Range("D" & lr + 1).Value = Me.cmbTermCode.Value
Ex.Range("E" & lr + 1).Value = Me.cbxAutomaticWithdrawal.Value
ElseIf Me.cmbCategory.Value = "Liability" Then
Li.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
Li.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
Li.Range("C" & lr + 1).Value = Me.txbBudgetTotalDue.Value
Li.Range("D" & lr + 1).Value = Me.cmbTermCode.Value
Li.Range("E" & lr + 1).Value = Me.cbxAutomaticWithdrawal.Value
Else
Me.cmbCategory.Value = "Saving"
Sa.Range("A" & lr + 1).Value = Me.txbEnterBudgetItemName.Value
Sa.Range("B" & lr + 1).Value = Me.txbBudgetBeginBal.Value
End If
'Clear Add Budget Item
Me.cmbCategory.Value = ""
Me.txbEnterBudgetItemName.Value = ""
Me.txbBudgetBeginBal.Value = ""
Me.txbBudgetTotalDue.Value = ""
Me.cmbTermCode.Value = ""
Me.cbxAutomaticWithdrawal.Value = False
MsgBox "Budget Item has been added.", vbInformation
End Sub