Autofit columns, password protected
 

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.

Autofit columns, password protected
 

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Tim" wrote:

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.

Autofit columns, password protected
 

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

"ryguy7272" wrote:

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

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Tim" wrote:

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.

Autofit columns, password protected
 

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

On Mon, 25 Jan 2010 13:37:01 -0800, Tim
wrote:

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.