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