Results 1 to 5 of 5

Hide multiple columns across all worksheets

Threaded View

  1. #1
    Registered User
    Join Date
    05-16-2020
    Location
    Alaska
    MS-Off Ver
    2016
    Posts
    7

    Hide multiple columns across all worksheets

    Thank you in advance for anyone who might have the time and energy to help me.

    I would like to include VBA coding to hide columns "H:H" and "O:P" on all worksheets in this workbook. I would like this to take place upon opening the workbook "Workbook_Open()", and the second is upon protecting all worksheets "Protect()".

    Couple things to note, I am using "UserInterfaceOnly:=True" and "EnableOutlining = True" as I still need the end users to be able to use the groupings (both rows and columns), but there are columns H:H and O:Q that have information I would like to stay hidden.

    I hope someone has time to help out!

    FYI - Password: netpar



    Workbook_Open: UserInterfaceOnly = True; Hide H:H and O:Q; Collapse all grouping to level 1



    Private Sub Workbook_Open()
        
    'Enable Outline, Password Protect
        
        Dim wsh As Worksheet
        For Each wsh In Me.Worksheets
            wsh.EnableOutlining = True
            wsh.Protect UserInterfaceOnly:=True, AllowFiltering:=True, Password:="netpar"
        Next wsh
        
    'Hide Earned Value Columns
    
        Range("H:H,O:Q").EntireColumn.Hidden = True
        
    'Collapse All Grouping
    
        Dim b As Worksheet
        For Each b In Worksheets
            b.Outline.ShowLevels ColumnLevels:=1
            b.Outline.ShowLevels RowLevels:=1
        Next b
        
    End Sub



    Module 1: Protect and hide columns :H: and O:Q



    Sub Protect()
    
    'Protect Worksheets
        Dim ws As Worksheet
        Dim Pwd As String
        Pwd = InputBox("Enter your password to protect all worksheets", "Protect Worksheets")
        For Each ws In ActiveWorkbook.Worksheets
            ws.Protect Password:=Pwd
        Next ws
    
    End Sub




    NOT NEARLY AS IMPORTANT

    There is Module 2 that unprotects all worksheets. I'd love it if it unhide all columns (then grouped to level 1). The issue I have experience with this is the end user can try to unlock without the password, which doesn't unprotect, but does open all hidden columns. I believe this is because of the UserInterface setting that is needed for the grouping.

    Thanks again (file attached this time)
    Attached Files Attached Files
    Last edited by loudengreiner; 05-22-2020 at 06:15 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Hide columns in multiple worksheets - each sheet has its own name
    By Tyso in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 09-29-2013, 01:53 PM
  2. [SOLVED] Hide columns in multiple worksheets
    By Tyso in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-29-2013, 10:47 AM
  3. Macro to hide columns and change formulas in multiple worksheets
    By Catthy in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-02-2012, 10:26 PM
  4. [SOLVED] Hide empty columns that appear the same from multiple worksheets
    By tinhtu in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-22-2012, 01:16 PM
  5. Hide empty columns from multiple worksheets
    By tinhtu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-18-2012, 02:19 PM
  6. Checkbox to hide columns across multiple worksheets
    By BrakeJake in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-27-2012, 09:49 PM
  7. Hide columns in multiple worksheets based on one cell.
    By -Johan- in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-03-2011, 02:36 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts