Logo 
Search:

MS Office Answers

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds
  Question Asked By: Annie Russell   on Oct 09 In MS Office Category.

  
Question Answered By: Leo Evans   on Oct 09

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

Share: 

 

This Question has 4 more answer(s). View Complete Question Thread

 
Didn't find what you were looking for? Find more on Returning to a Sub from a form Or get search suggestion and latest updates.


Tagged: