Excel VBA: ledger, dynamically creating button

[helraizer1]

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:

[code=vb]
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("Hom e").Range("G64" ).Value

Set r = Worksheets("led ger").Range("B1 ").End(xlDown). Offset(1).Entir eRow.Cells
Set p = Worksheets("sto ck").Range("B1" ).End(xlDown).O ffset(1).Entire Row.Cells


With Worksheets("Hom e")
If WorksheetFuncti on.CountA(r) <> 0 Then Exit Sub
If ((Worksheets("s tock").Range("C 9").Value - Worksheets("Hom e").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("sto ck").Range("F22 ").Value = (10000 - Worksheets("sto ck").Range("C9" ).Value)
Worksheets("sto ck").Range("C9" ).Value = 10000
Else: MsgBox "Transactio n Cancelled", vbOKOnly, "System Message"
Exit Sub
End If
End If
r(i, "B").Value = "FXB" & .Range("G64").V alue - 3
r(i, "C").Value = Now()
r(i, "E").Value = "GBP"
r(i, "D").Value = .Range("E12").V alue
r(i, "G").Value = .Range("E14").V alue
r(i, "J").Value = .Range("E25").V alue
r(i, "M").Value = .Range("E20").V alue
r(i, "P").Value = .Range("E9").Va lue

With Worksheets("Hom e")
If Worksheets("Hom e").Range("E12" ).Value = "USD" Then
p(3, "C").Value = p(3, "C").Value + .Range("E14").V alue
p(2, "C").Value = p(2, "C").Value - .Range("E25").V alue
p(15, "H").Value = p(15, "H").Value + .Range("E20").V alue
p(15, "C").Value = p(15, "C").Value + .Range("E25").V alue
p(16, "D").Value = p(16, "D").Value + .Range("E14").V alue

ElseIf Worksheets("Hom e").Range("E12" ).Value = "EUR" Then

p(4, "C").Value = p(4, "C").Value + .Range("E14").V alue
p(2, "C").Value = p(2, "C").Value - .Range("E25").V alue
p(15, "H").Value = p(15, "H").Value + .Range("E20").V alue
p(15, "C").Value = p(15, "C").Value + .Range("E25").V alue
p(17, "D").Value = p(17, "D").Value + .Range("E14").V alue


ElseIf Worksheets("Hom e").Range("E12" ).Value = "YEN" Then

p(5, "C").Value = p(5, "C").Value + .Range("E14").V alue
p(2, "C").Value = p(2, "C").Value - .Range("E25").V alue
p(15, "H").Value = p(15, "H").Value + .Range("E20").V alue
p(15, "C").Value = p(15, "C").Value + .Range("E25").V alue
p(18, "D").Value = p(18, "D").Value + .Range("E14").V alue


ElseIf Worksheets("Hom e").Range("E12" ).Value = "CAD" Then

p(6, "C").Value = p(6, "C").Value + .Range("E14").V alue
p(2, "C").Value = p(2, "C").Value - .Range("E25").V alue
p(15, "H").Value = p(15, "H").Value + .Range("E20").V alue
p(15, "C").Value = p(15, "C").Value + .Range("E25").V alue
p(19, "D").Value = p(19, "D").Value + .Range("E14").V alue


Else

p(7, "C").Value = p(7, "C").Value + .Range("E14").V alue
p(2, "C").Value = p(2, "C").Value - .Range("E25").V alue
p(15, "H").Value = p(15, "H").Value + .Range("E20").V alue
p(15, "C").Value = p(15, "C").Value + .Range("E25").V alue
p(20, "D").Value = p(20, "D").Value + .Range("E14").V alue

End If

MsgBox "The ledger and stock totals have been updated.", vbOKOnly, "System Message"

Application.Got o Worksheets("Hom e").Range("A 1")


Worksheets("Hom e").Range("G64" ).Value = Worksheets("Hom e").Range("G64" ).Value + 1




End With
End With

If Worksheets("sto ck").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("sto ck").Range("F22 ").Value = (10000 - Worksheets("sto ck").Range("C9" ).Value)
Worksheets("sto ck").Range("C9" ).Value = 10000
Else: Exit Sub
End If
End If

If Worksheets("sto ck").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("sto ck").Range("F23 ").Value = (10000 - Worksheets("sto ck").Range("C10 ").Value)
Worksheets("sto ck").Range("C10 ").Value = 10000
Else: Exit Sub
End If
End If

If Worksheets("sto ck").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("sto ck").Range("F24 ").Value = (10000 - Worksheets("sto ck").Range("C11 ").Value)
Worksheets("sto ck").Range("C11 ").Value = 10000
Else: Exit Sub
End If
End If

If Worksheets("sto ck").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("sto ck").Range("F25 ").Value = (10000 - Worksheets("sto ck").Range("C12 ").Value)
Worksheets("sto ck").Range("C12 ").Value = 10000
Else: Exit Sub
End If
End If

If Worksheets("sto ck").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("sto ck").Range("F26 ").Value = (10000 - Worksheets("sto ck").Range("C13 ").Value)
Worksheets("sto ck").Range("C13 ").Value = 10000
Else: Exit Sub
End If
End If

If Worksheets("sto ck").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("sto ck").Range("F27 ").Value = (10000 - Worksheets("sto ck").Range("C14 ").Value)
Worksheets("sto ck").Range("C14 ").Value = 10000
Else: Exit Sub
End If
End If

'next step

If Worksheets("sto ck").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("sto ck").Range("E22 ").Value = (Worksheets("st ock").Range("C9 ").Value - 10000)
Worksheets("sto ck").Range("C9" ).Value = 10000
Else: Exit Sub
End If
End If

If Worksheets("sto ck").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("sto ck").Range("E23 ").Value = (Worksheets("st ock").Range("C1 0").Value - 10000)
Worksheets("sto ck").Range("C10 ").Value = 10000
Else: Exit Sub
End If
End If

If Worksheets("sto ck").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("sto ck").Range("E24 ").Value = (Worksheets("st ock").Range("C1 1").Value - 10000)
Worksheets("sto ck").Range("C11 ").Value = 10000
Else: Exit Sub
End If

If Worksheets("sto ck").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("sto ck").Range("E25 ").Value = (Worksheets("st ock").Range("C1 2").Value - 10000)
Worksheets("sto ck").Range("C12 ").Value = 10000
Else: Exit Sub
End If
End If

If Worksheets("sto ck").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("sto ck").Range("E26 ").Value = (Worksheets("st ock").Range("C1 3").Value - 10000)
Worksheets("sto ck").Range("C13 ").Value = 10000
Else: Exit Sub
End If
End If

If Worksheets("sto ck").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("sto ck").Range("E27 ").Value = (Worksheets("st ock").Range("C1 4").Value - 10000)
Worksheets("sto ck").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("Rec eipt").Range("B 1").End(xlDown) .Offset(1).Enti reRow.Cells

With Worksheets("Hom e")
q(3, "F").Value = "FXB" & .Range("G64").V alue - 4
q(8, "F").Value = .Range("E14").V alue
q(10, "F").Value = .Range("E12").V alue
q(13, "F").Value = .Range("E25").V alue
q(15, "F").Value = "GBP"
q(17, "F").Value = .Range("E20").V alue
q(43, "B").Value = "You were served by " & .Range("E9").Va lue
q(44, "B").Value = "You were served on " & Left(Now(), 11) & "at " & Right(Now(), 8)

End With

Application.Got o Worksheets("Rec eipt").Range("A 1")

Else
Exit Sub
End If
End Sub
[/code]

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