How to Hide and protect few columns of worksheet

[Sumit_Bhokare]

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

[#2]

@HansVogelaar can you please help me out?

[#3]

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

[#4]

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

[#5 · Accepted solution]

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

[#6]

@HansVogelaar I need one more additional help from you

In below Select Case I want to exit Select if numLastRow is less than 10 but I'm not sure with simple If function can we exit select? I tried but not able to exit. Can you please let me know how I can exit Select with If condition or any other way? I'm getting Run time error 1004, Autofill method of range class failed

Select Case

Case "Pressure with Seal"

txtPrintArea = BlueTemplate.PageSetup.PrintArea

numLastRow = BlueTemplate.Cells(LightBlueTemplate.Rows.Count, 2).End(xlUp).Row

BlueTemplate.Range("UU10:XA10").Select

txtFormulaArea = "$UU$10:$XA$" & (numLastRow + 1)

Selection.AutoFill Destination:=Range(txtFormulaArea), Type:=xlFillDefault

[#7]

@Sumit_Bhokare 

You can do something like this:

    Select Case ...
        Case "Pressure with Seal"
            numLastRow = BlueTemplate.Cells(LightBlueTemplate.Rows.Count, 2).End(xlUp).Row
            If numLastRow >= 10 Then
                txtPrintArea = BlueTemplate.PageSetup.PrintArea
                txtFormulaArea = "$UU$10:$XA$" & (numLastRow + 1)
                BlueTemplate.Range("UU10:XA10").AutoFill Destination:=Range(txtFormulaArea), Type:=xlFillDefault
                ...
            End If
        Case ...