Locking Excel Sheets

[sabloomer]

I have a workbook that I want to send out to multiple people. The main sheet allows users to enter data in a few cells. This data updates some calculations as well as chats. Here is my problem, I want to lock the sheet down so that the user can not read the formulas or make any cell changes, BUT I want them to be able to change the column width if the new calculations do not fit. I can figure out everything but how do deal with column widths. Can anyone make any suggestions?

Thank you,

sabloomer

 

[DaleWatson123321]

Hi sabloomer,

Here's a routine you can use. It prompts the user for the column width, and proceeds to change the width of the current column or current selection (if more than one column).

It Un-Protects the sheet, and then Protects the sheet after the column width has been changed.

Dim num, pw As String
Sub Column_Width()
pw = "123"
UnProtect
num = InputBox("Specify Column Width for the current selection&quot
If num = "" Then Exit Sub
If num = 0 Then Exit Sub
UnProtect
Selection.ColumnWidth = num
Protect
End Sub
Sub UnProtect()
ActiveSheet.UnProtect pw
End Sub

Sub Protect()
ActiveSheet.Protect pw
End Sub

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[sabloomer]

Thank you for your time. I will give that a try. It looks great, I am sure that it will work. Sorry for the delay, I was out of the office.

sabloomer

 

[jbanker]

Could this code be modified to work with Auto Filter? Here's my problem. I want to protect a document so users can't overwrite certain formulas in specific columns. However, I also want the document to allow for auto filters. Problem is, if I protect auto filter doesn't work. If I auto filter, my columns with formulas are wide open.

Any suggestions?