Forum Discussion

Sumit_Bhokare's avatar
Sumit_Bhokare
Brass Contributor
Oct 09, 2023
Solved

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.

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.

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

    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
    • HansVogelaar's avatar
      HansVogelaar
      MVP
      Oct 16, 2023

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

       

Resources