Automatic Autofit

O

oranskeer

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!
 
J

Jim Rech

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

--
Jim Rech
Excel MVP
|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!
 
O

oranskeer

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

Dave Peterson

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?
Yes, it is. But I made sure that column is unlocked and it still gives me the
same error.

Jim Rech said:
The sheet is not protected is it? (Tools, Protection...)

--
Jim Rech
Excel MVP
|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!
 
O

oranskeer

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 said:
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?
Yes, it is. But I made sure that column is unlocked and it still gives me the
same error.

Jim Rech said:
The sheet is not protected is it? (Tools, Protection...)

--
Jim Rech
Excel MVP
|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!
 
J

Jim Rech

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
|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
| > > > | > > > |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
| >
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top