Benjamin Wilson7 said:I want to drop some VBA code, let's call it "time code", for a specific Excel file used by different people.
The file itself has an opening password, say "AB," which stays the same for everyone all year long.
The file has about 30 Sheets, and those are also locked with two different passwords (some with "CD," others with "EF," for example), but the data entry fields within those sheets are wide open.
Hard to say much without seeing a dummy version with fake data—that'd make it easier to see what you've already built. Just throwing out ideas here since my Excel 2007 is being weird and won't run Workbook_Open or Auto_Open events (no clue why)
But before I dive into the idea, you gotta consider how tech-savvy your users actually are. You really need to lock down the VBE access so nobody just snoops on your passwords; even then, anyone with enough time and brainpower can crack it.
Based on what you described, the plan is: Use certain VBA macros and VBA events
1. Set a global password for the Excel file via File => SaveAs (Tools). This is the one that pops up when you first launch the
Workbook. 2. You have 30 Sheets. Some Sheets should only be editable if you hit them with a specific password, right?
3. After a certain date, access to a specific Sheet or the whole Workbook gets cut off entirely.
There are plenty of ways to handle protecting the Workbook and Worksheets via VBA.
Here’s one way you could play it:
1. Set that global password like I mentioned, assuming there's no expiration date on the main file level.
2. Once they punch in the global password, land them right on Sheet1 so they can read instructions or whatever. To pull this off, you'll need to put something like this macro in the VBE ThisWorkbook module so it defaults to Sheet1 every time it opens.
Code:
Private Sub Workbook_Open()
Sheets("Sheet1").Activate 'lands you on Sheet1 when the workbook opens
call ProtectSheet2 'calls the protection procedure for Sheet2
Sheet2 call ProtectSheet3 'calls the protection procedure for Sheet3
Sheet3 end Sub
3. Put VBA code on all sheets that triggers a password prompt whenever someone clicks a sheet tab. Basically, when a user clicks a sheet name at the bottom of the Excel window, a dialog box pops up asking for the password before they can touch any data on that Sheet. Something like this macro:
Code:
Private Sub Worksheet_Activate() 'trigger when accessing the Sheet
Dim pasw As String, loz As String
pasw = "abc"
loz = InputBox("ENTER YOUR PASSWORD")
If loz = pasw Then
Sheets("Sheet2").Activate 'unlocks Sheet2 if password is correct
Else: MsgBox "WRONG PASSWORD", vbCritical, "Error"
Sheets("Sheet1").Activate 'kicks them back to Sheet1 if they fail
End If
End Sub
Next, inside that same Sheet's VBE right under that first macro, you’d drop in a second one to check the date. Basically, it limits access to that specific Sheet once a certain date passes. I haven't personally tested this exact combo, but
check online—there's a ton of similar macros out there. Here’s what that macro looks like (this part applies to Sheet2)
Code:
Sub ProtectSheet2()
If Now() >= DateSerial(2013, 9, 14) Then
MsgBox "OK, proceed with work"
'this is where you would place the protect code
Sheets("Sheet2").Unprotect Password:="abc"
Sheets("Sheet2").Protect Password:="33"
Else: MsgBox "Access denied. Contact the author."
Exit Sub
End If
End Sub
So, this macro checks the date; if it's passed, it wipes the old password used to get in ("abc") and swaps in a new one ("33") that only you know about (keep your own 😉
records). You can set this up on all Sheets, just be careful managing which password goes where. And obviously, you'll need to trigger all those Sheet macros within the Workbook_Open event
Hopefully, that gives you a decent starting point and some direction based on what you asked.
If you want to take the global approach, it’s actually way less work. You just do this:
1. Use VBA for preventing access to Workbook
2. Set up a Macro that checks the date
But leave the Sheets themselves accessible, or maybe just set passwords for specific user groups. Also, don't forget to look into File Sharing
Now, a quick question for you.
What happens if a user just rolls back the system date on their computer?
Think about this: once that expiration message pops up for the first user, you could have a Macro automatically lock down the entire file so it won't even open.
One more thing.
What if the user just disables Macros in Excel and opens the file without any running code (like by holding down the Shift key)?
You gotta think through every loophole. Though, honestly, against a real hacker? It's probably useless. 😉
