Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions


Reply
 
Thread Tools Search this Thread Display Modes
  #1  
Old 06-07-2019, 09:02 AM
Bicycle Repair Man's Avatar
Bicycle Repair Man Bicycle Repair Man is offline
Member
 
Join Date: Nov 2003
Posts: 14,347
Default Protecting a sheet in Excel

I just built a spreadsheet to keep track of my students' trips to the bathroom. By tapping buttons linked to macros, a log is created on another sheet of all of the times they logged in an out, and then with a simple formula I can keep track of the cumulative time they've been out of my room.

Is there a way I can password protect just the sheet with data from being manipulated directly? I want to leave this spreadsheet running near the door to my classroom so that students can check themselves in and out, but I want to block them from the ability to go to the tab with all the raw data, or they could just manually delete some of it. Of course, I would like to have the ability to do this myself if necessary.
__________________
Farewell, Necco Wafers. At least you died the way you lived: tossed away after someone realized they accidentally bought Necco Wafers. - Cedric Voets, Cracked.com
Reply With Quote
  #2  
Old 06-07-2019, 09:10 AM
yoyo's Avatar
yoyo yoyo is offline
Member
CAS
 
Join Date: Dec 2001
Posts: 23,725
Default

is this a parody thread?
Reply With Quote
  #3  
Old 06-07-2019, 09:18 AM
MayanActuary's Avatar
MayanActuary MayanActuary is offline
Member
SOA
 
Join Date: Jul 2010
Posts: 1,798
Default

Tracking bathroom visits seems odd, but that aside...

Multiple ways to do it, but I would just make the excel sheet "very hidden" and then password protect VBA. You cannot unhide it without a macro that I am aware of if it is very hidden.

Sub VeryHiddenSheet()
Sheets("Sheet1").Visible = xlVeryHidden
End Sub

And then to unhide it for yourself

Sub UnhideSheet()
Sheets("Sheet1").Visible = True
End Sub
Reply With Quote
  #4  
Old 06-07-2019, 09:26 AM
Kenny's Avatar
Kenny Kenny is offline
Member
Non-Actuary
 
Join Date: Jan 2003
Posts: 8,172
Default

Quote:
Originally Posted by MayanActuary View Post
Tracking bathroom visits seems odd, but that aside...

Multiple ways to do it, but I would just make the excel sheet "very hidden" and then password protect VBA. You cannot unhide it without a macro that I am aware of if it is very hidden.

Sub VeryHiddenSheet()
Sheets("Sheet1").Visible = xlVeryHidden
End Sub

And then to unhide it for yourself

Sub UnhideSheet()
Sheets("Sheet1").Visible = True
End Sub
You can see the "status" of all sheets, including very hidden sheets, when viewing VBA code. Visibilty can be changed manually from here
Attached Images
 
__________________
Play Free Games and Win Real Money
I am a scientist. I am sorry to disappoint you but I have never seen an elf or a troll. But who am I to exclude their existence? - Arni Bjoernsson
You are stupid and evil and do not know you are stupid and evil. ... Dumb students are educated stupid. - timecube.com
Usually while I'm reading, I'm actually thinking about...midgets riding toy horses - Roto


Reply With Quote
  #5  
Old 06-07-2019, 09:31 AM
MayanActuary's Avatar
MayanActuary MayanActuary is offline
Member
SOA
 
Join Date: Jul 2010
Posts: 1,798
Default

Quote:
Originally Posted by Kenny View Post
You can see the "status" of all sheets, including very hidden sheets, when viewing VBA code. Visibilty can be changed manually from here
Didn't know that, thanks for sharing. Do you have to enter in the VBA password to change the status of the sheet? Or can you do that without the password?
Reply With Quote
  #6  
Old 06-07-2019, 09:54 AM
Kenny's Avatar
Kenny Kenny is offline
Member
Non-Actuary
 
Join Date: Jan 2003
Posts: 8,172
Default

Quote:
Originally Posted by MayanActuary View Post
Didn't know that, thanks for sharing. Do you have to enter in the VBA password to change the status of the sheet? Or can you do that without the password?
No clue, I have never password protected VBA.
__________________
Play Free Games and Win Real Money
I am a scientist. I am sorry to disappoint you but I have never seen an elf or a troll. But who am I to exclude their existence? - Arni Bjoernsson
You are stupid and evil and do not know you are stupid and evil. ... Dumb students are educated stupid. - timecube.com
Usually while I'm reading, I'm actually thinking about...midgets riding toy horses - Roto


Reply With Quote
  #7  
Old 06-07-2019, 10:23 AM
Vorian Atreides's Avatar
Vorian Atreides Vorian Atreides is offline
Wiki/Note Contributor
CAS
 
Join Date: Apr 2005
Location: As far as 3 cups of sugar will take you
Studying for ACAS
College: Hard Knocks
Favorite beer: Most German dark lagers
Posts: 66,131
Default

I was also going to suggest the idea of a xlSheetVeryHidden for the data.

But I don't think that the worksheet itself can be "password protected" . . . that is a workbook-level feature; but this only affects whether or not it can be edited. I think with the password protection in place, VBA code will still work to "record" (e.g., copy/paste info from one worksheet to another), but the user cannot.

With all that said, I think if you open the workbook in "Read Only" mode (i.e., don't enter the password, if enabled), then you will not have access to the VBA editor for that workbook.
__________________
I find your lack of faith disturbing

Why should I worry about dying? It’s not going to happen in my lifetime!


Freedom of speech is not a license to discourtesy

#BLACKMATTERLIVES
Reply With Quote
  #8  
Old 06-07-2019, 10:26 AM
Vorian Atreides's Avatar
Vorian Atreides Vorian Atreides is offline
Wiki/Note Contributor
CAS
 
Join Date: Apr 2005
Location: As far as 3 cups of sugar will take you
Studying for ACAS
College: Hard Knocks
Favorite beer: Most German dark lagers
Posts: 66,131
Default

Other things that you can also do is to "unprotect" those cells that you want the students to be able to "manipulate" (e.g., edit to input their name), then protect the worksheet (can be done by right-clicking the worksheet tab or through the VBA editor).
__________________
I find your lack of faith disturbing

Why should I worry about dying? It’s not going to happen in my lifetime!


Freedom of speech is not a license to discourtesy

#BLACKMATTERLIVES
Reply With Quote
  #9  
Old 06-07-2019, 10:38 AM
Bicycle Repair Man's Avatar
Bicycle Repair Man Bicycle Repair Man is offline
Member
 
Join Date: Nov 2003
Posts: 14,347
Default

If the workbook is protected, will the macros still be able to change the values of cells?
__________________
Farewell, Necco Wafers. At least you died the way you lived: tossed away after someone realized they accidentally bought Necco Wafers. - Cedric Voets, Cracked.com
Reply With Quote
  #10  
Old 06-07-2019, 10:46 AM
BruteForce's Avatar
BruteForce BruteForce is offline
Member
SOA AAA
 
Join Date: Apr 2013
Studying for More Money
Favorite beer: Wurzel Bier
Posts: 11,653
Default

You can password protect the VBA (In the VBA editor: Tools -> VBAProject Properties -> Protection). That will make it so that no one without the password could open up your file and see the worksheets or VBA code (there are ways around it, but I'm assuming your kids aren't going to be sitting at the computer for 20 minutes without you noticing them).

You can password protect just a worksheet (In my version of Excel: Review -> Protect Sheet). In you're macro you'll just have to add a worksheet.unprotect "password" to your code before you do any edits, then a worksheet.protect "password" to the end of your code.

So you could either hide the worksheet (xlVeryHidden) or protect the cells and have the macro unprotect then reprotect them after the data is entered. If you use xlVeryHidden you may have to have the macro unhide the worksheet to enter data into it, depending on your macro.
__________________
ASA

Quote:
Originally Posted by Actuary321 View Post
I would really hate to bring Pokémon to a gun fight.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 04:21 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.26874 seconds with 10 queries