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

tbakbradley

Board Regular
Joined
Sep 24, 2010
Messages
126
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
 

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
L

Legacy 167133

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

tbakbradley

Board Regular
Joined
Sep 24, 2010
Messages
126
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.
 

tbakbradley

Board Regular
Joined
Sep 24, 2010
Messages
126

ADVERTISEMENT

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?
 
L

Legacy 167133

Guest
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

Board Regular
Joined
Sep 24, 2010
Messages
126

ADVERTISEMENT

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
 
L

Legacy 167133

Guest
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!
 
L

Legacy 167133

Guest
Then all is good!:)

As long as your issue is sorted out.

Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,122,668
Messages
5,597,463
Members
414,145
Latest member
lonnie451

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top