By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,963 Members | 1,061 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,963 IT Pros & Developers. It's quick & easy.

Excel VBA: ledger, dynamically creating button

100+
P: 118
Hi folks,

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
  1. Sub ledge()
  2.     Dim r As Excel.Range
  3.     Dim i As Integer
  4.     Dim p As Excel.Range
  5.     Dim q As Excel.Range
  6.  
  7.  
  8.     If MsgBox("Are you sure you want to make this transaction?", _
  9.     vbYesNo, "System Message") = vbNo Then Exit Sub
  10.  
  11.   i = Worksheets("Home").Range("G64").Value
  12.  
  13.     Set r = Worksheets("ledger").Range("B1").End(xlDown).Offset(1).EntireRow.Cells
  14.     Set p = Worksheets("stock").Range("B1").End(xlDown).Offset(1).EntireRow.Cells
  15.  
  16.  
  17.     With Worksheets("Home")
  18.         If WorksheetFunction.CountA(r) <> 0 Then Exit Sub
  19.           If ((Worksheets("stock").Range("C9").Value - Worksheets("Home").Range("E25").Value) <= 0) Then
  20.        If MsgBox("The number of GBP in stock is too low to make this transaction. Do you want to order some more?", _
  21.     vbYesNo, "System Message") = vbYes Then
  22.     Worksheets("stock").Range("F22").Value = (10000 - Worksheets("stock").Range("C9").Value)
  23.         Worksheets("stock").Range("C9").Value = 10000
  24.             Else: MsgBox "Transaction Cancelled", vbOKOnly, "System Message"
  25.             Exit Sub
  26.     End If
  27.     End If
  28.         r(i, "B").Value = "FXB" & .Range("G64").Value - 3
  29.         r(i, "C").Value = Now()
  30.         r(i, "E").Value = "GBP"
  31.         r(i, "D").Value = .Range("E12").Value
  32.         r(i, "G").Value = .Range("E14").Value
  33.         r(i, "J").Value = .Range("E25").Value
  34.         r(i, "M").Value = .Range("E20").Value
  35.         r(i, "P").Value = .Range("E9").Value
  36.  
  37.     With Worksheets("Home")
  38.         If Worksheets("Home").Range("E12").Value = "USD" Then
  39.         p(3, "C").Value = p(3, "C").Value + .Range("E14").Value
  40.         p(2, "C").Value = p(2, "C").Value - .Range("E25").Value
  41.         p(15, "H").Value = p(15, "H").Value + .Range("E20").Value
  42.         p(15, "C").Value = p(15, "C").Value + .Range("E25").Value
  43.         p(16, "D").Value = p(16, "D").Value + .Range("E14").Value
  44.  
  45.         ElseIf Worksheets("Home").Range("E12").Value = "EUR" Then
  46.  
  47.         p(4, "C").Value = p(4, "C").Value + .Range("E14").Value
  48.         p(2, "C").Value = p(2, "C").Value - .Range("E25").Value
  49.         p(15, "H").Value = p(15, "H").Value + .Range("E20").Value
  50.         p(15, "C").Value = p(15, "C").Value + .Range("E25").Value
  51.         p(17, "D").Value = p(17, "D").Value + .Range("E14").Value
  52.  
  53.  
  54.         ElseIf Worksheets("Home").Range("E12").Value = "YEN" Then
  55.  
  56.         p(5, "C").Value = p(5, "C").Value + .Range("E14").Value
  57.         p(2, "C").Value = p(2, "C").Value - .Range("E25").Value
  58.         p(15, "H").Value = p(15, "H").Value + .Range("E20").Value
  59.         p(15, "C").Value = p(15, "C").Value + .Range("E25").Value
  60.         p(18, "D").Value = p(18, "D").Value + .Range("E14").Value
  61.  
  62.  
  63.         ElseIf Worksheets("Home").Range("E12").Value = "CAD" Then
  64.  
  65.         p(6, "C").Value = p(6, "C").Value + .Range("E14").Value
  66.         p(2, "C").Value = p(2, "C").Value - .Range("E25").Value
  67.         p(15, "H").Value = p(15, "H").Value + .Range("E20").Value
  68.         p(15, "C").Value = p(15, "C").Value + .Range("E25").Value
  69.         p(19, "D").Value = p(19, "D").Value + .Range("E14").Value
  70.  
  71.  
  72.         Else
  73.  
  74.         p(7, "C").Value = p(7, "C").Value + .Range("E14").Value
  75.         p(2, "C").Value = p(2, "C").Value - .Range("E25").Value
  76.         p(15, "H").Value = p(15, "H").Value + .Range("E20").Value
  77.         p(15, "C").Value = p(15, "C").Value + .Range("E25").Value
  78.         p(20, "D").Value = p(20, "D").Value + .Range("E14").Value
  79.  
  80.         End If
  81.  
  82.         MsgBox "The ledger and stock totals have been updated.", vbOKOnly, "System Message"
  83.  
  84.         Application.Goto Worksheets("Home").Range("A1")
  85.  
  86.  
  87.     Worksheets("Home").Range("G64").Value = Worksheets("Home").Range("G64").Value + 1
  88.  
  89.  
  90.  
  91.  
  92. End With
  93.     End With
  94.  
  95.      If Worksheets("stock").Range("C9").Value <= 1000 Then
  96.     If MsgBox("The number of GBP in stock is too low. Do you want to order some more?", _
  97.     vbYesNo, "System Message") = vbYes Then
  98.     Worksheets("stock").Range("F22").Value = (10000 - Worksheets("stock").Range("C9").Value)
  99.         Worksheets("stock").Range("C9").Value = 10000
  100.             Else: Exit Sub
  101.     End If
  102.     End If
  103.  
  104.     If Worksheets("stock").Range("C10").Value <= 1000 Then
  105.         If MsgBox("The number of USD in stock is too low. Do you want to order some more?", _
  106.     vbYesNo, "System Message") = vbYes Then
  107.     Worksheets("stock").Range("F23").Value = (10000 - Worksheets("stock").Range("C10").Value)
  108.         Worksheets("stock").Range("C10").Value = 10000
  109.     Else: Exit Sub
  110.     End If
  111.     End If
  112.  
  113.     If Worksheets("stock").Range("C11").Value <= 1000 Then
  114.     If MsgBox("The number of EUR in stock is too low. Do you want to order some more?", _
  115.     vbYesNo, "System Message") = vbYes Then
  116.     Worksheets("stock").Range("F24").Value = (10000 - Worksheets("stock").Range("C11").Value)
  117.         Worksheets("stock").Range("C11").Value = 10000
  118.     Else: Exit Sub
  119.     End If
  120.     End If
  121.  
  122.     If Worksheets("stock").Range("C12").Value <= 1000 Then
  123.     If MsgBox("The number of YEN in stock is too low. Do you want to order some more?", _
  124.     vbYesNo, "System Message") = vbYes Then
  125.     Worksheets("stock").Range("F25").Value = (10000 - Worksheets("stock").Range("C12").Value)
  126.         Worksheets("stock").Range("C12").Value = 10000
  127.     Else: Exit Sub
  128.     End If
  129.     End If
  130.  
  131.     If Worksheets("stock").Range("C13").Value <= 1000 Then
  132.     If MsgBox("The number of CAD in stock is too low. Do you want to order some more?", _
  133.     vbYesNo, "System Message") = vbYes Then
  134.     Worksheets("stock").Range("F26").Value = (10000 - Worksheets("stock").Range("C13").Value)
  135.         Worksheets("stock").Range("C13").Value = 10000
  136.     Else: Exit Sub
  137.     End If
  138.     End If
  139.  
  140.     If Worksheets("stock").Range("C14").Value <= 1000 Then
  141.     If MsgBox("The number of AUD in stock is too low. Do you want to order some more?", _
  142.     vbYesNo, "System Message") = vbYes Then
  143.     Worksheets("stock").Range("F27").Value = (10000 - Worksheets("stock").Range("C14").Value)
  144.         Worksheets("stock").Range("C14").Value = 10000
  145.     Else: Exit Sub
  146.     End If
  147.     End If
  148.  
  149. 'next step
  150.  
  151.     If Worksheets("stock").Range("C9").Value >= 15000 Then
  152.     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?", _
  153.     vbYesNo, "System Message") = vbYes Then
  154.     Worksheets("stock").Range("E22").Value = (Worksheets("stock").Range("C9").Value - 10000)
  155.         Worksheets("stock").Range("C9").Value = 10000
  156.     Else: Exit Sub
  157.     End If
  158.     End If
  159.  
  160.     If Worksheets("stock").Range("C10").Value >= 15000 Then
  161.  
  162.     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?", _
  163.     vbYesNo, "System Message") = vbYes Then
  164.     Worksheets("stock").Range("E23").Value = (Worksheets("stock").Range("C10").Value - 10000)
  165.         Worksheets("stock").Range("C10").Value = 10000
  166.     Else: Exit Sub
  167.     End If
  168.     End If
  169.  
  170.     If Worksheets("stock").Range("C11").Value >= 15000 Then
  171.     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?", _
  172.     vbYesNo, "System Message") = vbYes Then
  173.     Worksheets("stock").Range("E24").Value = (Worksheets("stock").Range("C11").Value - 10000)
  174.         Worksheets("stock").Range("C11").Value = 10000
  175.     Else: Exit Sub
  176.    End If
  177.  
  178.     If Worksheets("stock").Range("C12").Value >= 15000 Then
  179.     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?", _
  180.     vbYesNo, "System Message") = vbYes Then
  181.     Worksheets("stock").Range("E25").Value = (Worksheets("stock").Range("C12").Value - 10000)
  182.         Worksheets("stock").Range("C12").Value = 10000
  183.     Else: Exit Sub
  184.    End If
  185.    End If
  186.  
  187.     If Worksheets("stock").Range("C13").Value >= 15000 Then
  188.     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?", _
  189.     vbYesNo, "System Message") = vbYes Then
  190.     Worksheets("stock").Range("E26").Value = (Worksheets("stock").Range("C13").Value - 10000)
  191.         Worksheets("stock").Range("C13").Value = 10000
  192.     Else: Exit Sub
  193.    End If
  194.    End If
  195.  
  196.     If Worksheets("stock").Range("C14").Value >= 15000 Then
  197.     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?", _
  198.     vbYesNo, "System Message") = vbYes Then
  199.     Worksheets("stock").Range("E27").Value = (Worksheets("stock").Range("C14").Value - 10000)
  200.         Worksheets("stock").Range("C14").Value = 10000
  201.     Else: Exit Sub
  202.     End If
  203.   End If
  204.    End If
  205.      If MsgBox("Would you like to view the receipt?", vbYesNo, "System Message") = vbYes Then
  206.     Set q = Worksheets("Receipt").Range("B1").End(xlDown).Offset(1).EntireRow.Cells
  207.  
  208.         With Worksheets("Home")
  209.         q(3, "F").Value = "FXB" & .Range("G64").Value - 4
  210.         q(8, "F").Value = .Range("E14").Value
  211.         q(10, "F").Value = .Range("E12").Value
  212.         q(13, "F").Value = .Range("E25").Value
  213.         q(15, "F").Value = "GBP"
  214.         q(17, "F").Value = .Range("E20").Value
  215.         q(43, "B").Value = "You were served by " & .Range("E9").Value
  216.         q(44, "B").Value = "You were served on " & Left(Now(), 11) & "at " & Right(Now(), 8)
  217.  
  218.         End With
  219.  
  220.      Application.Goto Worksheets("Receipt").Range("A1")
  221.  
  222.         Else
  223.           Exit Sub
  224.         End If
  225. End Sub
  226.  
I was thinking of adding a reload for the reciepts, so if the operator presses the button next to a transaction it'll load that data for the reciept.

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
Apr 23 '08 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.