Sheet Protection, Hidden Columns, and Autofit

[Guest]

Hi Folks,

I have a workbook with one sheet for each month. Depending on the month
there are either 4 or 5 weeks so my columns N can be hidden in a 4 week
month. In order to protect formulas and print area, I have protected each
sheet in the workbook and can autofit the contents of all sheets via a macro
but everytime I try to select cols N and hide them it takes the entire
sheet and hides my contents.
Here is the code I'm working with.

Private Sub Workbook_Open()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="###", AllowFormattingCells:=True,
AllowFormattingColumns:=True
ws.Columns("A:V").AutoFit
Range("B1").Select
Next ws

Sheets("January").Select
Columns("N").Select
Selection.EntireColumn.Hidden = True

Range("B1").Select

This latest attempt won't even autofit before it hides my January sheet. I
have tried moving the Range Select after the autofit but before the next col
selection, and have even tried moving the col hide event to the worksheet
itself under the active event. Nothing I try seems to work. Please help!

 

[Guest]

Hi Stephen,

You can't use column labels to refer to columns. Substitute Range where you
use this.

Columns() requires a number. ie: Columns(14:16).

Better yet, assign a local defined name to the ranges and use it.
Example:
Select columns N
In the names box type 'sheetname'!Week5
Press enter

Note the name is wrapped in single quotes. This is required if there's any
spaces or non-alphanumeric characters. The exclamation tells Excel it's a
sheetlevel name.

To use it:
Sheets("January").Range("Week5").Hidden = True

Note that there's no need to select anything because this uses fully
qualified references.

HTH
Regards,
Garry

 

[Guest]

Correction:
Columns() requires a number. ie: Columns(14:16).

should read:
Columns() requires a number. ie: Columns(14).

or
Columns() requires a number. ie: Columns(16).

where the number is the column's index.

This means:
Range("$A:$A") refers to Columns(1)
Range("$N:$N") refers to Columns(14)
...and so on.

 

[Guest]

Perfect. I ended up with the following and it works like a charm!

Thank you for the response.

Private Sub Workbook_Open()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="###", AllowFormattingCells:=True,
AllowFormattingColumns:=True
ws.Columns("A:V").AutoFit
Next ws

Sheets("January").Columns("$N:$P").Hidden = True
Sheets("February").Columns("$N:$P").Hidden = True
Sheets("April").Columns("$N:$P").Hidden = True
Sheets("May").Columns("$N:$P").Hidden = True
Sheets("July").Columns("$N:$P").Hidden = True
Sheets("August").Columns("$N:$P").Hidden = True
Sheets("October").Columns("$N:$P").Hidden = True
Sheets("November").Columns("$N:$P").Hidden = True

For Each ws In ThisWorkbook.Worksheets
ws.Protect Password:="###", AllowFormattingCells:=True
Next ws

End Sub

 

[Guest]

Hi Stephen,

I stand corrected in your use of Columns() with column labels in this
context. -thanks! I've never used it that way.

Here's an example of what I was trying to suggest:

Private Sub Workbook_Open()

Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
With ws
.Protect Password:="###", _
AllowFormattingCells:=True, _
AllowFormattingColumns:=True
'Fit only those that have contents
.Columns.AutoFit
'Hide this range regardless of which columns it occupies
.Range("Week5").EntireColumn.Hidden = True
.Protect Password:="###", AllowFormattingCells:=True
End With
Next ws
End Sub

If your certain that Columns("N") will never change, then you could
substitute the .Range.. line with:
.Columns("N").Hidden = True

Regards,
Garry

 

[Guest]

I see where you were going but I am 100% positive that the columns will never
change. The whole point of locking down the sheets, forcing formatting, and
hiding certain cells is to save my users from themselves in the first place.

Glad I was able to teach the teacher and I would have continues to bang my
head against the wall without your input.

Thank again!