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

 

[tbakbradley]

Then all is good!

As long as your issue is sorted out.

Cheers

I ended up creating a separate and simple macro with your Columns.SpecialCells(xlCellTypeVisible).Columns.AutoFit
provided. I created a button. That way, the user can click on that for the Auto Fit when needed after they enter their data. I was still having issues where some columns would automatically Auto Fit, but others were not. Creating the separate Macro with button was the easiest.