Forum Discussion

Sumit_Bhokare's avatar
Sumit_Bhokare
Brass Contributor
Oct 09, 2023

How to Hide and protect few columns of worksheet

Hello All,

Attached is sample worksheet on which currently I'm working.

I need to Hide column F, G and H and protect those only with password so that no one can unhide it without password.

I tried with one code but when I run it entire worksheet getting protected instead of column F, G and H. Can someone help me out with this.

HansVogelaar SergeiBaklan 

 

Regards,

Sumit

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Oct 11, 2023

    Sumit_Bhokare 

    Create a command button in rows 1 / 2 and assign the following macro to it:

    Sub DeleteRows()
    If Intersect(Range("A1:A2").EntireRow, Selection) Is Nothing Then
        ActiveSheet.Unprotect Password:="Hello"
        Selection.EntireRow.Delete
        ActiveSheet.Protect Password:="Hello"
    Else
        MsgBox "You are not allowed to delete rows 1 and/or 2!", vbCritical
    End If
End Sub

    See the attached version.

    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 10, 2023

      Sumit_Bhokare 

      Perhaps like this?

      Select all columns that should be editable.

      Press Ctrl+1.

      Activate the Protection tab.

      Clear the Locked check box.

      Change the macro to

      Sub Macro1()
    Dim Pwd As String
    If ActiveSheet.ProtectContents Then
        Pwd = InputBox("Enter the password")
        If Pwd = "Hello" Then
            ActiveSheet.Unprotect Password:=Pwd
            Range("F1:H1").EntireColumn.Hidden = False
            ActiveSheet.Buttons("Button 1").Caption = "Hide & Lock"
        Else
            MsgBox "Password not correct!", vbCritical
        End If
    Else
        ActiveSheet.Buttons("Button 1").Caption = "Unlock & Unhide"
        Range("F1:H1").EntireColumn.Hidden = True
        ActiveSheet.Protect Password:="Hello", UserInterfaceOnly:=True
    End If
End Sub

      See the attached version.

      • Sumit_Bhokare's avatar
        Sumit_Bhokare
        Brass Contributor
        Oct 11, 2023

        HansVogelaar thank you for replying.

         

        I was able to generate it somehow but now got stuck in another issue.

        As I'm protecting few columns of my data and because of same I think I'm not able to delete rows, can you please help.

         

        I tried to change lock property of rows to false, then Its allowing to edit data from that row and I don't want to allow that as few formulas are available in original file.

         

        Is there any alternate way that I can delete rows even if columns are protected?

        test file is attached herewith.

Resources