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...
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.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        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.

Resources