Conceptual Question

[reberryjr]

I'm struggling with how to build out something in an application I'm building. There are 5 services that a Client can sign up for. Payments for each service are tracked on separate sheets (each named after the respective service). Each service can have different due dates, payment amounts and payment frequencies, which can also differ by Client. What I'm wondering is, is there a way to build a User Form that allows the User to input the amount of money received, along with the method the money was received (Cash, Check or PayPal) and have the application triage the money, documenting the appropriate row on the appropriate sheet, with details on the payment?

I'd have to design the logic out a little bit more, but at a high level, it would be oldest due date to newest due date. Of course, if the service with the oldest due date is $200 and the Client sends in $500, $200 would go to the oldest due date, and I would want the application to split the remaining $300 among the next due dates.

Not sure if it would be needed, but I uploaded a copy of the application's current state here.

https://app.box.com/s/kay1m6jnsjaz6cs0ynrmb8czr9thfrfd

 

[reberryjr]

I'm still working on this and I think I'm nearing a possible solution, but need some help. Here's the code that gives me the values for the cobo_ClientID box. It reads off of the "Bios" sheet, since that's the only sheet that will have every Client ID on it. What I need to do is, pull in the DP Due Amount from the DP Sheet, if the value of the cobo_ClientID box is found. To further complicate matters, there can be multiple rows for each Client ID on the DP Sheet. I would need to pull in the first instance where the DP Pymt Status = "Late". If there is no "Late" status, then I need to pull in the first instance of the Pymt Status "Current".

Given the multiple conditions and sheets, I'm not sure what method is the best to use.

Private Sub UserForm_Initialize()

Dim ws1 As Worksheet
Dim ws3 As Worksheet
Dim ws4 As Worksheet
Dim ws5 As Worksheet
Dim ws6 As Worksheet
Dim ws7 As Worksheet
Dim ws8 As Worksheet


Dim cBiosClientID As Range
Dim cDPClientID As Range
Dim cDCClientID As Range
Dim cOCClientID As Range
Dim cCTIClientID As Range
Dim cCTOClientID As Range
Dim cPymtsRcvdClientID As Range


Set ws1 = ThisWorkbook.Sheets("Bios")
Set ws3 = ThisWorkbook.Sheets("DP")
Set ws4 = ThisWorkbook.Sheets("DC")
Set ws5 = ThisWorkbook.Sheets("OC")
Set ws6 = ThisWorkbook.Sheets("CTI")
Set ws7 = ThisWorkbook.Sheets("CTO")
Set ws8 = ThisWorkbook.Sheets("PymtsRcvd")


For Each cBiosClientID In ws1.Range("BiosClientID")
    With Me.cobo_ClientID
        .AddItem cBiosClientID
    End With
Next cBiosClientID


Set coboDict1 = CreateObject("Scripting.Dictionary")
With coboDict1
    For Each cBiosClientID In ws1.Range("BiosClientID")
        .Item(cBiosClientID.Value) = cBiosClientID.Row
    Next cBiosClientID
    Me.cobo_ClientID.List = Application.Transpose(.keys)
End With


Set coboDict3 = CreateObject("Scripting.Dictionary")
With coboDict3
    For Each cDPClientID In ws3.Range("DPClientID")
    If Not (cDPClientID.Offset(, 26).Value) = "Paid" Then
        .Item(cDPClientID.Value) = cDPClientID.Row
    End If
Next cDPClientID
'    Me.cobo_ClientID.List = Application.Transpose(.keys)
End With


End Sub
Private Sub cobo_ClientID_Change()


With Sheets("DP")
    Me.cobo_ClientID = .Cells(coboDict1.Item(Me.cobo_ClientID), "D").Value
    Me.txt_DPDueAmt = .Cells(coboDict3.Item(Me.cobo_ClientID), "AC").Value
End With


End Sub

 

[RoryA]

Disclaimer: I haven't looked at your workbook!

Why do you want separate sheets for each service? It will just make reporting at an overall level harder generally.

 

[reberryjr]

Well, the original thought was to have a single sheet to track all Clients' financial info. Then, it was strongly suggested that I split them by Client (which I liked the idea of), so I did. Now, I've switched to a sheet for each service, thinking it would be easier to find the correct target row to update. I'm not proficient enough with VBA to really understand what would be easier or harder to code. Still trying to learn.

 

[RoryA]

Why was it suggested to break the data up? That makes it harder to report simple calculations like how much money am I owed overall?

 

[reberryjr]

I believe it was because of the number of data elements that would be on a single worksheet, and the different calculations that the application needs to perform. 5 services, with potentially 5 different due dates, 5 different price points; and the application should identify when the Pymt Status changes to either Paid or Late, and insert a row below, to capture the next payment due date.

My goal was to get this version up and running, then try to figure out how to adapt the code to a version where each Client has their own sheet.

Essentially, what seems to be stumping the folks that have tried to help me is how to identify the correct row to populate the UserForm and then update the same data. The selection criteria would be

MIN Updated date Where Sheet.ClientID = cobo_ClientID AND Pymt Status <> Paid

In my mind, it seems like it should be fairly straightforward, but it's proving to not be.

 

[reberryjr]

I think I'm on to something here, but I'm not quite there yet. I've decided to go to one format for the data, and not waiver anymore. With that being said, I'm back to each Client having their own sheet to track their financials. I'm having issues finding the first instance of "Late" in column "BM", and pulling that data into the UserForm. The goal is to populate the UserForm with the data, then update the UserForm, which will then update the same row. Ideally, I'd be looking for the first instance of "Late". If there are no instances of "Late", going back to "BM2" and looking for the first instance of "Current". That piece isn't in the code yet, because I was struggling with aspect of the code enough. I was hoping if I could figure out the first part, I'd be able to extrapolate the code to account for both variables (the key word being "hoping").

Here's what I have so far.

Private Sub cobo_ClientID_Change()

Dim Sht As String
Dim LastRow As Long
Dim GoalRow As Long


Sht = Me.cobo_ClientID


With ActiveSheet
'Dim ws As Worksheet
'Set ws = ThisWorkbook.ActiveSheet
    LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    GoalRow = .Cells(WorksheetFunction.VLookup(Me.cobo_ClientID.Value, .Range("BM"), 1, False))
End With


Me.txt_DPNextDue = Sheets(Sht).Range("J" & GoalRow).Value


End Sub

@Fluff, I tagged you because you've been so extremely helpful. Hopefully that isn't intrusive on my part.

 

[reberryjr]

I might have figured it out with this code. I'll test more tomorrow.

Private Sub cobo_ClientID_Change()

Dim Sht As String
Dim LastRow As Long
Dim UpdateRow As Long
Dim FindRow As Range


Sht = Me.cobo_ClientID


With ActiveSheet
    LastRow = .Cells(.Rows.Count, "D").End(xlUp).Row
    Set FindRow = Range("BR:BR").Find(what:="Late", LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows)
    If Not FindRow Is Nothing Then
        UpdateRow = FindRow.Row
    End If
End With