Hide results in Excel

Question

Hide results in Excel

Navigation

#1 by (2 votes)

3

Good afternoon, guys. I have the following problem:

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

When the user opens the worksheet, all rows are locked and with the data hidden, then by the time he releases his range, the data would be shown, but only from his row, the one that he has the password and not the rest.

Does anyone know how to do this? I already tried with the lock and password settings, I already tried with the ";;;" trick, but nothing. Is it possible to do an advanced filter or something like this?

I could not get through functions and I am a layman in VBA.

Thank you.

excel vba

asked by anonymous 24.06.2015 / 19:49

1 answer

How to open a JSP inside a JSP through a Servlet? Context Delimited and Modules / Packages

score 2 · Accepted Answer

@Maximiliano I made a spreadsheet of this type.

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

Thenpastethefollowingcode,thiswillensurethateverytimetheworksheetisopened,norowwillbevisibleandtheentireworksheetwillbeprotected:

OptionExplicitPrivateSubWorkbook_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)

Andpastethefollowingcode(HereIaminsertingprotectionfortwolinesonly):

OptionExplicitSubverify1()Dimsenha1AsSingleOnErrorResumeNextsenha1=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 "Plan1" worksheet, you go to the developer tab in the Insert option. insert two Form Control check buttons) and rename them, one for "User1" and one for "User2".

Afterinsertingthebuttons,right-clickandassignamacrotoeach,MacroVerify1()foruser1andmacroverify2()foruser2.

Nowyougobacktothevbainplan1andclickontheareaofthePlanProtectmacroandclickonF5torunit(YoucanalsodothisdirectlyfromtheworksheetintheDeveloper>Macrooption)

Ready!youwillhavetwooptionsforblockedlineswithapassword.

Ifyouwantmore,youwillhavetofollowthe"cake recipe" that is in the   vba. Making copied pasted to 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 you can get boring or want to insert information with the spreadsheets blocked

Note: Whenever the spreadsheet is opened, do not show a line other than the first one, where you should leave it very wide (Height


30 or more) to insert the buttons. if the user enters the password     wrong message will be shown as "Incorrect password!" and can not     see the cell in question.


VBA   Once you have the worksheet ready, go to the Tools > VBA Project Properties. Enter a password for   protection, since an expert user may want to see the   consequently the passwords