Here is the code, I have 2 ComboBox's on this form and 2 TextBox's and for some obscure reason when I change the debtor ComboBox it Refreshes the Balance, but doesn't refresh the Price; Price is a cross reference of Debtor and Quantity.
Private Sub UserForm_Initialize()
Purchase_Select_Debtor.List = Workbooks("New Template.xlsm").Worksheets("Debtor_list").Range("A2:A13").Value
Purchase_Select_Debtor.ListIndex = 0
Purchase_Select_Quantity.List = Workbooks("New Template.xlsm").Worksheets("RangeNames").Range("A15:A20").Value
Purchase_Select_Quantity.ListIndex = 0
End Sub
Private Sub cmdBuy_Purchase_Click()
Purchase_Select_Debtor.Value = Name
Purchase_Select_Price.Value = Amount
Purchase_Select_Balance.Value = Balance
If Name = "" Then
MsgBox "Select Debtor"
Exit Sub
End If
DebtorRow = 1
Do
TempName = Worksheets("Debtor_list").Range("A" & DebtorRow).Value
If TempName = Name Then
DebtorBalance = CSng(Worksheets("Debtor_List").Range("B" & DebtorRow).Value)
Exit Do
End If
DebtorRow = DebtorRow + 1
Loop Until TempName = ""
If TempName = "" Then
MsgBox "Debtor not found"
Exit Sub
End If
Worksheets("Debtor_List").Range("B" & DebtorRow).Value = DebtorBalance - Amount
MsgBox "You have just Purchased " & Amount & " For $" & Amount & vbCrLf & "Your Account Balance is now: " & Balance
End Sub
Private Sub cmdClose_Purchase_Click()
Unload Me
End Sub
Private Sub Purchase_Select_Debtor_Change()
Purchase_Select_Balance.Value = "$" & Application.VLookup(Purchase_Select_Debtor.Value, Sheets("Debtor_list").Range("A2:B13"), 2, 0)
End Sub
Private Sub Purchase_Select_Quantity_Change()
Purchase_Select_Price.Value = "$" & Application.Index(Sheets("Inventory_list").Range("A1:G13"), Application.Match(Purchase_Select_Debtor.Value, Sheets("Inventory_list").Range("A1:A13"), 0), Application.Match(Purchase_Select_Quantity.Value, Sheets("Inventory_list").Range("A1:G1"), 0))
End Sub