I'm kind of embarrassed, this isn't very elegant and it isn't done
yet, but I think it will work.
Public Sub A2_ZeroOutBalance()
Dim Acct As Variant
Dim StrEntry As String
Dim StrEntry2 As String
Dim MyMo As String
Dim MyEntryDate As String
' Get Entry Date
MyEntryDate = InputBox("What Entry Date?")
'Calculate MyMo
MyMo = MonthName(Month(MyEntryDate))
' Go thru each account
For Each Acct In Range("accounts")
'Get balance
StrEntry = WorksheetFunction.VLookup(CStr(Acct),
Range("acct_dbase"), 4, False)
' Zero out & Enter account and balance into ItemEntry
With ItemEntry
.TxtTaxClass = ""
.TxtFor = ""
.ListAccounts.Value = Acct
.TxtAmount.Value = StrEntry
.Repaint
End With
'=====================================================================
'I WANT TO COME BACK TO THIS SPOT IF THE USER CLICKS THE "ENTER"
BUTTON IN FORM "ITEMENTRY"
'IF HE CLICKS THE "SKIP" BUTTON I WANT TO GO LABEL NEXTFOR:, DOWN BELOW
'IF HE CLICKS THE "CANCEL" BUTTON I WANT TO EXIT THE SUB.
' THE ONLY PART I HAVE BEEN ABLE TO DO SO FAR IS THE "CANCEL"
'=====================================================================
'If ZeroOut, make entries for the account & "Special"
' Get last Cell in the database
Range("Home").Select
Selection.End(xlDown).Select
'Enter Account Info
With ActiveCell
.Range("A2").Value = Acct
.Range("B2").Value = MyEntryDate
.Range("C2").Value = "MEAdj"
.Range("D2").Value = MyEntryDate
.Range("E2").Value = MyMo & " MEAdj to/fm Special"
'Handle negative numbers
StrEntry2 = IIf(Left(StrEntry, 1) = "-", _
Right(StrEntry, Len(StrEntry) - 1), "-" & StrEntry)
.Range("F2").Value = StrEntry2
.Range("H2").Value = "Adj"
.Range("I2").Value = ItemEntry.TxtTaxClass.Value
.Range("J2").Value = ItemEntry.TxtFor
'Enter "Special" Info
.Range("A3").Value = "Special"
.Range("B3").Value = MyEntryDate
.Range("C3").Value = "MEAdj"
.Range("D3").Value = MyEntryDate
.Range("E3").Value = MyMo & " MEAdj to/fm " & Acct
.Range("F3").Value = StrEntry
.Range("H3").Value = "Adj"
.Offset(2, 0).Select
End With
' Redefine the data database
With ActiveWorkbook.Names("Entries")
.Name = "Entries"
.RefersTo = "=BUDGET!$A$4:$H$" & ActiveCell.Row
End With
With ActiveWorkbook.Names("DataBase")
.Name = "DataBase"
.RefersTo = "=BUDGET!$A$4:$H$" & ActiveCell.Row
End With
NextFor:
Next
End Sub