Hiding Sheets during the Before_Close Event

[cart0250]

Good day,

I've built a vba project that displays a userform login form via the open event. If user opens the document with macros disabled, the open event doesn't trigger and the user login form doesn't open. To address this, I've created a 'launch sheet' that is made visible during the close event while all other sheets are made 'very hidden'. If user opens with macros enabled and enters correct login/password, the launch sheet is re-hidden and the applicable sheets are made visible. If user opens with macros disabled, all they see is the launch page with a message advising must enable macros. Combined with VBA password protection, this approach appears to protect the document at least against the casual user.

However... the above only works if i force save the document during the close event. If I allow the user to close without saving, the 'very hide' code within the close event has no impact since the visible property changes aren't saved. So the way i have this set up currently the user has no way to close the document without saving their changes (except maybe thru task manager (?) etc. haven't tested that).

Is there any workaround here? A way to save changes to the visible sheet properties during the close event without requiring the user to save changes to the actual data?

 

[Jaafar Tribak]

@cart0250

If you go the VBScript route, here is a small script that you can write in NotePad.

Save the notepad text file as .txt file and then change its extension to .vbs
Note that the script below assumes the main workbook is password protected for opening and the password is 1234. Change to suit.

Dim xl,sPath,Rf,i

sPath="C:\Users\MySecretWorkbook.xlsm"  '<== change the file PathName as required.
on error resume next
Set xl=GetObject(,"Excel.Application")
If xl Is Nothing Then
  Set xl=CreateObject("Excel.Application")
  xl.Visible=True
end if
xl.AutomationSecurity = 1
xl.Workbooks.Open sPath, , , , "1234"    '<== change file password as required.
For Each Rf In xl.RecentFiles
  If sPath = Rf.Path Then
    i = i + 1
    xl.RecentFiles.Item(i).Delete
  End If
Next

This next step is optional but to make it more friendly for the user, you can create a shortcut to the newly created vbs file and then right-click the shortcut icon in explorer, go to Properties,, shortcut tab and change icon. You can pick any icon you want from the icons available as shown below: There are icons related to excel.

Now all the user will have to do is click the vbs file (or the vbs shortcut if you created one) and the script will automatically open the workbook with macros enabled.
If there is an excel session already open and running, the workbook will open in it, otherwise it will open in a new excel instance.

Now, the only disadvantage of this vbs approach is the fact that you can't open it from within excel File|Open menu. It will have to be opened from the shell explorer by clicking the vbs file (or its shortcut)