Excel VBA update a cell from a single checkbox, with the checkbox linked to a randomly selected unique identifier and return a Timestamp

Hi,  in MS Excel I have 10,000 rows of data in 35 columns in Worksheet A, the unique identifier for each row is contained in Column C called the SaleID which is alphanumeric (sometimes an Integer or string).

I have set up in Worksheet B a simple form that acts like a report, extracting data from Worksheet A, primarily using Vlookup, based on any manually entered SaleID in cell A1 of Worksheet B.

I've tried my best at writing some code but it's completely baffling me and can't seem to figure it out...please can you help me with VBA code that works as follows (please follow through with the example at the bottom as well):

1. In Worksheet B, I enter a random SaleID in cell A1;
2. Then when I click on the single checkbox in cell B1 of Worksheet B the code should look at the SaleID in cell A1 and then go to Worksheet A and Column C and find the the associated SaleID for example row 254, and write, and permanently record, in Column D of the same row, either a 'Yes' (i.e., the checkbox is ticked) or a 'No' (i.e., the checkbox is not ticked);
3. Then each time a row in Column D in Worksheet A is updated to 'Yes', Column E, in Worksheet A, permanently records the Date and Time (24hr clock) (in format dd/mm/yyyy hh:mm) of the last update (which is different to =Now() and =Today(), in that these two functions change each time I open the workbook).
4. Lastly, in Column F, of Worksheet A, in each row I would like to calculate in days how much time has expired since the last update in Column E.

For example...

                                             WorkSheet A                                                         Worksheet B
Row          Col. C       Col D.              Col E.                 Col. F                       Col. A                 Col. B
1               SaleID      Listed           Updated                 +/-                        N09786        'Checkbox'
2               7428           Yes       01/02/2015 22:15    209 days
3               xx6250       No
...
254          N09786      Yes       20/08/2015  09:38      9 days
...  

I know this is quite challenging and perhaps difficult to program but I've yet to come across any example that someone has been able to do this.  Please do let me know if there are any questions.

Thank you in advance for all your help.

Best Regards
Jacque
Jacque de LacyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Roy CoxGroup Finance ManagerCommented:
An example workbook would be helpful
Jacque de LacyAuthor Commented:
Sorry about that...please find the attached example...
Roy CoxGroup Finance ManagerCommented:
Jacque  there is still no attachment. Have you clicked upload file after selecting it?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Jacque de LacyAuthor Commented:
Let me try it again...
Book1.xls
Martin LissOlder than dirtCommented:
Try the attached workbook. BTW don't worry about my use of Now. I believe it will change only if used in a formula.
28709675.xls
Martin LissOlder than dirtCommented:
Here's a second version where I created a dynamic Named Range called SaleIDs and used it to create a dropdown list in cell B1 on sheet Worksheet B.
28709675a.xls
Jacque de LacyAuthor Commented:
Hi,

Thanks for the sending this through to me, it's working very well but unfortunately there's a couple of tweaks required.  

The ranges specified are absolute and not defined names, so when I insert a column or row the macro stops working.

Also, I can't seem to change the names of any of the headings in Worksheet A without an error debugging.

Lastly, if I manually change Column D in Worksheet A, Column F doesn't appear to update automatically.

Best Regards
Muj
Martin LissOlder than dirtCommented:
The ranges specified are absolute and not defined names, so when I insert a column or row the macro stops working.
In the "a" version of the workbook I posted I added the SaleIDs named range that's dynamic as long as it's only one column.

I can't modify the other Named Ranges because they refer to ='Y:\Users\attic\Documents\Mujtaba\eBay Sales\[Auction Catalogues Inventory.xlsm]Data Tables (''Data_T'')'!..." and obviously I don't have that path. However the following describes how to create dynamic formulas.

Named Ranges
To correct the column header change error add line 2 to the following sub.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Row = 1 Then Exit Sub
If Not Intersect(Target, Range("D:D")) Is Nothing Then
    If Cells(Target.Row, "E") <> "" Then
        Cells(Target.Row, "F") = DateDiff("d", Cells(Target.Row, "E"), Now) & " days"
    End If
    Cells(Target.Row, "E") = Format(Now, "dd/mm/yyyy hh:mm")
End If
End Sub

Open in new window


For the last problem I assume you were typing in "yes" rather than "Yes" but change line 7 and see if that helps.
Sub FindSaleID()
Dim rngFound As Range

Set rngFound = Sheets("Worksheet A").Range("C:C").Find(What:=Sheets("Worksheet B").Range("B1"), LookIn:=xlValues)
If Not rngFound Is Nothing Then
    If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = xlOn Then
        UCase(Sheets("Worksheet A").Cells(rngFound.Row, "D")) = "YES"
    Else
        Sheets("Worksheet A").Cells(rngFound.Row, "D") = "No"
    End If
Else
    MsgBox "SaleID not found"
    Exit Sub
End If

End Sub

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Martin LissOlder than dirtCommented:
I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.