Hide results in Excel

Question

Good afternoon, guys. I have the following problem:

I have several lines and each one of them is protected with a different password for the edition, each corresponding to a company unit. I need the values to be hidden for those who don’t know the password, for example:

When the user opens the spreadsheet all the lines are blocked and with the hidden data, then, the moment he releases his interval, the data would be shown, but only from his line, the one that he has the password and not the others.

Does anyone know how to do that? I’ve tried it with the lock and password settings, I’ve tried it with the ";;" trick, but nothing. Does it have how to make an advanced filter or something like?

I did not get through functions and I am layman in VBA.

Thank you.

Answer 1

@Maximilian I made a spreadsheet of this kind.

In a new spreadsheet open the vba (Alt + F11) And click "This workbook"

Then paste the following code, this will ensure that every time the worksheet is opened, no line will be visible and the entire worksheet will be protected:

Option Explicit
Private Sub Workbook_Open()
Sheets("Plan1").Select
    ActiveSheet.Unprotect Password:="123"
Range("A1:G1").Select
    Selection.ClearContents
Range("2:2").Select
    Selection.EntireRow.Hidden = True
Range("3:3").Select
    Selection.EntireRow.Hidden = True
ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True

End Sub

Then click on Plan1 (Plan1)

And paste the following code (Here I am inserting protection for two lines only):

Option Explicit
Sub verify1()

Dim senha1 As Single

On Error Resume Next

    senha1 = InputBox(Prompt:="Digite a Senha:")

    If senha1 = "111" Then
    ActiveSheet.Unprotect Password:="123"

    ActiveSheet.Protection.AllowEditRanges.Add Title:="Intervalo2", Range:=Rows _
        ("3:3"), Password:="222"
    Range("2:2").Select
    Selection.EntireRow.Hidden = False

    Range("3:3").Select
    Selection.EntireRow.Hidden = True
    Range("A2").Select
    ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True
    Else
    MsgBox "A Senha Incorreta!"
    Range("A1:G1").Select
    Selection.ClearContents
    End If

End Sub
Sub verify2()

    Dim senha2 As Single

    On Error Resume Next

    senha2 = InputBox(Prompt:="Digite a Senha:")

    If senha2 = "222" Then
    ActiveSheet.Unprotect Password:="123"

    ActiveSheet.Protection.AllowEditRanges.Add Title:="Intervalo1", Range:=Rows _
        ("2:2"), Password:="111"
    Range("3:3").Select
    Selection.EntireRow.Hidden = False
    Range("2:2").Select
    Selection.EntireRow.Hidden = True

    Range("A3").Select
    ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True

    Else
    MsgBox "A Senha Incorreta!"
    Range("A1:G1").Select
    Selection.ClearContents
    End If

End Sub
Sub PlanProtect()
On Error Resume Next
Range("3:3").Select
    ActiveSheet.Unprotect Password:="123"

    Columns("H:H").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.EntireColumn.Hidden = True

    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.EntireRow.Hidden = True

        Range("A1:G1").Select
        With Selection
            .HorizontalAlignment = xlCenter
            .VerticalAlignment = xlBottom
            .WrapText = False
            .Orientation = 0
            .AddIndent = False
            .IndentLevel = 0
            .ShrinkToFit = False
            .ReadingOrder = xlContext
            .MergeCells = False
        End With
        Selection.Merge

    ActiveSheet.Protection.AllowEditRanges.Add Title:="Intervalo1", Range:=Rows _
        ("2:2"), Password:="111"

    ActiveSheet.Protection.AllowEditRanges.Add Title:="Intervalo2", Range:=Rows _
        ("3:3"), Password:="222"



    Sheets("Plan1").Select
    ActiveSheet.Protect Password:="123", DrawingObjects:=True, Contents:=True, Scenarios:=True



End Sub

Now in the spreadsheet "Plan1" You go to the developer tab, in the Insert option. insert Two Form Control selection buttons) and rename them, one for "User1" and one for "User2".

After inserting the buttons, right-click and assign a macro to each one, Macro Verify1() to user1 and macro verify2() to user2.

Now you go back to the vba in plan1 and click on the area of the macro Planprotect and click on F5 to run it (You can also do this directly by the spreadsheet in the option Developer > Macro)

Ready! You will have two options of blocked lines with password.

If you want more, you will have to follow the "cake recipe" on vba. Making copied or pasted for each line you want and each user option. It is also good that you already have all the data in the spreadsheet before because later it may get boring want to insert information with the blocked spreadsheets

Observing: Every time the spreadsheet is opened, do not show any line except the first one, where Voce should leave it wide (Height

30 or more) to enter the buttons. if the user enters the password wrong message will be shown as "Incorrect Password!" and cannot see the cell concerned.

VBA Once you have the spreadsheet ready, go to the vba in Tools > VBA Project Properties. Enter a password protection, because an expert user may want to see the code and consequently the passwords