SuperHide column (not exactly)

pantakos · Feb 28th 2022

Hello,

I am trying to "totally hide" a column from a worksheet. I have found this code at the internet and tried to make it work, (sorry I cant remember or find out where...) but it is not working

Any ideas? (what it does it just lock editing etc)

Code

Function VeryHideColumn(myColumn As Range)

    myColumn.Locked = True
    myColumn.Hidden = xlVeryHidden
    myColumn.FormulaHidden = True
    
    Dim SPsheet As Worksheet
    Dim wb1 As Workbook: Set wb1 = ThisWorkbook
    Set SPsheet = wb1.Sheets("TESTSHEET")
    
    SPsheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
        False, AllowFormattingCells:=True, AllowFormattingRows:=True, _
        AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True

End Function

Public Sub HideColumn()
    
    VeryHideColumn ("A:A")

End Sub

Display More

Thank you!

Logit · Mar 1st 2022

Here are two macros ... one to hide and one to unhide. Change the column indicated in the macro code ...

Code

Option Explicit

Sub HideCols()
    Dim ws As Integer
    Dim sCol As String
    On Error Resume Next
    sCol = "P:U"
   
    For ws = 1 To ThisWorkbook.Worksheets.Count
        ThisWorkbook.Sheets(ws).Columns(sCol).Hidden = True
    Next ws
    On Error GoTo 0
End Sub

Sub unHideCols()
    Dim ws As Integer
    Dim sCol As String
    On Error Resume Next
    sCol = "P:U"
   
    For ws = 1 To ThisWorkbook.Worksheets.Count
        ThisWorkbook.Sheets(ws).Columns(sCol).Hidden = False
    Next ws
    On Error GoTo 0
End Sub

Display More

**royUK** · Mar 1st 2022

I told you in your last post that you cannot "superhide" a column and provided code to hide a column and protect the sheet which is all you can do, so why waste members' time?

pantakos · Mar 1st 2022

Quote from royUK

I told you in your last post that you cannot "superhide" a column and provided code to hide a column and protect the sheet which is all you can do, so why waste members' time?

royUK Yes I know that, and you told me so. I only wanted (and maybe I didnt use the correct meaning and title) to understand why the code I posted not working. It gives me an error. I apologies to all members , I really dont want to waste time! I only want to lock the column from editing. This thread of course can be locked if it is against forum rules. Sorry

**royUK** · Mar 1st 2022

Code

myColumn.Hidden = xlVeryHidden

This is why the code will not work, as I said you can only use very hidden on sheets, not parts of sheets.

pantakos · Mar 1st 2022

Ok, you are right. Sorry for my ignorance, but I just want to hide the column by decrease width and not let anyone can change the width in order to see the data. I dont want to be super hidden (my mistake, wrong title etc). I have this code

Code

SPsheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
 False, AllowFormattingCells:=True, AllowFormattingRows:=True, _
 AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:=True _
 , AllowUsingPivotTables:=True

that I think that this will do the trick and can put it in Case so asks for password in order to let someone play with width or not.

The problem is that it doesnt let me use a column (ie D) or even range.

This is what I am asking for. Help about that issue (and NOT superhidden as I undetstand this cant be done).

I hope you understand (my English are not that good)

Thank you

pantakos · Mar 1st 2022

This do what I need.

Code

Public Sub LockProforma()

Dim SPsheet As Worksheet
    Dim wb1 As Workbook: Set wb1 = ThisWorkbook
    Set SPsheet = wb1.Sheets("TEST1")
    sinput = InputBox("Enter your password")

Select Case sinput
    Case "LOCK"
        SPsheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:= _
        True, AllowFormattingCells:=True, AllowFormattingRows:=True, _
        AllowInsertingHyperlinks:=True, AllowSorting:=True, AllowFiltering:=True _
    , AllowUsingPivotTables:=True
    
    Case "FREE"
       
        SPsheet.Protect DrawingObjects:=False, Contents:=False, Scenarios:= _
        False, AllowFormattingCells:=False, AllowFormattingRows:=False, _
        AllowInsertingHyperlinks:=False, AllowSorting:=False, AllowFiltering:=False _
    , AllowUsingPivotTables:=False
        
    Case Else
        dummy = MsgBox("Incorrect Password", vbExclamation)
    End Select
End Sub

Display More

Thank you all and apologies for misunderstandings.

**royUK** · Mar 2nd 2022

I don't understand why you think changing the width is better than making a Column hidden. That is actually what making the column hidden does.

pantakos · Mar 2nd 2022

The idea is to only (try) to lock certain Column (by not changing width). I can make it hidden, but I was having problem make the sheet protected only by changing width (I need to can edit it). Basically I dont user can change width, so thats why I needed to lock width. I maybe hidden but a user can change width and reveal hidden data.

**royUK** · Mar 2nd 2022

If you protect the sheet then the user cannot change the width

pantakos · Mar 2nd 2022

Yes, you are absolutely correct. You see, there are a lot if macro running that collect data from various colums, sheets etc. So if I protect the whole sheet then the macro is not working and got errors, access denied. Thats my problem

**royUK** · Mar 3rd 2022

Then I would say that the code needs checking. If it is selecting ranges then that would cause errors, not the hidden columns. Post an example of your code.

pantakos · Mar 3rd 2022

Thank you royUK for all the effort and Help.

I managed to lock or unlock , hide or not sheets or columns.

I am trying to make a locked sheet, to just leave edit cells open in order to can make some changes.

I am "playing" with

Code

Sheets("Sheet1").Protect Password:="myPassword", _
    DrawingObjects:=False, _
    Contents:=True, _
    Scenarios:=False, _
    AllowFormattingCells:=True, _
    AllowFormattingColumns:=True, _
    AllowFormattingRows:=True, _
    AllowInsertingColumns:=True, _
    AllowInsertingRows:=False, _
    AllowInsertingHyperlinks:=False, _
    AllowDeletingColumns:=True, _
    AllowDeletingRows:=True, _
    AllowSorting:=False, _
    AllowFiltering:=False, _
    AllowUsingPivotTables:=False

Display More

But cannot find the combination in order to allow edit cells but NOT be able to change column width. Is that possible? I undestand that the sheet must be already protected (right?) and then try the above script. I will upload the whole excel file in order to understand (if you have the time) my thoughts.

Thank you !

pantakos · Mar 3rd 2022

royUK here is the example sheet

Thank you

**royUK** · Mar 3rd 2022

When the sheet is protected no one can unhide the column unless the know the code.

Your InputBox should be used to ask for the password, hide or close. Without the password anyone can remove protection and access the hidden column. I'm sure that that's how I wrote the code for you.

This is the code with some changes, but it is not really doing what I think you expect.

Code

Public Sub HideData()
    
    Dim oWs As Worksheet
    Dim OwsPH As Worksheet
    Const sPw As String = "makis"
    Const xAddress As Integer = 5
    Const yAddress As Integer = 3
    Dim MyPassword As String
    Dim sinput As Variant
    '    Dim dummy As Variant
    'Dim ws As Worksheet
    Dim wb As Workbook: Set wb = ThisWorkbook
    Set OwsPH = wb.Sheets("PROFORMA DRYHIRE")
    
    ''/// The inputbox is not asking for the password, but asking to use Hide or Show
    sinput = InputBox("Enter your password hide or show")
    Select Case sinput
            
        Case "hide"
            
            For Each oWs In ThisWorkbook.Worksheets
                With oWs
                    .Unprotect sPw
                    .Columns(xAddress).Hidden = True
                    .Visible = xlVeryHidden
                    .Activate
                    .Protect sPw
                End With
            Next oWs
            'OwsPH.Columns(yAddress).Hidden = Not OwsPH.Columns(yAddress).Hidden
            
        Case "show"
            
            For Each oWs In ThisWorkbook.Worksheets
                With oWs
                    .Unprotect sPw
                    .Columns(xAddress).Hidden = False
                    OwsPH.Visible = True
                    OwsPH.Activate
                    oWs.Protect sPw
                End With
            Next oWs
            
        Case Else
            MsgBox "Incorrect Password", vbExclamation
            ''/// you do not need the variant
            '                dummy = MsgBox("Incorrect Password", vbExclamation)
    End Select
End Sub

Display More

**royUK** · Mar 3rd 2022

Try this.

To show the data and sheet enter the password & show, i.e. makisshow.
To hide the sheet and data enter password and hide.

pantakos · Mar 3rd 2022

As I understand (I havent tested the code as I dont have my pc here) I cant hide and at the same time leave the cells unprotected for editing. You se the scenario is that the workgroup maybe protected but it is needed to enter data in order the macros to work and create the invoice. My manager needs to sent excel to technicians to full BOOK cell but they cant see the DAILY RATE cell. Thats why I am trying to figure out a way to fo that. Thats the idea. I hope you understand. Thank you for your effort!

pantakos · Mar 3rd 2022

royUK a put this code and I think it is working. I placed it in Hide

Code

    For Each oWs In ThisWorkbook.Worksheets
        oWs.Unprotect sPw
            'Cells.Locked = True
            oWs.Columns(xAddress).Locked = False
            oWs.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
        oWs.Protect sPw
         Next oWs

**royUK** · Mar 5th 2022

The problem with your code is it is not asking for a password to unprotect the sheet because the password is in the code. This means that anyone who runs rhe code will be able to unhide the column.

pantakos · Mar 5th 2022

Yes correct. But I am password protect the code (of course every password can break...)

SuperHide column (not exactly)

Participate now!

Share