Autofit columns, password protected

[Tim]

Hi there,
I have 12 sheets (Deposits) and 12 sheets (Cheques). I want the column to
autofit based on user text input. So for Deposits ( columns G and H ) ....
for Cheques ( columns I and J ). Not sure how to make them autofit and
password protect. Any ideas out there? Thanks very much.

 

[ryguy7272]

Ok, this will auto-fiit the columns A:I for you:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

Worksheets("Sheet1").Columns("A:I").AutoFit

Application.EnableEvents = True
End Sub

You have to unprotect the sheet to enable this kind of change. If you
auto-unprotect the sheet, there is no point in protecting the sheet. You can
certainly control that with another macro though...from a post back in 2007
by 'JW'

To unprotect the sheet:
Sheets("Sheet2").Unprotect Password:="YourPassword"

To protect the sheet:
Sheets("Sheet2").Protect Password:="YourPassword"

To run a check and proceed accordingly:
Sub thiser()
Dim pw As String
pw = "YourPassword"
With Sheets("Sheet2")
If .ProtectContents = True Then
..Unprotect Password:=pw
Else
..Protect Password:=pw
End If
End With
End Sub

 

[Tim]

Hi there,
So could I insert your code somewhere in between the password protect and
unprotect code and copy in to each sheet?
Could you show me how? I really do need to protect and unprotect these
sheets. Thanks

 

[Gord Dibben]

When you protect the sheet enable formatting of columns.

Also uncheck select locked cells.

Then users can select and enter text only in the two columns that are
unlocked.

I assume G and H are unlocked so's users can input the text.


Gord Dibben MS Excel MVP