I have made a ledger sub in Excel. The task is to create a spreadsheet solution for a foreign exchange bureau so each transaction that happens it adds the details to a ledger sheet.
For this I have this code:
Expand|Select|Wrap|Line Numbers
- Sub ledge()
- Dim r As Excel.Range
- Dim i As Integer
- Dim p As Excel.Range
- Dim q As Excel.Range
- If MsgBox("Are you sure you want to make this transaction?", _
- vbYesNo, "System Message") = vbNo Then Exit Sub
- i = Worksheets("Home").Range("G64").Value
- Set r = Worksheets("ledger").Range("B1").End(xlDown).Offset(1).EntireRow.Cells
- Set p = Worksheets("stock").Range("B1").End(xlDown).Offset(1).EntireRow.Cells
- With Worksheets("Home")
- If WorksheetFunction.CountA(r) <> 0 Then Exit Sub
- If ((Worksheets("stock").Range("C9").Value - Worksheets("Home").Range("E25").Value) <= 0) Then
- If MsgBox("The number of GBP in stock is too low to make this transaction. Do you want to order some more?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("F22").Value = (10000 - Worksheets("stock").Range("C9").Value)
- Worksheets("stock").Range("C9").Value = 10000
- Else: MsgBox "Transaction Cancelled", vbOKOnly, "System Message"
- Exit Sub
- End If
- End If
- r(i, "B").Value = "FXB" & .Range("G64").Value - 3
- r(i, "C").Value = Now()
- r(i, "E").Value = "GBP"
- r(i, "D").Value = .Range("E12").Value
- r(i, "G").Value = .Range("E14").Value
- r(i, "J").Value = .Range("E25").Value
- r(i, "M").Value = .Range("E20").Value
- r(i, "P").Value = .Range("E9").Value
- With Worksheets("Home")
- If Worksheets("Home").Range("E12").Value = "USD" Then
- p(3, "C").Value = p(3, "C").Value + .Range("E14").Value
- p(2, "C").Value = p(2, "C").Value - .Range("E25").Value
- p(15, "H").Value = p(15, "H").Value + .Range("E20").Value
- p(15, "C").Value = p(15, "C").Value + .Range("E25").Value
- p(16, "D").Value = p(16, "D").Value + .Range("E14").Value
- ElseIf Worksheets("Home").Range("E12").Value = "EUR" Then
- p(4, "C").Value = p(4, "C").Value + .Range("E14").Value
- p(2, "C").Value = p(2, "C").Value - .Range("E25").Value
- p(15, "H").Value = p(15, "H").Value + .Range("E20").Value
- p(15, "C").Value = p(15, "C").Value + .Range("E25").Value
- p(17, "D").Value = p(17, "D").Value + .Range("E14").Value
- ElseIf Worksheets("Home").Range("E12").Value = "YEN" Then
- p(5, "C").Value = p(5, "C").Value + .Range("E14").Value
- p(2, "C").Value = p(2, "C").Value - .Range("E25").Value
- p(15, "H").Value = p(15, "H").Value + .Range("E20").Value
- p(15, "C").Value = p(15, "C").Value + .Range("E25").Value
- p(18, "D").Value = p(18, "D").Value + .Range("E14").Value
- ElseIf Worksheets("Home").Range("E12").Value = "CAD" Then
- p(6, "C").Value = p(6, "C").Value + .Range("E14").Value
- p(2, "C").Value = p(2, "C").Value - .Range("E25").Value
- p(15, "H").Value = p(15, "H").Value + .Range("E20").Value
- p(15, "C").Value = p(15, "C").Value + .Range("E25").Value
- p(19, "D").Value = p(19, "D").Value + .Range("E14").Value
- Else
- p(7, "C").Value = p(7, "C").Value + .Range("E14").Value
- p(2, "C").Value = p(2, "C").Value - .Range("E25").Value
- p(15, "H").Value = p(15, "H").Value + .Range("E20").Value
- p(15, "C").Value = p(15, "C").Value + .Range("E25").Value
- p(20, "D").Value = p(20, "D").Value + .Range("E14").Value
- End If
- MsgBox "The ledger and stock totals have been updated.", vbOKOnly, "System Message"
- Application.Goto Worksheets("Home").Range("A1")
- Worksheets("Home").Range("G64").Value = Worksheets("Home").Range("G64").Value + 1
- End With
- End With
- If Worksheets("stock").Range("C9").Value <= 1000 Then
- If MsgBox("The number of GBP in stock is too low. Do you want to order some more?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("F22").Value = (10000 - Worksheets("stock").Range("C9").Value)
- Worksheets("stock").Range("C9").Value = 10000
- Else: Exit Sub
- End If
- End If
- If Worksheets("stock").Range("C10").Value <= 1000 Then
- If MsgBox("The number of USD in stock is too low. Do you want to order some more?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("F23").Value = (10000 - Worksheets("stock").Range("C10").Value)
- Worksheets("stock").Range("C10").Value = 10000
- Else: Exit Sub
- End If
- End If
- If Worksheets("stock").Range("C11").Value <= 1000 Then
- If MsgBox("The number of EUR in stock is too low. Do you want to order some more?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("F24").Value = (10000 - Worksheets("stock").Range("C11").Value)
- Worksheets("stock").Range("C11").Value = 10000
- Else: Exit Sub
- End If
- End If
- If Worksheets("stock").Range("C12").Value <= 1000 Then
- If MsgBox("The number of YEN in stock is too low. Do you want to order some more?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("F25").Value = (10000 - Worksheets("stock").Range("C12").Value)
- Worksheets("stock").Range("C12").Value = 10000
- Else: Exit Sub
- End If
- End If
- If Worksheets("stock").Range("C13").Value <= 1000 Then
- If MsgBox("The number of CAD in stock is too low. Do you want to order some more?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("F26").Value = (10000 - Worksheets("stock").Range("C13").Value)
- Worksheets("stock").Range("C13").Value = 10000
- Else: Exit Sub
- End If
- End If
- If Worksheets("stock").Range("C14").Value <= 1000 Then
- If MsgBox("The number of AUD in stock is too low. Do you want to order some more?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("F27").Value = (10000 - Worksheets("stock").Range("C14").Value)
- Worksheets("stock").Range("C14").Value = 10000
- Else: Exit Sub
- End If
- End If
- 'next step
- If Worksheets("stock").Range("C9").Value >= 15000 Then
- If MsgBox("The number of GBP in stock is too high. Holding too much money can pose as a security risk! Do you wish to release some of these funds?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("E22").Value = (Worksheets("stock").Range("C9").Value - 10000)
- Worksheets("stock").Range("C9").Value = 10000
- Else: Exit Sub
- End If
- End If
- If Worksheets("stock").Range("C10").Value >= 15000 Then
- If MsgBox("The number of USD in stock is too high. Holding too much money can pose as a security risk! Do you wish to release some of these funds?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("E23").Value = (Worksheets("stock").Range("C10").Value - 10000)
- Worksheets("stock").Range("C10").Value = 10000
- Else: Exit Sub
- End If
- End If
- If Worksheets("stock").Range("C11").Value >= 15000 Then
- If MsgBox("The number of EUR in stock is too high. Holding too much money can pose as a security risk! Do you wish to release some of these funds?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("E24").Value = (Worksheets("stock").Range("C11").Value - 10000)
- Worksheets("stock").Range("C11").Value = 10000
- Else: Exit Sub
- End If
- If Worksheets("stock").Range("C12").Value >= 15000 Then
- If MsgBox("The number of YEN in stock is too high. Holding too much money can pose as a security risk! Do you wish to release some of these funds?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("E25").Value = (Worksheets("stock").Range("C12").Value - 10000)
- Worksheets("stock").Range("C12").Value = 10000
- Else: Exit Sub
- End If
- End If
- If Worksheets("stock").Range("C13").Value >= 15000 Then
- If MsgBox("The number of CAD in stock is too high. Holding too much money can pose as a security risk! Do you wish to release some of these funds?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("E26").Value = (Worksheets("stock").Range("C13").Value - 10000)
- Worksheets("stock").Range("C13").Value = 10000
- Else: Exit Sub
- End If
- End If
- If Worksheets("stock").Range("C14").Value >= 15000 Then
- If MsgBox("The number of AUD in stock is too high. Holding too much money can pose as a security risk! Do you wish to release some of these funds?", _
- vbYesNo, "System Message") = vbYes Then
- Worksheets("stock").Range("E27").Value = (Worksheets("stock").Range("C14").Value - 10000)
- Worksheets("stock").Range("C14").Value = 10000
- Else: Exit Sub
- End If
- End If
- End If
- If MsgBox("Would you like to view the receipt?", vbYesNo, "System Message") = vbYes Then
- Set q = Worksheets("Receipt").Range("B1").End(xlDown).Offset(1).EntireRow.Cells
- With Worksheets("Home")
- q(3, "F").Value = "FXB" & .Range("G64").Value - 4
- q(8, "F").Value = .Range("E14").Value
- q(10, "F").Value = .Range("E12").Value
- q(13, "F").Value = .Range("E25").Value
- q(15, "F").Value = "GBP"
- q(17, "F").Value = .Range("E20").Value
- q(43, "B").Value = "You were served by " & .Range("E9").Value
- q(44, "B").Value = "You were served on " & Left(Now(), 11) & "at " & Right(Now(), 8)
- End With
- Application.Goto Worksheets("Receipt").Range("A1")
- Else
- Exit Sub
- End If
- End Sub
The code for the reciept is easy, but how would I make it so that, like when it adds the new ledger line, it adds a button into the cell "R"+i (where 'i' is the new line each time) that when pressed loads the data from the same line into the reciept? It'd have to change i each time accordingly with the line it's on..
Any ideas?
Hope that was clear enough,
Sam