Forum Discussion

Sumit_Bhokare's avatar
Sumit_Bhokare
Brass Contributor
Aug 24, 2023
Solved

Column hide & to unhide column password is required

Tech Community,

 

I need one help, I have a main template from which I'm going generate one customer copy.

while generating customer copy I need to hide column B:G of each sheet in customer copy, as these columns are hidden in customer copy and if I need to unhide them then it should request a password without which it should not unhide. Can someone help me out in this.

attached sample template as detailed one not able to share.

 

HansVogelaar can you please help me?

Macros and VBA
  • HansVogelaar's avatar
    HansVogelaar
    Aug 24, 2023

    Sumit_Bhokare 

    Sub CreateCopy()
    Dim wsh As Worksheet
    Dim fil
    For Each wsh In Worksheets
        wsh.Range("B1:G1").EntireColumn.Hidden = True
    Next wsh
    fil = Application.GetSaveAsFilename( _
        InitialFileName:="New Workbook", _
        FileFilter:="Excel Macro-enabled Workbook (*.xlsm),*.xlsm", _
        Title:="Create copy of workbook")
    If fil = False Then
        Beep
    Else
        ActiveWorkbook.SaveCopyAs fil
    End If
    For Each wsh In Worksheets
        wsh.Range("B1:G1").EntireColumn.Hidden = False
    Next wsh
End Sub
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Aug 24, 2023

    Sumit_Bhokare 

    As a comment, all information into the hidden columns of the protected sheet could be available to users without password. For example, it's enough in any unprotected cell to enter =B1 and drag to the right and down.

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Aug 24, 2023

    Sumit_Bhokare 

    If you want to require a password to unhide columns, you will have to do the following:

    • Select all cells that the user should be able to edit.
    • Press Ctrl+1 to activate the Format Cells dialog.
    • Activate the Protection tab.
    • Clear the locked check box.
    • Click OK.
    • On the Review tab of the ribbon, click Protect Sheet.
    • Use the check boxes to specify what the user will be allowed to do.
    • Leave the check box 'Select unprotected cells' ticked.
    • Do not tick the check box 'Format Columns'.
    • Specify a password.
    • Click OK.
    • Enter the password again.
    • Click OK.
    • Save the workbook.

    Would that be OK?

    • Sumit_Bhokare's avatar
      Sumit_Bhokare
      Brass Contributor
      Aug 24, 2023

      HansVogelaar  Requirement is that column should get hide while creating a customer copy from main template.
      Main template already have vba code that is developed to meet other functionality requirements hide columns is new additional requirement.

      • HansVogelaar's avatar
        HansVogelaar
        MVP
        Aug 24, 2023

        Sumit_Bhokare 

        Sub CreateCopy()
    Dim wsh As Worksheet
    Dim fil
    For Each wsh In Worksheets
        wsh.Range("B1:G1").EntireColumn.Hidden = True
    Next wsh
    fil = Application.GetSaveAsFilename( _
        InitialFileName:="New Workbook", _
        FileFilter:="Excel Macro-enabled Workbook (*.xlsm),*.xlsm", _
        Title:="Create copy of workbook")
    If fil = False Then
        Beep
    Else
        ActiveWorkbook.SaveCopyAs fil
    End If
    For Each wsh In Worksheets
        wsh.Range("B1:G1").EntireColumn.Hidden = False
    Next wsh
End Sub

Resources