Excel Spreadsheet Convert to UserForm VBA Problem

Question

I made a Sheet which I have now converted to a UserForm but the VB code I was using no longer works, I will copy the old code used and the new one which keeps telling me "Debtor not found", any help would be Appreciated.

Old Code:

Sub Buy_Click()

    Name = Worksheets("Purchase").Range("G19").Value
    Amount = CSng(Worksheets("Purchase").Range("I19").Value)
    Balance = CSng(Worksheets("Purchase").Range("J19").Value)

    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 " & Range("H19") & " For $" & Range("I19") & vbCrLf & "Your Account Balance is now: " & Range("J19")

    Application.Goto Reference:="Purchase_Debtor"
    Selection.ClearContents

    Application.Goto Reference:="Purchase_Quantity"
    Selection.ClearContents

    Sheets("Menu").Select

End Sub

New Code:

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

And another Code I've used for a different UserForm with the same issue;

Old Code:

Sub Pay_Click()

    Name = Worksheets("pay_balance").Range("F18").Value
    Amount = CSng(Worksheets("pay_balance").Range("G18").Value)

    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 Credited $" & Range("G18") & vbCrLf & "Your Account Balance is now: " & Range("H18")

    Application.Goto Reference:="Creditbox"
    Selection.ClearContents

    Application.Goto Reference:="Balance_Debtor"
    Selection.ClearContents

    Sheets("Menu").Select

End Sub

New Code:

Private Sub cmdPay_Balance_Click()

Pay_Balance_Balance.Value = Name
Pay_Balance_Credit.Value = Amount

    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 Credited $" & Amount & vbCrLf & "Your Account Balance is now: " & Name

End Sub

James · Accepted Answer · 2010-09-10 11:26:43Z

Private Sub cmdBuy_Purchase_Click()

    Debtor = Purchase_Select_Debtor.Value
    Amount = CSng(txtPrice.Value)
    Balance = CSng(txtBalance.Value)

    If Debtor = "" Then
        MsgBox "Select Debtor"
        Exit Sub
    End If

    DebtorRow = 1
    Do
        TempDebtor = Worksheets("Debtor_list").Range("A" & DebtorRow).Value
        If TempDebtor = Debtor Then
            DebtorBalance = CSng(Worksheets("Debtor_List").Range("B" & DebtorRow).Value)
            Exit Do
        End If
        DebtorRow = DebtorRow + 1
    Loop Until TempDebtor = ""

    If TempDebtor = "" Then
        MsgBox "Debtor not found"
        Exit Sub
    End If

    Worksheets("Debtor_List").Range("B" & DebtorRow).Value = DebtorBalance - Amount

    Balance = Application.VLookup(Purchase_Select_Debtor.Value, Sheets("Debtor_list").Range("A2:B13"), 2, 0)

    MsgBox "You have just Purchased " & Purchase_Select_Quantity.Value & " For $" & Amount & vbCrLf & "Your Account Balance is now: " & Balance

    Unload FrmPurchase

End Sub

&

Sub cmdPay_Balance_Click()

    Debtor = Pay_Balance_Select_Debtor.Value
    Amount = CSng(txtCredit.Value)

    If Debtor = "" Then
        MsgBox "Select Debtor"
        Exit Sub
    End If

    DebtorRow = 1
    Do
        TempDebtor = Worksheets("Debtor_list").Range("A" & DebtorRow).Value
        If TempDebtor = Debtor Then
            DebtorBalance = CSng(Worksheets("Debtor_List").Range("B" & DebtorRow).Value)
            Exit Do
        End If
        DebtorRow = DebtorRow + 1
    Loop Until TempDebtor = ""

    If TempDebtor = "" Then
        MsgBox "Debtor not found"
        Exit Sub
    End If


    Worksheets("Debtor_List").Range("B" & DebtorRow).Value = DebtorBalance + Amount

    txtBalance.Value = Application.VLookup(Pay_Balance_Select_Debtor.Value, Sheets("Debtor_list").Range("A2:B13"), 2, 0)

    MsgBox "You have just Credited $" & Amount & vbCrLf & "Your Account Balance is now: " & txtBalance

    Unload frmPay_Balance

End Sub

Collectives™ on Stack Overflow

Excel Spreadsheet Convert to UserForm VBA Problem

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related