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?

 

[rabsofty]

As far as i know when you make any changes (eg: hiding a sheet) you have to save the workbook.

You could hide your sheets during the workbook open and unhide them when you login.

I have a financial program i built and got around it sort of.
all my data is stored in encrypted files.
when my workbook closes, it clears all data and saves the workbook.
when the workbook opens, nothing can be seen until the user logs in and then data is loaded in.

Can you provide your workbook open event code

 

[Micron]

Untested, but thinking this:
In the close event, test the saved property. If the wb is being closed with unsaved changes, the property value is False. You could prompt or not. If not, set it to True and that should discard the changes. Then call your sheet hiding code, then save, then close.

 

[cart0250]

You could prompt or not. If not, set it to True and that should discard the changes.

Hi... testing this, and i don't think setting the saved property to True discards any changes since last save... unless of course you close the workbook directly afterwards. But if I set to true, then call sheet hiding code, save and close, it retains the changes that were made and that the user did not necessarily want to save.

 

[Micron]

According to this, changes are not saved. See example two.

Use Saved property to determine if workbook has changed - Microsoft 365 Apps

Describes how to use Saved property to determine if a workbook has changed and provides sample macros.

learn.microsoft.com

You have proved that changes are saved, or you are just making that assumption?

 

[cart0250]

when my workbook closes, it clears all data and saves the workbook.

Thanks for reply. What if they want to close the workbook without saving the data they've changed? Do you just not allow that option?

According to this, changes are not saved. See example two.

Use Saved property to determine if workbook has changed - Microsoft 365 Apps

Describes how to use Saved property to determine if a workbook has changed and provides sample macros.

learn.microsoft.com

You have proved that changes are saved, or you are just making that assumption?

Thanks for reply. If you close the workbook after setting the property to True, the changes are not saved as in their example.

Sub CloseWithoutChanges()
    ThisWorkbook.Saved = True
    ThisWorkbook.Close
End Sub

But in your original response, you said setting this property to true would 'discard the changes' and that i'd then be able to call the sheet hiding code and then save and close. That doesn't seem to work. Any changes the user made since previous save still exist after the sheet hiding code is called and then are saved when save command is executed before close.

I suppose what i'm looking for here is a way to revert the workbook to the state it was in immediately subsequent to the last save (thereby discarding any user changes).... then call the sheet hiding code and save. That way the user's changes wouldn't be saved but the sheets would get hidden before close.

I've also been looking at other posts suggesting to close the workbook without saving (which the above code does), then immediately reopen it, call the code, and then resave and close. Haven't got that idea to work yet though.

 

[Jaafar Tribak]

I was once in a similar situation and I opted for hiding the workbook file from the user (Placed it somewhere in an obscure hidden folder) and using an intermediary workbook for opening the hidden workbook file programmatically (Think of it as a shortcut to your actual target workbook).

- The user will open the Intermediary file. This intermediary file has one single worksheet with a message advising that macros must be enabled.
If the user has macros enabled, the intermediary file runs its Workbook_Open event to display the userform login form, open the workbook and then automatically closes itself. AutomationSecurityLow is the default setting but you can explicitly set it before opening the workbook programmatically, just in case

- In the workbook before close event, make sure you run Application.RecentFiles("TheWorkbookFullNameGoesHere").Delete otherwise, the workbook which is supposed to be hidden from the user would be accessible via the Excel File|Open menu.

Using this workaround, you won't have to worry about hiding the worksheets and saving the workbook before closing.

BTW, you could also use a small VBScript (if this is allowed by the system admins) to open the workbook instead of using an intermediary workbook. This is probably a much cleaner workaround as it won't show the intermediary excel screen .

 

[cart0250]

I was once in a similar situation and I opted for hiding the workbook file from the user (Placed it somewhere in an obscure hidden folder) and using an intermediary workbook for opening the hidden workbook file programmatically (Think of it as a shortcut to your actual target workbook).

- The user will open the Intermediary file. This intermediary file has one single worksheet with a message advising that macros must be enabled.
If the user has macros enabled, the intermediary file runs its Workbook_Open event to display the userform login form, open the workbook and then automatically closes itself. AutomationSecurityLow is the default setting but you can explicitly set it before opening the workbook programmatically, just in case

- In the workbook before close event, make sure you run Application.RecentFiles("TheWorkbookFullNameGoesHere").Delete otherwise, the workbook which is supposed to be hidden from the user would be accessible via the Excel File|Open menu.

Using this workaround, you won't have to worry about hiding the worksheets and saving the workbook before closing.

BTW, you could also use a small VBScript (if this is allowed by the system admins) to open the workbook instead of using an intermediary workbook. This is probably a much cleaner workaround as it won't show the intermediary excel screen .

Thanks for these ideas. Much appreciated!

 

[cart0250]

Thanks for these ideas. Much appreciated!

Thanks for these ideas. Much appreciated!

I suppose I could also password protect the main file and have the intermediary file pass in the password during open event. That way even if user found the main file they couldn’t open it.

 

[Jaafar Tribak]

I suppose I could also password protect the main file and have the intermediary file pass in the password during open event. That way even if user found the main file they couldn’t open it.

Yes. You can do that.
Also, I would password protect the intermediary file as well as the main file VBProjects.