Solved

Excel Spreadsheet - log time of click of checkbox

Posted on 2010-09-10
12
456 Views
Last Modified: 2012-06-22
Hi

I have never programmed in excel but have lots of programming with Delphi, and have been given a problem of logging the time a particular checkbox was ticked.

This will apply to about 140 individual checkboxes and ideally the results need to be in a password protected area, so the person doing the ticking can't mess with the result

thanks
0
Comment
Question by:rcdcsjdc
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 5

Expert Comment

by:briangochnauer
ID: 33645642
Not possible without installling "keylogger" software.
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33647097
"Keylogging" has nothing to do with it.

The short answer is that yes, almost certainly you can do this.

The catch, of course, is two-fold:

1) If macros are not enabled, it will not work, and depending on Group Policy the end user may have some control over that

2) The password protection offered by Excel here (protecting worksheets + workbook structure, protecting VBA project) is extremely weak, and can be hacked in seconds using cheap and readily available tools.

Still interested?
0
 
LVL 33

Expert Comment

by:Norie
ID: 33655720
Like matthew has said this is likely to be possible, but why 140 checkboxes?

What is it you are actually logging?

0
 

Author Comment

by:rcdcsjdc
ID: 33669962
Thank you Matthewspatric and imnorie.
I always knew it would be possible and i suspected that it could be hacked by competent people.
The operatives that will be ticking the boxes are play centre operatives and the manager informs me that both ability levels and interest are low. It's more a manual operative environment so the likelihood of suffucient skill is very low and the benefits relatively minor.
There are so many checkboxes because each one indicates a task done. There are 20 tasks and they are done daily for health and safety reasons like checking of fridge temperatures etc, so the records are required to be kept by law. The main current issue is that there are mountains of unfiled illegible paper that continues to build, so I have been requested to provide this spreadsheet so they can indicate that they have done the check and the time it was done.
Thanks
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 33671439
Based on your description, I am thinking that you would ultimately be better off implementing this as an intranet tool.  This has a major advantage: it keeps all of the data in a central store at every step of the process, and that data store can be secured.

It may require more work up front to get started, but long term I think it is the way to go.
0
 
LVL 5

Expert Comment

by:briangochnauer
ID: 33671591
I had a brain fart. check the file attachment.
TimestampCkb.xls
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 5

Expert Comment

by:briangochnauer
ID: 33671843
I like using a form like this, it's a cleaner interface.
TimestampCkb.xls
0
 

Author Comment

by:rcdcsjdc
ID: 33673986
Than you briangochnauer
I downloaded the code but I get the error
cannot run the macro "TimestampCkb(1).xls!CheckBox1_Click, the macro may not be available in this workbook or all macros may be disabled
I have tried all macro security settings including enable all macros but to no avail, so I am assuming something is missing in this sheet
Thanks
0
 
LVL 5

Expert Comment

by:briangochnauer
ID: 33690907

Then you'll have to create the macro;
Open a sheet insert a checkbox on it;
Right-click on checkbox then click 'Format Control' then choose 'Control' tab
In 'Cell Link' type $H$1,  click 'OK'

Right-click on the checkbox and 'Assign Macro' click 'Record'
Insert  (Bold );

Sub CheckBox1_Click()
    If CheckBox1  Then
          Worksheets(1).Range("I1").Value = Now

     End If
End Sub

Of course the worksheet range storing the value could be any sheet even a hidden sheet.
0
 
LVL 5

Accepted Solution

by:
briangochnauer earned 500 total points
ID: 33690988
Remove the 'IF' statement
Sub CheckBox1_Click()
         Worksheets(1).Range("I1").Value = Now
End Sub
 
0
 
LVL 5

Expert Comment

by:briangochnauer
ID: 33691323
You could actually do it without using a macro (simplify) by
Right-click on checkbox then click 'Format Control' then choose 'Control' tab
In 'Cell Link' type $H$1,  click 'OK'
then in cell i1 use the formula      =IF(H1=TRUE,NOW(),"")

ClipXLS.jpg
0
 

Author Closing Comment

by:rcdcsjdc
ID: 33691467
gave enough information to solve what was asked so I am happy
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now