macro to lookup record on current row and mark completion date to corresponding recor

[shellecj]

Hello,

I’m hoping someone could help me with some ideas on how to do this or if something similar been done already, to point me in the right direction? I have spent many hours now combing the internet to see if others out there have posted something similar like this in the past with the hopes I could modify the code to suit my needs but just cannot find anything even remotely similar to what I’m trying to do?

I have a workbook that consists of a main sheet with all data pulled out of our system on a weekly basis for all active properties being managed and have a separate sheet for each asset manager that pulls all tasks being due for outstanding BPO’s ordered, BPO’s due, etc, which is accomplished with an excel array formula in each of the tables on their sheets (see attachment). After I pull the report, I forward to all of the asset managers and they go to their corresponding sheet tab in the workbook to work off their lists to see what tasks are due or outstanding; however I need a simple way to mark each as complete when done (note: attachment provided is only a sample includes only one of the asset manager’s sheet tab for example). Currently what they have to do is go to the main sheet which is cumbersome (several hundred rows and columns) and find the record they’re looking for and manually scroll over to find the BPO Complete column, etc and put today’s date in that field so it no longer shows up on their sheet as being due.

So what I would like to do is give them a way to mark the particular task as either complete or to otherwise remove it from sheet by a click of a button on their sheet (see first table on the sheet that says AM1 where I inserted 2 buttons for this purpose at the right of each line). To mark as complete, I need the code to look up the loan number on the current row (where the button is, on the asset managers tab; “AM1” in this example) and find the corresponding record on the Task Flow tab and write in today’s date in column AS (column named” “BPO Complete”) on same row (or record). The 2^nd button I would like to use for a manual override to still remove it off the list (say BPO’s not done but agent was emailed, etc) and work like the previous macro to lookup the corresponding record on the main sheet (Task Flow) and put an * (asterisk) in column CS (column named “Override”) of that row.

Any help, ideas or suggestions would be greatly appreciated…thanks!

[Bob Phillips]

You only need one button on the AM's sheet as you can work off of the active row to get the loan number. You can then use that loan number to lookup on the main sheet and update the BPO completed column accordingly.

[Bob Phillips]

Here's some basic code

Public Function BPOCompleted()
Dim idx As Long

    With ActiveSheet
    
        If .Cells(ActiveCell.Row, "B").Value <> "" Then
        
            On Error Resume Next
            idx = Application.Match(.Cells(ActiveCell.Row, "B").Value, Worksheets("Task Flow").Columns("A"), 0)
            On Error GoTo 0
            If idx > 0 Then
            
                Worksheets("Task Flow").Cells(idx, "AS").Value = Date
            End If
        End If
    End With
End Function

[shellecj]

Hi XLD

Thank you for the assistance but I'm not sure how to use the function. I see that I can use the UDF in a cell in the worksheet but now sure how to use it? Also, I'm not clear how one button would work (unless the user has to manually click on any one record on that page...which I'm afraid the idea might be lost on them?) but what if they want to mark, say the 3rd one down on the list as complete?

I'm very limited in my knowledge of vba by the way...I'm trying though. Have a few books I've bought but even in going through those, I can't find anything that addresses anything I'm seeking.

[Bob Phillips]

It is not a UDF, it is a function that would be assigned to the one button. The idea would be that the user selects the row that is to be updated, then press the button. You could add a check in there to say display a message as to which loan number is being updated and ask the user to confirm that this is the correct item.

[shellecj]

Hi XLD,

Sorry for the slow reply; I wasn't able to get back to this until this evening. First, thank you so much for the help you've provided me on this, this was greatly appreciated!

I think I have this code pretty much working though seems to have a few quirks to smooth out.

I changed the function to a sub (the idea on why a function was a little lost on me, I didn't know how to call it) and created just one button, as you suggested, to assign the macro to it. From this code I created 2 macros; one for the to enter in the current date on the main sheet in the BPO Complete column (assigned to one button labeled "Mark as Done") and one to just put an asterisk (*) in column CS on the main Task Flow tab, to just temporarily remove it from the list and postpone or delay action. The 2nd macro is assigned to another button labeled "Postpone".

I followed your suggestion to display a msgbox to have the user confirm; however on the first macro I put a 2nd msgbox for one more prompt (or msg) to alert that the property will be removed from the page. This seems to work fine and when I hit No to the first msgbox, the macro aborts. If I hit yes, the 2nd msgbox comes up but to this one, the macro runs its course and marks the task as complete whether I hit Yes or Cancel? I take it I did something wrong here or can't have the back to back msgboxes? The second macro assigned to the "Postpone" button seems to work fine, though there is only one msgbox prompt for this one.

Another issue I'm having is sometimes when I click anywhere, whether it's on the main tab or the AM1 tab, the macro still wants to run, regardless of whether I executed the macro or not. I believe it's because of the code I have in the main Wkbk module ('ThisWorkbook') for sheet selection change event to recalculate. I have this set up to use in conjunction with conditional formatted where the active row is highlighted. The strange thing is, is that this behavior is intermittent?

Also, can this code be amended so that it only works if a row somewhere in the table is selected? Rather than clicking on blank space anywhere on the page?

Below is the amended code I have thus far:

  
Sub BPOCompleted()
    Dim BPO_Cpl As Long
    Dim property As String
    property = ActiveSheet.Cells(ActiveCell.Row, "E").Value
    
    If MsgBox("Mark " & property & "as complete?", vbYesNo + vbExclamation) = vbNo Then Exit Sub _
    Else If MsgBox("Property will be removed from this page.", vbOKCancel) = vbCancel + vbNo Then Exit Sub
     
    With ActiveSheet
         
        If .Cells(ActiveCell.Row, "B").Value <> "" Then
             
            On Error Resume Next
            BPO_Cpl = Application.Match(.Cells(ActiveCell.Row, "B").Value, Worksheets("Task Flow").Columns("A"), 0)
            On Error GoTo 0
            If BPO_Cpl > 0 Then
                 
                Worksheets("Task Flow").Cells(BPO_Cpl, "AS").Value = Date
            End If
        End If
    End With
End Sub

Sub BPOComplete_Postpone()
    Dim BPO_Cpl As Long
    Dim property As String
    property = ActiveSheet.Cells(ActiveCell.Row, "E").Value
    
    If MsgBox("Click ""OK"" to delay action and remove " & property & "from this list.", vbOKCancel) = vbCancel Then Exit Sub _
     
    With ActiveSheet
         
        If .Cells(ActiveCell.Row, "B").Value <> "" Then
             
            On Error Resume Next
            BPO_Cpl = Application.Match(.Cells(ActiveCell.Row, "B").Value, Worksheets("Task Flow").Columns("A"), 0)
            On Error GoTo 0
            If BPO_Cpl > 0 Then
                 
                Worksheets("Task Flow").Cells(BPO_Cpl, "CS").Value = "*"
            End If
        End If
    End With
End Sub

Any more thoughts, ideas or suggestions would be greatly appreciated! Thanks again!

[Bob Phillips]

I changed the function to a sub (the idea on why a function was a little lost on me, I didn't know how to call it) and created just one button, as you suggested, to assign the macro to it. From this code I created 2 macros; one for the to enter in the current date on the main sheet in the BPO Complete column (assigned to one button labeled "Mark as Done") and one to just put an asterisk (*) in column CS on the main Task Flow tab, to just temporarily remove it from the list and postpone or delay action. The 2nd macro is assigned to another button labeled "Postpone".

I always use functions, not subs, except where I have to (such as event code).

I followed your suggestion to display a msgbox to have the user confirm; however on the first macro I put a 2nd msgbox for one more prompt (or msg) to alert that the property will be removed from the page. This seems to work fine and when I hit No to the first msgbox, the macro aborts. If I hit yes, the 2nd msgbox comes up but to this one, the macro runs its course and marks the task as complete whether I hit Yes or Cancel? I take it I did something wrong here or can't have the back to back msgboxes? The second macro assigned to the "Postpone" button seems to work fine, though there is only one msgbox prompt for this one.

This is because you test the second MsgBox for vbCancel + vbNo. Only one button can be pressed, so only test for one, and there is no No button anyway. I changed your code to be a biut more structured (IMO), making it easier to see what is going on.

Sub BPOCompleted()
    Dim BPO_Cpl As Long
    Dim property As String
    property = ActiveSheet.Cells(ActiveCell.Row, "E").Value
     
    If MsgBox("Mark " & property & "as complete?", vbYesNo + vbExclamation) = vbNo Then
    
        Exit Sub
    Else
    
        If MsgBox("Property will be removed from this page.", vbOKCancel) = vbCancel Then Exit Sub
     
        With ActiveSheet
             
            If .Cells(ActiveCell.Row, "B").Value <> "" Then
                 
                On Error Resume Next
                BPO_Cpl = Application.Match(.Cells(ActiveCell.Row, "B").Value, Worksheets("Task Flow").Columns("A"), 0)
                On Error GoTo 0
                If BPO_Cpl > 0 Then
                     
                    Worksheets("Task Flow").Cells(BPO_Cpl, "AS").Value = Date
                End If
            End If
        End With
    End If
End Sub

Another issue I'm having is sometimes when I click anywhere, whether it's on the main tab or the AM1 tab, the macro still wants to run, regardless of whether I executed the macro or not. I believe it's because of the code I have in the main Wkbk module ('ThisWorkbook') for sheet selection change event to recalculate. I have this set up to use in conjunction with conditional formatted where the active row is highlighted. The strange thing is, is that this behavior is intermittent?

Are you calling these subs from the Workbook_SheetChange event? if so, it is hardly surprising. I didn't understand the bit about conditional formatting. Maybe it is intermittent because you check the range in that calling code?

Also, can this code be amended so that it only works if a row somewhere in the table is selected? Rather than clicking on blank space anywhere on the page?

How can something not be selected?

[shellecj]

Scrubbed Wkbk.xlsmHi XLD,

I still seem to be having trouble. Now when I run the BPO completed macro, I get an Else without if but it when I look at the code, it looks like every if is followed with an end if, so can't figure out where I'm going wrong? Also, I'm still having a lot of trouble with the BPOCompleted macro wanting to fire off randomly just when clicking around in the worksheet. I even got this when I switched to a completely different workbook for something else.

As for the macro that works with conditional formatting I have in the workbook module the code set to recalculate whenever I click on a row, then in the conditional formatting rules I have set up a rule to apply to all of the tables to calculate the entire row that is selected with this formula --> =ROW()=CELL("row") and apply a fill color. This changes as I click different rows. I have been using this trick for a couple years now and never had any problems with other code conflicting with it; especially when it's a macro that's only executed (or supposed to be anyway) by the click of a button, selected from the macros list or from the VBE code window.

As for my question about the macro only being able to work when the are clicked on a row in the table ("AM1_Outstanding_BPOs"). I'm concerned that if they unintentionally clicked, say row 37 in the next task table down, the code would run on the record there? Though the message boxes should help prevent this for the most part, one might get rushed and not catch the property address read back to them in the msgbox was for a different property with a similar sounding address.

Can you please take a look at the attached workbook and test out the 3 macros attached to the macro buttons on the AM1 page (i.e. "Mark Complete", "Postpone" and "Restore Postponed Records") to see why I'm getting the Else without if message, why the code for this macro randomly seems to fire off or why I also seem to be having trouble sometimes with the 3rd macro to restore postponed records...sometimes it runs smoothly but sometimes it seems to run slow with a lot of flickering (I do have screenupdating turned off for the code). And should I dim the variable "aMgr" in that code? No matter what I try declaring the variable as, I get errors, etc.

Sorry so many questions and thanks again for all your help!

[Bob Phillips]

The Endif problem is because you have twoi statements on one line for the first if, you need to break them with that Else construct.

Sub BPOCompleted()
    Dim BPO_Cplt As Long
    Dim property As String
    property = ActiveSheet.Cells(ActiveCell.Row, "E").Value

    If MsgBox("Mark " & property & " as complete?", vbYesNo + vbExclamation) = vbNo Then
    
        Exit Sub
    Else
        If MsgBox("BPO will be marked as completed on main " & vbLf & "tab and property removed from this page.", vbOKCancel) = vbCancel Then
        
            Exit Sub
        Else
            With ActiveSheet
    
                If .Cells(ActiveCell.Row, "B").Value <> "" Then
    
                    On Error Resume Next
                    BPO_Cplt = Application.Match(.Cells(ActiveCell.Row, "B").Value, Worksheets("Task Flow").Columns("A"), 0)
                    On Error GoTo 0
                    If BPO_Cplt > 0 Then
                        Worksheets("Task Flow").Cells(BPO_Cplt, "AS").Value = Date
                    End If
                End If
            End With
        End If
    End If
End Sub

I can't see that second issue of firing off the macros randomly, I don't seem to be able to reproduce.

Not sure why it is flickering, but you can iprove it by turning calculation mode to manual at start, turning events off, clear the filters on Task Flow, and reset at the end.