+ Reply to Thread
Results 1 to 1 of 1

UserForm Does not add Data to worksheet

Hybrid View

  1. #1
    Registered User
    Join Date
    04-21-2011
    Location
    Atlanta GA
    MS-Off Ver
    Excel 2003
    Posts
    1

    UserForm Does not add Data to worksheet

    I have created the following code to accomplish the following:
    1. Identify all project less than 90 days old
    2. If the project is less than 90 days old identify if the commission will be split and if so with which sales person and at what amount.(this is the object of the UserForm)
    3. Add the project information for the sales commission split to the worksheet. In other words, copy the entire row for that project to the bottom of the spreadsheet and update the two cells with the input from the userform.
    There are 3 projects that meet the less than 90 days criteria that I wish to add to the worksheet via the UserForm, but with my code it adds the first entry (project #1058.) I need the code to loop back to the spreadsheet, identify the next project (in this case project #1082.) that is less than 90 days and if that commission will be split add the project information to the worksheet for the commission split.
    Lastly, the salesperson is only eligible to receive the commission once so I also need to flag the entries so that the next time its run they won’t be eligible to get the commission again.
    I am also sure there is a more efficient way to do this, just not sure what that is.
    Any help would be appreciated. Thanks

    'Determine if Service Agreement is the Initial Booking.
    Sub Service_Agreement_Incentive_MsgBox() 
    Application.EnableCancelKey = xlDisabled
    Dim InitBookg As Integer
    Dim Split As Integer
    Dim I As Integer
    I = 2
    Do While Not (IsEmpty(Cells(I, 2)))
    If Cells(I, 2) = "Service_Agreement_1" Or Cells(I, 2) = "Service_Agreement_3" Or Cells(I, 2) = "Service_Agreement_5" Then
        If Cells(I, 49) <= 90 And Cells(I, 53) <> "X" Then
            InitBookg = MsgBox(Prompt:="Is this the Initial Booking of Project #" & Cells(I, 1) & "?", Buttons:=vbYesNo, Title:="Booking Status")
                If InitBookg = vbYes Then
                    Cells(I, 8) = "Yes"
                    Call AddServiceAgreementIncentive
                    Split = MsgBox(Prompt:="Will Project " & "#" & Cells(I, 1) & " be split with another Salesperson" & "?", Buttons:=vbYesNo, Title:="Sales Incentive Split")
                        If Split = vbYes Then
                        Call ShowForm
                        End If
                 Else
                    Cells(I, 8) = "No"
                End If
    UserForm code to copy row and add to bottom of worksheet with 2nd salesperson and commission pertentage
    Sub OKButton_Click()
    Dim Confirm As Integer
    Dim I As Integer
    
    I = 2
    Sheets("Model").Select
    FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
    If Cells(I, 49) <= 90 And Cells(I, 53) = "X" Then
        Confirm = MsgBox(Prompt:="Please Confirm Project " & "#" & Cells(I, 1) & " be split with another Salesperson" & "?", Buttons:=vbYesNo, Title:="Sales Incentive Split")
            If Confirm = vbYes Then
                Cells(I, 1).Resize(1, 53).Copy
                Cells(FinalRow, 1).Select
                NextRow = Cells(Rows.Count, 1).End(xlUp).Row + 1
                Cells(NextRow, 1).Select
                ActiveSheet.Paste
                ActiveCell.Offset(0, 9) = Salesperson.Value
                ActiveCell.Offset(0, 53) = Split.Value
                Unload UserForm1 ‘close UserForm
                Call Service_Agreement_Incentive_MsgBox ‘Go back to original Macro
        End If
    End If
    End Sub
    Attached Files Attached Files
    Last edited by SCAL8N; 04-26-2011 at 04:02 PM. Reason: Added Code Tags

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1