VBA Protect userInterfaceOnly and hidden cells?

**Iberlos** · 09-01-2024, 09:05 PM

Hello, I am using Excell for a personal project to learn a bit of Excell and VBA.

The Personal project is a simple TTRPG automated character sheet and I use protection just so the players don't mess with the cells that are used only for logic purposes such as the racial trait tables tables and etc. I want to hide the second worksheet called "GeneralTables", so the players can't see the tables, and the second page of the "Character Sheet" worksheet, so that players can share their screen without revealing their Character secret details that are on the second page of the Character sheet.

I have a ActiveX checkbox that protects and Unprotects the worksheets using the following VBA code, but the cells marked as Hidden are still visible. Is there an option to make them hide even if I am using the userinterfaceonly flag?

PHP Code:


Private Sub CheckBox1_Click()

    If CheckBox1.value = True Then

        Worksheets("Character Sheet").Protect "1234", userinterfaceonly:=True

    Else

        Worksheets("Character Sheet").Unprotect "1234"

    End If

End Sub

**6StringJazzer** · 09-01-2024, 10:32 PM

Can you be more specific about what you mean by "cells marked as Hidden"? How are they marked as hidden? When are they hidden and when are they visible?

1. Sheet protection is unrelated to whether cells are hidden or visible.

2. You can't make individual cells hidden, you can only make entire columns or entire rows hidden.

So because of these two facts, I can't make sense out of "cells marked as Hidden are still visible".

**Iberlos** · 09-01-2024, 11:38 PM

Oh, maybe I assumed wrong, but I thought the checkbox "Hidden" under Format Cells -> Protection meant that the cell would not be visible if the sheet is protected. Did I completelly misunderstand this?

**romperstomper** · 09-02-2024, 02:58 AM

Yes, I'm afraid you did. That checkbox only hides the contents of the cells in the formula bar. It's designed to stop people seeing what formulas are used in cells.

**Iberlos** · 09-02-2024, 03:51 PM

So, there is no way to hide some cells or at least a full worksheet when I distribute the final document?

I mean, I am working on something silly by what if you need to reference price tables or some information that is not for the end users eyes?

**6StringJazzer** · 09-02-2024, 05:01 PM

You could put the secret data in hidden columns or hidden rows. The best practice for underlying data that you don't want your user to see is put it on another sheet and hide that sheet altogether.

There are additional layers of "security" but Excel's features are for compliant users, not malicious ones. They can be broken.

If you want more advice specific your file, you can attach your file here and explain in more detail what you need to do.

**Iberlos** · 09-02-2024, 05:43 PM

Humm, ok, I was able to hide second sheet by right clicking and choosing hide. I guess I can just do that to the files that I send to the players.

Thank you all for your help.

**skywriter** · 09-02-2024, 05:57 PM

Very Hidden
You can use the xlVeryHidden property to hide a sheet and keep the Unhide dialog box from listing it.

**TMS** · 09-02-2024, 06:05 PM

Bear in mind that setting a sheet to xlVeryHidden can only be done in the VBA Editor. And it can be unset there too. Therefore, having set the sheet(s) to xlVeryHidden, you need to protect the VBA Project with a password.

That said, no amount of hiding sheets can prevent people from accessing the data, even if it is on a hidden sheet, if they know it is there and where to look. So,you should still lock and hide the formulae cells and protect the sheet with a password.

**skywriter** · 09-02-2024, 06:21 PM

Originally Posted by TMS

Bear in mind that setting a sheet to xlVeryHidden can only be done in the VBA Editor. And it can be unset there too. Therefore, having set the sheet(s) to xlVeryHidden, you need to protect the VBA Project with a password.

That said, no amount of hiding sheets can prevent people from accessing the data, even if it is on a hidden sheet, if they know it is there and where to look. So,you should still lock and hide the formulae cells and protect the sheet with a password.

Just so we're clear the purpose of my post was if those compliant users mentioned in #6 don't see something in an unhide list then that would be better than having it listed there.

**TMS** · 09-02-2024, 06:50 PM

Originally Posted by skywriter

Just so we're clear the purpose of my post was if those compliant users mentioned in #6 don't see something in an unhide list then that would be better than having it listed there.

No disagreement at all. I'm just trying to cover the bases. You never really know how "compliant" a user might be. Accidental discovery and curiosity can take you a long way.

Let's say I have a formula on another worksheet that refers to the (Very) Hidden sheet. That tells me a) that there is a sheet there but, for some reason, I can't see it. BUT, I can see the value (static or calculated) that is in that cell. And I can then determine if there is a formula in that cell, indeed, what that formula might be. That might point me at other specific cells or I might just looking generally.

So, thinking about it, it's actually the sheet that refers to the hidden sheet that needs to have formulae locked and hidden with the sheet protected.

So, no criticism intended. I agree that Very Hidden is better than just Hidden, and more difficult to access. I just think that there's a bit more to do, even for compliant users. If you're going to do it all, you best do a thorough job (IMO).

**skywriter** · 09-02-2024, 10:37 PM

So, no criticism intended. I agree that Very Hidden is better than just Hidden, and more difficult to access. I just think that there's a bit more to do, even for compliant users. If you're going to do it all, you best do a thorough job (IMO).

No worries I just wanted to clarify my motivation was 6StringJazzer's post and perhaps since we're talking about hiding stuff veryhidden might be better than regular hidden.
I agree with you both regarding security.
Thanks for the rep.

VBA Protect userInterfaceOnly and hidden cells?

LinkBack

Thread Tools

Rate This Thread

Display

VBA Protect userInterfaceOnly and hidden cells?

Re: VBA Protect userInterfaceOnly and hidden cells?

Re: VBA Protect userInterfaceOnly and hidden cells?

Re: VBA Protect userInterfaceOnly and hidden cells?

Re: VBA Protect userInterfaceOnly and hidden cells?

Re: VBA Protect userInterfaceOnly and hidden cells?

Re: VBA Protect userInterfaceOnly and hidden cells?

Re: VBA Protect userInterfaceOnly and hidden cells?

Re: VBA Protect userInterfaceOnly and hidden cells?

Re: VBA Protect userInterfaceOnly and hidden cells?

Re: VBA Protect userInterfaceOnly and hidden cells?

Re: VBA Protect userInterfaceOnly and hidden cells?

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Protect workbook for UserInterfaceOnly

Protect WS and allow Userinterfaceonly, AllowFiltering, EnableOutlining

Protect UserInterFaceOnly:=True ONLY to VISIBLE worksheets WITHOUT a password

Protect(UserInterfaceOnly)

Sheets.Protect UserInterfaceOnly parm not working for me

.protect userinterfaceonly:=true not working?

Protect UserInterfaceOnly + Control deletion of file in same folder

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions