PDA

View Full Version : macro to lookup record on current row and mark completion date to corresponding recor



shellecj
01-01-2014, 04:08 PM
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 2nd 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
01-01-2014, 04:41 PM
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
01-01-2014, 04:46 PM
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
01-01-2014, 05:32 PM
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. :dunno

Bob Phillips
01-02-2014, 01:31 AM
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
01-04-2014, 08:35 PM
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
01-05-2014, 10:28 AM
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
01-05-2014, 06:47 PM
11057Hi 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
01-06-2014, 01:44 AM
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.