Posts by pantakos

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

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)

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

Thank you!

Quote from pantakos

Working with this solution and trying I find out that it locks the worksheet, so no other code will work. How can I make it not to lock the workgroup? but just hide column E?

Thank you

My mistake. Everything is ok!

Quote from royUK

I would use the before close event that way if a user opens the workbook without enabling macros the columns will still be hidden.

Auto_Open has been replaced by the WorkBook_Open event. Read this to see where to paste the code.

Where to put the code

Code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim oWs As Worksheet
    Const sPw As String = "makis"
    
    For Each oWs In ThisWorkbook.Worksheets
        oWs.Unprotect sPw
        oWs.Columns(5).Hidden = True
        oWs.Protect sPw
    Next oWs
End Sub

Display More

Working with this solution and trying I find out that it locks the worksheet, so no other code will work. How can I make it not to lock the workgroup? but just hide column E?

Thank you

royUK if I may ask. How can I make the width of the column lets say 0.1 cm ? Is that possible oŕ its too small. Where into the code should I define width?

Thank you

Hello royUK , yes this is a better solution. Thank you !

Yeap the code is working fine! Thank you royUK for your help!

In order users not try to open column E width and have a look at the data / prices, thats why I need them hidden.

The code to be hidden at the start will be something like that?

Private Sub Auto_Open()
   Range("E:E").EntireColumn.Hidden = True
End Sub

If I may ask, is it better to use Module or place the code at the sheet it self?

Thank you royUK !

Ok, So in order to make them onopen hidden what should I enter in the code? To start always hidden on open ? And somerhing else, with this way it hides the column E , this is what I needed, is there a way to change the width of the colum let say 0.1 ?

Thank you

Hello,

I tried the code and is working perfect. I was wondering. As I understand now, column has to be hidden in order to make it unhide. Do I have to hide all columns first? And then run the code?

Thank you royUK

Hello royUK and thank you for your answer. I will try it and I will let you know!

Thank you!

Hello,

I want to make the below code to work globaly for all sheets .

I can manage to do it for just the activesheet but not globaly. When I press the macro button asks for password if correct unhide at all sheets column E if not stay hidden or hide it.

Can be also made super hidden? Cannot someone play with width etc

Sub pasw_inputbox()
    Dim mypassword
    Dim xAddress As String
        xAddress = "E:E"
        Application.ActiveSheet.Columns(xAddress).Hidden = True
tryagain:
        mypassword = InputBox("Enter Password", "Password")
    If mypassword = "makis" Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    MsgBox ("Correct")
    Else
If MsgBox("Password incorrect, try again?", vbYesNo, "Wrong password") = vbYes Then
    GoTo tryagain
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    End If
End If
End Sub

Thank you!

Thread answered a lot of times! Thank you rollis13

Quote from rollis13

Sorry, but this time it's better to start a new thread, the title of this one has nothing to do with this last request.

You are absolutely right. As I told you in my previous post, I dont want to go against forum rules! I will open a new one, about this matter. Again, thank you very very much for all the effort and your help! Thank you !!! ?

Can I ask and some other thing? I want to make the below code to work globaly for all sheets (its from the same workgroup), I can manage to do it for just the activesheet but not globaly. (I tried but...)

Sub pasw_inputbox()
    Dim mypassword
    Dim xAddress As String
    xAddress = "E:E"
    Application.ActiveSheet.Columns(xAddress).Hidden = True
tryagain:
    mypassword = InputBox("Enter Password", "Password")
    If mypassword = "makis" Then
    Application.ActiveSheet.Columns(xAddress).Hidden = False
    MsgBox ("Correct")
    Else
If MsgBox("Password incorrect, try again?", vbYesNo, "Wrong password") = vbYes Then
    GoTo tryagain
    Application.ActiveSheet.Columns(xAddress).Hidden = True
    End If
End If
End Sub

What should I change? Thank you

Quote from rollis13

If you are using ws Array you will only pick from here, elsewise you could change your macro and add a If/Then, something like this:

Code

If Sheets.Name <> "PROFORMA DRYHIRE" Then
'do something
End If

Yes I can try that and let you know !

Quote from rollis13

Sorry, found a typo, missed a reference to sheet, change this:

Code

Range(.Cells(cell.Row, "C"), .Cells(cell.Row, "E")).Copy

to (note the leading dot):

Code

 .Range(.Cells(cell.Row, "C"), .Cells(cell.Row, "E")).Copy

Yeap! Working like a charm ! Thank you!

thank you for your answer! I will try it at night, cos I am at work right now! Thanks a lot. I was wondering, because at the excel there will be a lot of sheets, for the last one code, can it choose all the sheets and leave one out? ie choose all the sheets, except PROFORMA DRYHIRE the one that collects all the data. I have find something at the net but nope, doesnt work well. Thank you!

Yes! This is working Fine ! Thank you, Thank you, Thank you !!!

If I can ask, at the same workgroup , at sheet PROFORMA DRYHIRE , the macro is working fine, but when I press the button and the macro ends , do the job, it redirect to sheet 3.... Why is it doing that? I dont have any redirect to do that, maybe goes to last sheet before PROFORMA DRYHIRE.

If it is not possible to answer that ( due to forum rules) I understand and I can open a new thread.

Yes, You are absolutely correct (as anytime...) Everything is working fine, all templates are made correctly. But (there is always a but), now it creates templates for all the sheets not only the active sheet. You see every sheet will have its own button to create the template. How can this will be done? I know that you must be tired, correcting things, but this will be the last one (I hope...)