Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection, Hidden Columns, and Autofit
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:P 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:P 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:P").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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection, Hidden Columns, and Autofit
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:P 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection, Hidden Columns, and Autofit
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection, Hidden Columns, and Autofit
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 "GS" wrote: 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection, Hidden Columns, and Autofit
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:P") will never change, then you could substitute the .Range.. line with: .Columns("N:P").Hidden = True Regards, Garry |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheet Protection, Hidden Columns, and Autofit
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! "GS" wrote: 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:P") will never change, then you could substitute the .Range.. line with: .Columns("N:P").Hidden = True Regards, Garry |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Autofit (Columns.EntireColumn.AutoFit) does not work | Excel Discussion (Misc queries) | |||
Protected document loses all protection(hidden columns) when manually copy-pasted | Excel Discussion (Misc queries) | |||
protected document loses all protection(hidden columns) when manually copy-pasted | Excel Worksheet Functions | |||
How to keep hidden columns hidden using protection | Excel Discussion (Misc queries) | |||
Cell protection with hidden columns | Excel Discussion (Misc queries) |