SuperHide column (not exactly)

  • 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)


    Thank you! :)

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


  • 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?

  • 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

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

  • 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

  • This do what I need.


    Thank you all and apologies for misunderstandings.

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

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

  • 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

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

  • 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


    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 !

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


  • Try this.


    1. To show the data and sheet enter the password & show, i.e. makisshow.
    2. To hide the sheet and data enter password and hide.
  • 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!

  • 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

    X/

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!