Automatic Autofit
 

I have the following code for each sheet in my workbook to make the cells
grow with the data entered automatically:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target(1, 1), [A:A]) Is Nothing Then Exit Sub
Target(1, 1).EntireColumn.Autofit
End Sub

All of a sudden, it just stopped working and now I get an error whenever I
click on one of the cells:

Run-time error '1004'
AutoFit Method of Range class failed

When I press debug it takes me to the line Target(1, 1).EntireColumn.Autofit

Any suggestions?

Thanks in advance!

The sheet is not protected is it? (Tools, Protection...)

--
Jim Rech
Excel MVP
"oranskeer" wrote in message
...
|I have the following code for each sheet in my workbook to make the cells
| grow with the data entered automatically:
|
| Private Sub Worksheet_SelectionChange(ByVal Target As Range)
| If Intersect(Target(1, 1), [A:A]) Is Nothing Then Exit Sub
| Target(1, 1).EntireColumn.Autofit
| End Sub
|
| All of a sudden, it just stopped working and now I get an error whenever I
| click on one of the cells:
|
| Run-time error '1004'
| AutoFit Method of Range class failed
|
| When I press debug it takes me to the line Target(1,
1).EntireColumn.Autofit
|
| Any suggestions?
|
| Thanks in advance!

Yes, it is. But I made sure that column is unlocked and it still gives me the
same error.

"Jim Rech" wrote:

The sheet is not protected is it? (Tools, Protection...)

--
Jim Rech
Excel MVP
"oranskeer" wrote in message
...
|I have the following code for each sheet in my workbook to make the cells
| grow with the data entered automatically:
|
| Private Sub Worksheet_SelectionChange(ByVal Target As Range)
| If Intersect(Target(1, 1), [A:A]) Is Nothing Then Exit Sub
| Target(1, 1).EntireColumn.Autofit
| End Sub
|
| All of a sudden, it just stopped working and now I get an error whenever I
| click on one of the cells:
|
| Run-time error '1004'
| AutoFit Method of Range class failed
|
| When I press debug it takes me to the line Target(1,
1).EntireColumn.Autofit
|
| Any suggestions?
|
| Thanks in advance!

If you select column A (Unlocked) on a protected worksheet. Then click on
Format|Column, you'll see that all those options are greyed out.

You can't do this on a protected worksheet.

===
Well, in xl2002+, you can protect the worksheet and allow the user to format the
column. If you have xl2002+, did you protect the sheet that way?

oranskeer wrote:

Yes, it is. But I made sure that column is unlocked and it still gives me the
same error.

"Jim Rech" wrote:

The sheet is not protected is it? (Tools, Protection...)

--
Jim Rech
Excel MVP
"oranskeer" wrote in message
...
|I have the following code for each sheet in my workbook to make the cells
| grow with the data entered automatically:
|
| Private Sub Worksheet_SelectionChange(ByVal Target As Range)
| If Intersect(Target(1, 1), [A:A]) Is Nothing Then Exit Sub
| Target(1, 1).EntireColumn.Autofit
| End Sub
|
| All of a sudden, it just stopped working and now I get an error whenever I
| click on one of the cells:
|
| Run-time error '1004'
| AutoFit Method of Range class failed
|
| When I press debug it takes me to the line Target(1,
1).EntireColumn.Autofit
|
| Any suggestions?
|
| Thanks in advance!




--

Dave Peterson

I am running xl2003. How do I protect the sheet but allow the user to format
the column? I know there is the edit ranges option, but I'm having trouble
figuring out how to use it with the protection.

"Dave Peterson" wrote:

If you select column A (Unlocked) on a protected worksheet. Then click on
Format|Column, you'll see that all those options are greyed out.

You can't do this on a protected worksheet.

===
Well, in xl2002+, you can protect the worksheet and allow the user to format the
column. If you have xl2002+, did you protect the sheet that way?

oranskeer wrote:

Yes, it is. But I made sure that column is unlocked and it still gives me the
same error.

"Jim Rech" wrote:

The sheet is not protected is it? (Tools, Protection...)

--
Jim Rech
Excel MVP
"oranskeer" wrote in message
...
|I have the following code for each sheet in my workbook to make the cells
| grow with the data entered automatically:
|
| Private Sub Worksheet_SelectionChange(ByVal Target As Range)
| If Intersect(Target(1, 1), [A:A]) Is Nothing Then Exit Sub
| Target(1, 1).EntireColumn.Autofit
| End Sub
|
| All of a sudden, it just stopped working and now I get an error whenever I
| click on one of the cells:
|
| Run-time error '1004'
| AutoFit Method of Range class failed
|
| When I press debug it takes me to the line Target(1,
1).EntireColumn.Autofit
|
| Any suggestions?
|
| Thanks in advance!




--

Dave Peterson

For Excel 2003, in the Tools, Protection, Protect Sheet dialog, if you pick
"Format Columns" (in addition to the first two choices that are selected by
default) then your macro runs for me on a protected sheet.

--
Jim Rech
Excel MVP
"oranskeer" wrote in message
...
|I am running xl2003. How do I protect the sheet but allow the user to
format
| the column? I know there is the edit ranges option, but I'm having trouble
| figuring out how to use it with the protection.
|
| "Dave Peterson" wrote:
|
| If you select column A (Unlocked) on a protected worksheet. Then click
on
| Format|Column, you'll see that all those options are greyed out.
|
| You can't do this on a protected worksheet.
|
| ===
| Well, in xl2002+, you can protect the worksheet and allow the user to
format the
| column. If you have xl2002+, did you protect the sheet that way?
|
| oranskeer wrote:
|
| Yes, it is. But I made sure that column is unlocked and it still gives
me the
| same error.
|
| "Jim Rech" wrote:
|
| The sheet is not protected is it? (Tools, Protection...)
|
| --
| Jim Rech
| Excel MVP
| "oranskeer" wrote in message
| ...
| |I have the following code for each sheet in my workbook to make the
cells
| | grow with the data entered automatically:
| |
| | Private Sub Worksheet_SelectionChange(ByVal Target As Range)
| | If Intersect(Target(1, 1), [A:A]) Is Nothing Then Exit Sub
| | Target(1, 1).EntireColumn.Autofit
| | End Sub
| |
| | All of a sudden, it just stopped working and now I get an error
whenever I
| | click on one of the cells:
| |
| | Run-time error '1004'
| | AutoFit Method of Range class failed
| |
| | When I press debug it takes me to the line Target(1,
| 1).EntireColumn.Autofit
| |
| | Any suggestions?
| |
| | Thanks in advance!
|
|
|
|
| --
|
| Dave Peterson
|

That works great!!! Thank you so much!!!

"Jim Rech" wrote:

For Excel 2003, in the Tools, Protection, Protect Sheet dialog, if you pick
"Format Columns" (in addition to the first two choices that are selected by
default) then your macro runs for me on a protected sheet.

--
Jim Rech
Excel MVP
"oranskeer" wrote in message
...
|I am running xl2003. How do I protect the sheet but allow the user to
format
| the column? I know there is the edit ranges option, but I'm having trouble
| figuring out how to use it with the protection.
|
| "Dave Peterson" wrote:
|
| If you select column A (Unlocked) on a protected worksheet. Then click
on
| Format|Column, you'll see that all those options are greyed out.
|
| You can't do this on a protected worksheet.
|
| ===
| Well, in xl2002+, you can protect the worksheet and allow the user to
format the
| column. If you have xl2002+, did you protect the sheet that way?
|
| oranskeer wrote:
|
| Yes, it is. But I made sure that column is unlocked and it still gives
me the
| same error.
|
| "Jim Rech" wrote:
|
| The sheet is not protected is it? (Tools, Protection...)
|
| --
| Jim Rech
| Excel MVP
| "oranskeer" wrote in message
| ...
| |I have the following code for each sheet in my workbook to make the
cells
| | grow with the data entered automatically:
| |
| | Private Sub Worksheet_SelectionChange(ByVal Target As Range)
| | If Intersect(Target(1, 1), [A:A]) Is Nothing Then Exit Sub
| | Target(1, 1).EntireColumn.Autofit
| | End Sub
| |
| | All of a sudden, it just stopped working and now I get an error
whenever I
| | click on one of the cells:
| |
| | Run-time error '1004'
| | AutoFit Method of Range class failed
| |
| | When I press debug it takes me to the line Target(1,
| 1).EntireColumn.Autofit
| |
| | Any suggestions?
| |
| | Thanks in advance!
|
|
|
|
| --
|
| Dave Peterson
|