VBA Excel trying to make the column "very hidden"

Question

I want to hid some columns in my worksheets. I want to have them invisible for all external users. Thety cannot be aware, that the hidden column exist at all.

I found something, which can help me made them "Very hidden" likewise we can set for a whole sheets.

Unfortunately my approach doesn't work so far.

I have got a function, which should be right, but to be fair I don't know how to use it properly.

  Sub Overheadhidden()
  Columns("V:W").VeryHideColumn
  End Sub


  Function VeryHideColumn(myColumn As Range)
  'By M--, April 2017

   myColumn.Locked = True
   myColumn.Hidden = xlVeryHidden
   myColumn.FormulaHidden = True

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

  End Function

It comes from this solution:

Protecting and Hiding Columns same as "Very Hidden" Sheets

Is there ANY possible way to make the columns very hidden?

Can you not transfer the columns to a separate worksheet then hide and password protect that worksheet? — Tragamor, Jan 19 '21 at 15:40
No, It would be vital to keep everything in the same worksheet. — Geographos, Jan 19 '21 at 15:47

score 0 · Answer 1 · answered Jan 19 '21 at 11:04

0

.VeryHideColumn() is not a method or a property. You need to call it as a function:

Sub Overheadhidden()

  Call VeryHideColumn(Columns("V:W"))

End Sub

I will also add that I would recommend this being a Sub rather than a Function simply because the general rule of thumb is to use Function when you want to return something. With your code you are simply giving it a task, so below is a bit better:

Sub VeryHideColumn(myColumn As Range)

   myColumn.Locked = True
   myColumn.Hidden = xlVeryHidden
   myColumn.FormulaHidden = True

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

End Sub

answered Jan 19 '21 at 11:04

Dean

2,326
3
13
32

Now I am getting an error: Unable to set locked property for a range of class. – Geographos Jan 19 '21 at 11:22
@MKR I have tested and this is working on my system. – Dean Jan 19 '21 at 11:37
@MKR the error happens when you run the code more than once. You will need to reverse the locked property before running again. – Dean Jan 19 '21 at 11:38
I found a slightly different way for it. Thank you very much for your help. – Geographos Jan 19 '21 at 15:48

VBA Excel trying to make the column "very hidden"

1 Answers1