autofit column, password sheets
 

Hi there,
I have a workbook that is made up of 26 sheets, that are 20 columns wide,
are password protected. I want only 24 of the sheets and only 2 of these
columns in each sheet to automatically fit the text typed in to them, and the
rest of the columns stay the same. Does anyone have any idea on what code to
use? Thanks for your time.

autofit column, password sheets
 

Hi

Change NotThisSheet and NotThisSheet2 to the names of sheets to exclude from
autofit. This macro will autofit column 2 and 5 if they are changed.

As this is an event macro it has to be inserted into the codesheet for
ThisWorkbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name < "NotThisSheet" And Sh.Name < "NotThisSheet2" Then
If Target.Column = 2 Or Target.Column = 5 Then
Target.EntireColumn.AutoFit
End If
End If
End Sub


Regards,
Per

"Tim" skrev i meddelelsen
...
Hi there,
I have a workbook that is made up of 26 sheets, that are 20 columns wide,
are password protected. I want only 24 of the sheets and only 2 of these
columns in each sheet to automatically fit the text typed in to them, and
the
rest of the columns stay the same. Does anyone have any idea on what code
to
use? Thanks for your time.

autofit column, password sheets
 

I forgot the password issue, try this version:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name < "NotThisSheet" And Sh.Name < "NotThisSheet2" Then
If Target.Column = 2 Or Target.Column = 5 Then
Sh.Unprotect Password:="JustMe"
Target.EntireColumn.AutoFit
Sh.Protect Password:="JustMe"
End If
End If
End Sub

Regards,
Per

"Per Jessen" skrev i meddelelsen
...
Hi

Change NotThisSheet and NotThisSheet2 to the names of sheets to exclude
from autofit. This macro will autofit column 2 and 5 if they are changed.

As this is an event macro it has to be inserted into the codesheet for
ThisWorkbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Sh.Name < "NotThisSheet" And Sh.Name < "NotThisSheet2" Then
If Target.Column = 2 Or Target.Column = 5 Then
Target.EntireColumn.AutoFit
End If
End If
End Sub


Regards,
Per

"Tim" skrev i meddelelsen
...
Hi there,
I have a workbook that is made up of 26 sheets, that are 20 columns wide,
are password protected. I want only 24 of the sheets and only 2 of these
columns in each sheet to automatically fit the text typed in to them, and
the
rest of the columns stay the same. Does anyone have any idea on what code
to
use? Thanks for your time.

autofit column, password sheets
 

Hi there,
I just realized that half the sheets are in columns G and H ... and the
other half of the sheets is in I and J. I will have to have 2 types of code
and put in 'each' sheet instead of ThisWorkbook. Do you know how I revise?
Thanks

"Per Jessen" wrote:

I forgot the password issue, try this version:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Sh.Name < "NotThisSheet" And Sh.Name < "NotThisSheet2" Then
If Target.Column = 2 Or Target.Column = 5 Then
Sh.Unprotect Password:="JustMe"
Target.EntireColumn.AutoFit
Sh.Protect Password:="JustMe"
End If
End If
End Sub

Regards,
Per

"Per Jessen" skrev i meddelelsen
...
Hi

Change NotThisSheet and NotThisSheet2 to the names of sheets to exclude
from autofit. This macro will autofit column 2 and 5 if they are changed.

As this is an event macro it has to be inserted into the codesheet for
ThisWorkbook

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Sh.Name < "NotThisSheet" And Sh.Name < "NotThisSheet2" Then
If Target.Column = 2 Or Target.Column = 5 Then
Target.EntireColumn.AutoFit
End If
End If
End Sub


Regards,
Per

"Tim" skrev i meddelelsen
...
Hi there,
I have a workbook that is made up of 26 sheets, that are 20 columns wide,
are password protected. I want only 24 of the sheets and only 2 of these
columns in each sheet to automatically fit the text typed in to them, and
the
rest of the columns stay the same. Does anyone have any idea on what code
to
use? Thanks for your time.


.