-1

I have an idea that i'd like to get someones take on. So let's say I have an excel spreadsheet and here is what i'm trying to do. if i have an .xlsx file on my desktop - am i in any way shape or form code it so that when I click on the file, it asks the user to log in before the workbook even opens? the reason why I am asking is because the excel spreadsheet has macros in it. So upon opening the workbook, it firsts prompts the user to enable editing, before any of the ThisWorkbook_OPEN event code gets executed(in which case I hide all the tabs but one empty one, display login form; after loggin in, i show the tabs). Until they click enable editing, they are able to do whatever. Anyone have any ideas how this could be done?

vacip
  • 5,246
  • 2
  • 26
  • 54
BobSki
  • 1,531
  • 2
  • 25
  • 61
  • 1
    Are you [looking for this functionality?](http://www.excel-easy.com/basics/protect.html) – Jimmy Smith Sep 23 '16 at 20:29
  • @JimmySmith - partially, but i'd like to create my own user form basically. I need a logo on it and disclaimer. But thank you! – BobSki Sep 23 '16 at 20:32
  • 1
    Well, then [I wonder if you need something like this](https://turbofuture.com/computers/Excel-VBA-Create-A-Basic-Login-Function) – Jimmy Smith Sep 23 '16 at 20:35
  • @JimmySmith - i like this. But I already do have a login page, here is my issue. The spreadsheet has macros in it. On Workbook_Open I want to show user the LoginForm that is very very nice. The problem is, since spreadsheet has macros, and users open it for first time, it asks them to ENABLE EDITING, once they click that, the loginForm pops up and asks them to login. That's my issue, until the click enable editing they can roam freely and make edits to the spreadsheet. I'm trying to prevent that from happening. – BobSki Sep 23 '16 at 20:38
  • 1
    Aha, I was not paying attention to that piece of it. I feel [we are getting closer](http://stackoverflow.com/questions/25889742/remove-protected-view-from-excel-sheet-opened-programmatically-in-access) Caveat is, this may require another workbook has been opened previously... but you could give it a shot. – Jimmy Smith Sep 23 '16 at 20:42
  • 2
    Why don' t you hide all the sheets as "xlSheetVeryHidden" and make a splash screen in the only visible tab that alerts the user to enable editing to open the log-in form. By putting a password on the VBA, they won't be able to unhide any of the sheets unless they used VBA from another open workbook. If they have that level of skill, though, they'd probably be able to break whatever security you put on it as Excel is inherently not secure. – OpiesDad Sep 23 '16 at 20:56
  • 3
    I'm sorry to disappoint, but in my experience, there is no way to properly protect an Excel workbook. That is simply not what Excel s built for. Even if you manage to force your users to enable macros, what's stopping them from saving the workbook as an xlsx file with no macros in it? So unless you have very cooperative very basic users, you can't protect Excel. – vacip Sep 23 '16 at 21:01
  • @OpiesDad - they have no skill whatsoever. This is what I exactly do. I hide all the sheets on ThisWorkbook_Close except for one sheet. This becomes the template, so on the initial opening of the spreadsheet (wher it requires them to enable editing), there's only one sheet and everything else is hidden. They will only get that prompt once. But now that this template is hidden, i go back into the vba and comment that code out, so it doesn't hide it every time they close the workbook - see what I mean. I only make that template for when they open it with the prompt. – BobSki Sep 23 '16 at 21:02
  • @OpiesDad then every other time they open it on their box, they won't see the prompt, in that case the login form pops open, i set focus to empty tab and they must login. – BobSki Sep 23 '16 at 21:03
  • @vacip - i hear ya. thanks – BobSki Sep 23 '16 at 21:03
  • I don't see what you mean. You can hide a sheets as "Hidden" or "VeryHidden." If it is just "Hidden", the user can unhide the sheet. If it is "VeryHidden," it can only be unhidden using VBA or at least the Project Editor. As vacip stated, Excel just isn't secure. You can't secure it. – OpiesDad Sep 23 '16 at 21:04
  • @opiesdad i understand and these are veryhidden. But i dont know if i should hide them everytime they close the workbook because in that case i have to do thisworkbook.autosave=true. But users might enter some data and then not want to save the spreadsheet so next time they open it the tabs wont be hidden anymore. See what i mean now? – BobSki Sep 23 '16 at 21:08
  • @opiesdad the only way for me to hide them woild be on workbook beforeClose event right? – BobSki Sep 23 '16 at 21:09
  • You could prompt them about whether they want to save the workbook or not. If you never save the workbook with the tabs unhidden, how will they open it next time with the tabs not hidden anymore? – OpiesDad Sep 23 '16 at 21:10
  • @opiesdad so basically if i dont hide the sheets before workbook closes and then they open it again on another computer and are prompted to enable editing all sheets will bebvisible to them since the code in workbook open event doesnt get executed until they click on enable editing – BobSki Sep 23 '16 at 21:10
  • That would certainly be a good way. I hesitate to say the "only" way, but I don't see any problem with it. – OpiesDad Sep 23 '16 at 21:11
  • If you send them a workbook that has all the tabs hidden and they open the workbook, but never save it, then whenever they open the workbook somewhere else, it will open the original copy that has the hidden tabs. How are they saving it with unhidden tabs? – OpiesDad Sep 23 '16 at 21:11
  • @opiesdad when they open it i unhide the tabs and do autosave at that point. But i cant force users to save it or do autosave =true everytime they close the workbook – BobSki Sep 23 '16 at 21:15
  • why are you autosaving the workbook when they open it? – OpiesDad Sep 23 '16 at 21:16
  • Let me make sure I understand this correctly. The *only time* that there will be code in the workbook is the first time the user opens it? – Comintern Sep 23 '16 at 21:17
  • @opiesdad the only way for me to hide all the tabs is on workbook close, hate to say it but it is. Thays why i tey to hide them only for the first time they open it because it will ask them to enable editing then everytime after when they try to open it all tabs are visible but focus is set to empty tab while they log in – BobSki Sep 23 '16 at 21:17
  • @comintern only time i hide all the tabs is for the first time they open it im able to skip the login form and commwnt out the code that hides it. Everythijg else, the login form code and such is still as it was. I only comment out the code on workbook before close event – BobSki Sep 23 '16 at 21:21
  • Another choice is to compile the Excel file to executable, but it's not free https://www.google.com/search?q=compile+excel – Slai Sep 23 '16 at 21:22
  • You are asking for design help. Your design is as follows: 1) Give user workbook with tabs hidden except one. 2) User opens workbook and clicks "Enable Editing". 3) Macro runs asking user to sign on. 4) Once user signs on, the tabs are unhidden. 5) Workbook is autosaved with tabs unhidden. 6) User does work. Might want to save the workbook again. 7) User closes workbook 8) Possibly hide all the sheets again, but maybe not. 9) User cannot open workbook without tabs unhidden. Also code automatically runs without user doing anything. – OpiesDad Sep 23 '16 at 21:23
  • I am questioning part 5 as it doesn't make any sense to me, especially given that it makes several later steps not really possible. – OpiesDad Sep 23 '16 at 21:24
  • What exactly is the use case for this? It's beginning to sound like what you really need is a separate "front-end" and "back-end". – Comintern Sep 23 '16 at 21:25
  • I guess if you don't want them to "freely roam" then the only choice is to hide the tabs before they open it and protect them somehow. You can't force the macro to run unless the user allows it. – OpiesDad Sep 23 '16 at 21:29
  • And the only way to hide the tabs before they open it is to hide the tabs when they close it. – OpiesDad Sep 23 '16 at 21:29
  • 1
    AFAIK Excel password protection can be circumvented in less than a minute. Just one of many examples https://www.youtube.com/watch?v=xW0YMtjiPxc – Slai Sep 23 '16 at 21:40
  • @slai no ones going to be doing that but thanks – BobSki Sep 23 '16 at 22:05
  • @opiesdad the reason why i autosave is because i wont know if user will save changes after logging in. So since i unhide the same and am not sure if user will save it, i autosave it automatically. Next time when user opens it excel will not ask to enable content (it only asks once), so my code on OPEN event sets focus to empty sheet and login form pops up – BobSki Sep 23 '16 at 23:02
  • @opiesdad i dont need to and also cant hide the sheets again(since users might not save changes when closing workbook). I can only definitely hide the sheets once beforenthe first time they open it. – BobSki Sep 23 '16 at 23:04

2 Answers2

2

No, you can't run code from a workbook that hasn't even opened yet.

The first event to be triggered is the Workbook_Open event, but before that it the layers of security that Excel processes for good reason (would you be happy for arbitrary code being allowed to run from some file you just downloaded from a website without asking your permission first?). It can be annoying at times but you should always code with the intention of working with system security features, not working around them.

And to be honest - if you've got something that it so confidential that you need to protect it this heavily, it probably shouldn't be in an Excel file in the first place. Excel workbooks are not secure - I am a novice programmer at best and I can break into a password protected Excel workbook / VBA project well within 5 minutes.

SierraOscar
  • 17,507
  • 6
  • 40
  • 68
  • I agree - for this reason I've gotten rid of the log in form and am using excel password option to secure the spreadsheet. Thank you. – BobSki Sep 26 '16 at 13:46
1

For locking a workbook before it has opened, office provides a locking mechanism.

Password Management Documentation.

Once the sheet has opened, if the macro is not enabled by the user, there is no way to ensure running of macro. The only alternative is to save the workbook in a trusted location. In this case, macro will run without a prompt. (Very unsafe)

Trusted Location Documentation.

Once you have managed to run the macro, then what you suggested is possible. There may be two approaches:

  1. Run the macro to show a form with user id & password. If the password is correct, allow the excel to be on. Otherwise, close it.

  2. Make an additional sheet with two cells acting as form. Run macro to verify the details and allow access.

Locking of sheets could be done using xlveryhide feature of excel.

XLVeryHide

Som Shekhar
  • 138
  • 10