Workbook_Open event that cannot be disabled...?

[SunGodly]

Okay, this should be an easy one for you veteran developers. I am just starting to develop projects professionally and I would like to come up with a routine that will prompt the user for a password after a specified date has passed, then lock down the workbook if the password is not supplied. Basically, I want to ensure that I am paid for my work, and I will supply the password once the bill is paid. My thought is that the routine should run on workbook open but the user should not be allowed to 'disable macros' to circumvent. Any ideas?

Thanks,
Scott Hicks
Mighty Oak

 

[rmikesmith]

Scott,

You cannot prevent the user from disabling macros. Therefore, you need a different strategy. I have seen the following used, not for the purpose you intend, but to ensure macros are enabled so that the application functions. You should be able to use it also. Your application will have a single sheet visible at startup, which displays a message regarding enabling macros. All other sheets are hidden by setting the Visible property to xlVeryHidden. Sheets hidden in this manner cannot be made visible from the user interface. After the user enters the proper password, your code would then unhide the necessary sheets. Here is some sample code to hide all worksheets except the initially displayed one:

Sub HideWorksheets()
Dim Wks as Worksheet

  For Each Wks In Thisworkbook.Worksheets
    If Wks.Name <> &quot;NameOfYourInitialWorksheet&quot; Then
      Wks.Visible = xlVeryHidden
    End If
  Next Wks

End Sub

HTH
Mike

 

[SunGodly]

Thanks, Mike. I thought of that one but any other developer will be able to unhide those sheets via another macro. I thought of another possible solution, although I haven't had an opportunity to try it. Maybe you can tell me if this sounds feasible: I know Windows allows you to set a password to open a file. Is there any way to do this through VBA? Again, I haven't had a chance to toy with this idea yet but it may be an easier solution. Let me know what you think, if you get a chance.

Thanks.

 

[kylua]

In tools, project properties, you can password protect the macros. No more macros can be written or even recorded until the password is provided.
So there is no way of unhiding the sheets without that password.

 

[SunGodly]

Kylua, thanks but there is a way around that. If you open a workbook with hidden sheets, you can run an unhide macro from another workbook to show those sheets. I appreciate your input, however. This is turning out to be trickier than I thought!

Thanks.

 

[kylua]

You're right, hadn't thought of that. But if you password protect the workbook, won't that stop it anyway?

Alternatively, make a large part of the functionality of your workbook disabled until you run a password protected macro. That macro can put in the key formulas etc without which the book will be useless.