Allow Column Width Resize, but not allow Hiding/Unhiding of Columns

[tbakbradley]

I have a spreadsheet with several macros. One of those Macro buttons will Hide or Unhide specific columns based on a selection by the User.

Because my Worksheet is Protected and I have locked Cells, the "Formatting Columns" option must be selected to allow the proper columns to be Hid or Unhid. This is unwanted because the user cannot have access to "Unhide" or "Hide" columns at their will.

Therefore, the first step of the macro is to unprotect the Worksheet, Hide/Unhide proper columns based on the User's selection, then it Protects the worksheet with the "Formatting Columns" being False.

I thought that solved my delima. Now the Macro works, and the User cannot hide/unhide Columns as their selection and the macro dictates that. Now I have a new problem. Because the Worksheet is Protected with the "Formatting Columns" False, the user cannot change the Width of the Column as their need may reside. Some data in a cell may be three characters, where another user could enter data into the same cell and it contain 45 characters.

Can I Protect the Worksheet so that the user cannot Hide/Unhide Columns, but still have the ability to change the width of a cell? If not, is there another option? Is it best for me to Manually widen each of the columns to the "Maximum I think someone may use"?

Thanks for the assistance

 

[Legacy 167133]

Include "Formatting Cells" = True, while protecting the sheet.

 

[tbakbradley]

Include "Formatting Cells" = True, while protecting the sheet.

Formatting the Cells will not allow the user to "Widen" the column to fit whatever they need. Am I missing something on this? It's Monday, so probably so.

 

[Legacy 167133]

Then in that case, include in your code:

Columns.AutoFit

 

[tbakbradley]

Then in that case, include in your code:

Thank you so much for the response. Unfortunately, I have tried the Columns.AutoFit, but when the User selects the option they need, for the macro to Hide/Unhide proper Columns, the Columns.AutoFit is opening ALL Columns...regardless of the selection. Columns.AutoFit makes all Columns visible. Is this how it's supposed to work?

 

[Legacy 167133]

It is hard to tell without seeing your Code, but you could try:

Columns.AutoFit
Columns("G").Hidden = True ' Change to suit
Columns("J").Hidden = True ' Change to suit

 

[tbakbradley]

It is hard to tell without seeing your Code, but you could try:

Here is what I'm trying to do. When a user selects "Estimate for Inquiry" and clicks the macro button, it will Hide/Unhide only the columns relative to that selection. I have the "AllowFormattingColumns"=False as I do NOT want the user to Hide/Unhide columns at will. With doing that, it locks the user from Expanding/Widening the column if the data they are entering is too long for the Cell.

I've added the Columns.Autofit below, but I've never used it before, so not sure how to use it. Do I need to sepecify the Columns that are to be AutoFit?

If Sheets("TLS Inquiry").Range("Investments") = "Estimate for Inquiry" Then
ActiveSheet.Unprotect Password:="Password"
ActiveSheet.Columns.AutoFit
ActiveSheet.Range("Sales_Section", "DNIC_Section").Columns.Hidden = False
ActiveSheet.Range("Loop_Common", "Loop_Common_Dist_dB").Columns.Hidden = False
ActiveSheet.Range("IOF_Common", "IOF_Common_Dist_dB").Columns.Hidden = False
ActiveSheet.Range("ONSE_Section").Columns.Hidden = False
ActiveSheet.Range("Loop_Estimate_Section", "Loop_NID").Columns.Hidden = False
ActiveSheet.Range("IOF_Estimate_Section", "Threshold_Section_Estimate").Columns.Hidden = False
ActiveSheet.Range("Loop_Detail", "Loop_Equipment_Section").Columns.Hidden = True
ActiveSheet.Range("IOF_Detail", "Threshold_Section_Detail").Columns.Hidden = True
ActiveSheet.Range("Loop_NID").Columns.Hidden = False
ActiveSheet.Shapes("SummaryButton").Visible = False
ActiveSheet.Protect Password:="Password", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=False, AllowFormattingColumns:=False, AllowFormattingRows:=True, AllowDeletingRows:=True, AllowDeletingColumns:=False
End If

 

[Legacy 167133]

Hello tbakbradley,

Thank you, now I understand better.

Please try your Code like this;

If Sheets("TLS Inquiry").Range("Investments") = "Estimate for Inquiry" Then
ActiveSheet.Unprotect Password:="Password"
ActiveSheet.Range("Sales_Section", "DNIC_Section").Columns.Hidden = False
ActiveSheet.Range("Loop_Common", "Loop_Common_Dist_dB").Columns.Hidden = False
ActiveSheet.Range("IOF_Common", "IOF_Common_Dist_dB").Columns.Hidden = False
ActiveSheet.Range("ONSE_Section").Columns.Hidden = False
ActiveSheet.Range("Loop_Estimate_Section", "Loop_NID").Columns.Hidden = False
ActiveSheet.Range("IOF_Estimate_Section", "Threshold_Section_Estimate").Columns.Hidden = False
ActiveSheet.Range("Loop_Detail", "Loop_Equipment_Section").Columns.Hidden = True
ActiveSheet.Range("IOF_Detail", "Threshold_Section_Detail").Columns.Hidden = True
ActiveSheet.Range("Loop_NID").Columns.Hidden = False
ActiveSheet.Shapes("SummaryButton").Visible = False</SPAN></SPAN>
Columns.SpecialCells(xlCellTypeVisible).Columns.AutoFit</SPAN></SPAN>
ActiveSheet.Protect Password:="Password", DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=False, AllowFormattingColumns:=False, AllowFormattingRows:=True, AllowDeletingRows:=True, AllowDeletingColumns:=False
End If</SPAN></SPAN>

Hope that helps!

 

[tbakbradley]

Thank you so much. This works!!!

 

[Legacy 167133]

Then all is good!

As long as your issue is sorted out.

Cheers