Hi All,
I have a couple of requests that I need help with.
The first problem:
I want to track changes/updates I make to a single column (Status) every time a change is made. So every time there's a transaction on a case, I post the latest update on it. But this as you can imagine will become huge and almost impossible to read if you do not expand the cell size after wrapping text and so on. What I need is for it to display the latest input only while showing any changes/updates made to that particular column in the form of a note over that cell or when I hover the mouse over it. I had read up about this and found the following VBA, however, it tracks all changes I make to the sheet and that can get really annoying. I'm completely a beginner with the VBA's and will appreciate any help.
The earlier code I found that tracks all changes is:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'copy previous value to another sheet
Sheet2.Range(Target.Address) = Target
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'//clearing more than one cell causes an error
On Error Resume Next
'//(can't overwrite an existing comment)
Target.ClearComments
With Target
'get the previous value when value changes
.AddComment
.Comment.Visible = False
.Comment.Text Text:="Previous value = " & Sheet2.Range(Target.Address)
End With
End Sub
Problem two:
I also need to track certain transactions and so I store the dates of when a case is filed and then I need to follow up on it on the 13th day to check if I have received a response from the 'Assignee'. However, if that person has contacted me and completed the task he was required to (I mark 'Assignee has completed biometrics' in the 'Status' column), then I just need to follow up with a third party. This may sound easy/confusing, but I have been breaking my head with a IF function and that did the trick for only one condition, and as soon as I wanted it to compare the value on another cell the formula failed as it would still only calculate the first IF function. Anyway, cutting the long story short, I am now in need of another VBA to check if the following:
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting
I created an additional column called 'Biometrics & Application Follow-up', as I didnt know how to track via a VBA. What I now want to do is to be alerted when it has been 13 days after the application submission date, to 'Check with the Customer', however, if the Status column reads 'Assignee completed biometrics', I should be prompted to 'Check Case progress with STAR-DIS'.
Finally, could I also automate the excel sheet to send out emails automatically for cases where I havent received any update from the 'assignees' on the 13th day from the 'Application Submission Date'.
Thank you for reading my detailed problem list.
Reuben
I have a couple of requests that I need help with.
The first problem:
I want to track changes/updates I make to a single column (Status) every time a change is made. So every time there's a transaction on a case, I post the latest update on it. But this as you can imagine will become huge and almost impossible to read if you do not expand the cell size after wrapping text and so on. What I need is for it to display the latest input only while showing any changes/updates made to that particular column in the form of a note over that cell or when I hover the mouse over it. I had read up about this and found the following VBA, however, it tracks all changes I make to the sheet and that can get really annoying. I'm completely a beginner with the VBA's and will appreciate any help.
The earlier code I found that tracks all changes is:
Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'copy previous value to another sheet
Sheet2.Range(Target.Address) = Target
End Sub
Private Sub Worksheet_Change(ByVal Target As Range)
'//clearing more than one cell causes an error
On Error Resume Next
'//(can't overwrite an existing comment)
Target.ClearComments
With Target
'get the previous value when value changes
.AddComment
.Comment.Visible = False
.Comment.Text Text:="Previous value = " & Sheet2.Range(Target.Address)
End With
End Sub
Problem two:
I also need to track certain transactions and so I store the dates of when a case is filed and then I need to follow up on it on the 13th day to check if I have received a response from the 'Assignee'. However, if that person has contacted me and completed the task he was required to (I mark 'Assignee has completed biometrics' in the 'Status' column), then I just need to follow up with a third party. This may sound easy/confusing, but I have been breaking my head with a IF function and that did the trick for only one condition, and as soon as I wanted it to compare the value on another cell the formula failed as it would still only calculate the first IF function. Anyway, cutting the long story short, I am now in need of another VBA to check if the following:
Image - TinyPic - Free Image Hosting, Photo Sharing & Video Hosting
I created an additional column called 'Biometrics & Application Follow-up', as I didnt know how to track via a VBA. What I now want to do is to be alerted when it has been 13 days after the application submission date, to 'Check with the Customer', however, if the Status column reads 'Assignee completed biometrics', I should be prompted to 'Check Case progress with STAR-DIS'.
Finally, could I also automate the excel sheet to send out emails automatically for cases where I havent received any update from the 'assignees' on the 13th day from the 'Application Submission Date'.
Thank you for reading my detailed problem list.
Reuben
