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.

Scott Holtzman · Accepted Answer · 2015-12-11 16:18:57Z

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

edited Dec 11, 2015 at 16:18

answered Dec 11, 2015 at 14:54

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SHU Over a year ago

thank you for your time, i've looked at the code but there are some things i still don't really understand, shouldn't it be worksheet_change(byval target as range) followed by target.worksheet("versement adherent") instead of worksheet_change( target as range) with worksheet("versement adherent") ? @ScottHoltzmann

Scott Holtzman Over a year ago

I made two typos. Sorry. I fixed them in the edited code. ByVal should have been in the Worksheet_Change argument, as you pointed out. However With Worksheet("verse ... should have been With Worksheets("verse .... Saying Target.Worksheet does not make sense in VBA syntax, since Target is a range. (You can refer to the Target Worksheet by using Target.Parent however)

SHU Over a year ago

hello again, I worked a little more on the code but encountered a few new issues. Here's the thing, So here's my question, First of all, how do i extend this code from If Target.column = 6 And UCase ... to targeting all columns from 6 to 25? Second question, The code works great but not for every row. for example if i write the N in the cell on column 6 and row 8 it might write the payment in the cell on column 6 but row 20. @ScottHoltzman

SHU Over a year ago

I should probably tell you this to make it easier to understand, on the versement adherent worksheet I have in column A I have the names of the people, and in columns C to U I have the payments. On the presence page in column A I have the names and in column F to Y I write a X or an N. Also on the versement adherent page, the payments should be put in a cell according to the cell in which the N was written (column 3 in both sheets for example) I think the part where it finds the name might not be working great because it should normally write the payment in front of the name

Collectives™ on Stack Overflow

Excel VBA programming executing a VBA

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related