Excel VBA programming executing a VBA

Question

I am in charge of keeping a record of the fees and payments in my club and so I decided to make an Excel page for it. I am new to Excel VBA programming, so I need some advice.

My idea is: if the person is present but didn't pay, I just mark an x, if he is present and just payed, I would like to be able to mark an N and make it so that a msgbox pops up asking the value of the payment and directly puts that value in a precise cell on another page.

Sub Pay()
    Dim Pay As String
    Pay = InputBox("Enter A New Payment", "Payment", "Enter amount here")
    Range("'versement adherent'!C15").Value = Pay
End Sub

So that was my idea for the msgbox but it is too narrow, it only starts if asked to (doesn't start automatically when a cell with the value N is written) and the cell in which it writes the value is always the same.

Private Sub FindN(StrSearchQuery As String)
    Set SearchRange = Range("versement adherent!F2:Y21")
    FindN = StrSearchQuery

    Function FindAll(SearchRange As Range, LookFor As String)
        Dim FoundCell As Range

        For Each area In SearchRange.Areas
            With area
                If .cell(.cell.Count).Row > MaxRow Then
                    MaxRow = .cell(.cell.Count).Column
                End If
            End With

        Next area
        Set lastcell = SearchRange.Worksheet.Cells(MaxRow, MaxCol)
        Do Until True
            Set FoundCell = SearchRange.Find(N)
            If FoundCell = False Then
        Exit Do

        If FoundCell = True Then
            For Each FoundCell In Worksheet("versement adherent").Range(FoundCell)
                Range("versement adherent !Foundcell").Value = Pay
                Range(FoundCell).Value = X
            Exit Do

        End If
    End Function

    Set FoundCell = FindAll

    If FoundCell = True Then
        For Each FoundCell In Worksheet("versement adherent").Range(FoundCell)
        Range("versement adherent !Foundcell").Value = Pay
    End If

I've tried adding some code to make the part where it finds a cell with the value N but it doesn't seem to work.

Answer 1

Use the Worksheet_Change event.

I will admit, with your example code, it was hard to fully understand what you were trying to do, but I based my code on your explanation.

My code below makes the assumption that a given person's name is in column A and the cell to mark X or N is in column B (so right next to the person's name).

It also makes the assumption that you just want to enter the payment amount for each person once in the versement adherent sheet. It assumes the person's name is listed in that sheet in column Y. It searches column Y for that name and places in the payment amount in column Z right next to the person's name.

This obviously will need to be tweaked for your exact requirements, but should be easy to do so given the comments I have made. However, if my assumptions are way-off base, let me know in the comments and I can probably adjust for you :)

Place the code below inside the Worksheet Module where you mark X or N and adjust for your specific ranges, sheet names etc. The code will fire each time you mark an N for a person.

Private Sub Worksheet_Change(ByVal Target as Range)

If Target.Column = 2 And UCase(Target.Value2) = "N" ' assumes you enter x or N in column B

   Dim Pay As String
   Pay = InputBox("Enter A New Payment", "Payment", "Enter amount here")

   'now find the name in versement adherent sheet
   With Worksheets("versement adherent")

       'first find last row to search
       Dim lRow as Long
       lRow = .Range("Y" & .Rows.Count).End(xlup).Row

       'search column Y for the name
       Dim rPayCell as Range
       Set rPayCell = .Range("Y2:Y" & lRow).Find(Target.Offset(,-1).Value2

       If Not rPayCell is Nothing Then 
          rPayCell.Offset(,1).Value = Pay 'if name is found place payment in column Z
       Else 'otherwise alert
          Msgbox "Name Not Found in Versement Adherent Sheet"
       End If

   End With

End If

End Sub