Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Locking Excel Sheets

Status
Not open for further replies.

sabloomer

Technical User
Joined
Aug 8, 2003
Messages
153
Location
US
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
 
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")
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
 
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
 
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?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top