Dear all,
I am creating a ledger spreadsheet that posts journal entries. The format is the following:
Click the command button and an inputbox opens asking which of the three possible transaction types the user wants to do. In the code, I created a switch that allows me to choose OE, PMT, or CLS. If the user chooses an alternate type, a msgbox opens and tells the user of their failure and it stops the function. If the user chooses a correct type, they are prompted to give a dollar amount.
Here is where it gets tricky. If the user suddenly changes his/her mind and hits cancel, the function is not cancelled. In fact, the entry is posted, except at $0. Is there a way I can have the function halt if the cancel is chosen? For some reason, it worked in the first switch, but not the second.
Following is the code I have so far. (Go easy on me, I am a VBA newbie.)
Sub FunTran()
Application.ScreenUpdating = False
usermsg = InputBox("What Type of Transaction do you wish to enter? (OE: Addition or Withdrawl from the Business, PMT: A Payment to the principle lender; MBNA, or CLS: A Journal entry to close the Accounting Period.)")
Select Case usermsg
Case vcancel
Sheets("Financials").Range("a1").Select
Exit Sub
Case "PMT"
Sheets("Journal").Range("r22") = InputBox("How much do you wish to pay MBNA?")
Sheets("Journal").Range("k23:r24").Copy
Sheets("Journal").Select
Range("a3").End(xlDown).Select
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlValues
Sheets("Journal").Range("r22").Select
ActiveCell.ClearContents
Sheets("Financials").Select
Range("a1").Select
Case "OE"
Sheets("Journal").Select
Range("r25") = InputBox("How much to you wish to add or subtract from the business?")
Range("k26:r27").Copy
Range("a3").End(xlDown).Select
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlValues
Range("r25").ClearContents
Sheets("Financials").Select
Range("a1").Select
Case "CLS"
Sheets("Journal").Select
Range("k18:r21").Copy
Range("a3").End(xlDown).Select
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlValues
Sheets("Financials").Select
Range("a1").Select
Case Else
MsgBox ("Available Transactions are OE: Addition or Withdrawl from the Business, PMT: A Payment to the principle lender; MBNA, or CLS: A Journal entry to close the Accounting Period.")
Exit Sub
End Select
End Sub
Thank you in advance for your wonderful help. I have been an avid reader of this board for several days, but this is my first post.
-Jarrod
I am creating a ledger spreadsheet that posts journal entries. The format is the following:
Click the command button and an inputbox opens asking which of the three possible transaction types the user wants to do. In the code, I created a switch that allows me to choose OE, PMT, or CLS. If the user chooses an alternate type, a msgbox opens and tells the user of their failure and it stops the function. If the user chooses a correct type, they are prompted to give a dollar amount.
Here is where it gets tricky. If the user suddenly changes his/her mind and hits cancel, the function is not cancelled. In fact, the entry is posted, except at $0. Is there a way I can have the function halt if the cancel is chosen? For some reason, it worked in the first switch, but not the second.
Following is the code I have so far. (Go easy on me, I am a VBA newbie.)
Sub FunTran()
Application.ScreenUpdating = False
usermsg = InputBox("What Type of Transaction do you wish to enter? (OE: Addition or Withdrawl from the Business, PMT: A Payment to the principle lender; MBNA, or CLS: A Journal entry to close the Accounting Period.)")
Select Case usermsg
Case vcancel
Sheets("Financials").Range("a1").Select
Exit Sub
Case "PMT"
Sheets("Journal").Range("r22") = InputBox("How much do you wish to pay MBNA?")
Sheets("Journal").Range("k23:r24").Copy
Sheets("Journal").Select
Range("a3").End(xlDown).Select
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlValues
Sheets("Journal").Range("r22").Select
ActiveCell.ClearContents
Sheets("Financials").Select
Range("a1").Select
Case "OE"
Sheets("Journal").Select
Range("r25") = InputBox("How much to you wish to add or subtract from the business?")
Range("k26:r27").Copy
Range("a3").End(xlDown).Select
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlValues
Range("r25").ClearContents
Sheets("Financials").Select
Range("a1").Select
Case "CLS"
Sheets("Journal").Select
Range("k18:r21").Copy
Range("a3").End(xlDown).Select
ActiveCell.Offset(1, 0).PasteSpecial Paste:=xlValues
Sheets("Financials").Select
Range("a1").Select
Case Else
MsgBox ("Available Transactions are OE: Addition or Withdrawl from the Business, PMT: A Payment to the principle lender; MBNA, or CLS: A Journal entry to close the Accounting Period.")
Exit Sub
End Select
End Sub
Thank you in advance for your wonderful help. I have been an avid reader of this board for several days, but this is my first post.
-Jarrod