#1   Report Post  
oranskeer
 
Posts: n/a
Default 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!
  #2   Report Post  
Jim Rech
 
Posts: n/a
Default

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!


  #3   Report Post  
oranskeer
 
Posts: n/a
Default

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!



  #4   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #5   Report Post  
oranskeer
 
Posts: n/a
Default

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



  #6   Report Post  
Jim Rech
 
Posts: n/a
Default

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
|


  #7   Report Post  
oranskeer
 
Posts: n/a
Default

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
|



Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I switch off automatic formatting (e.g. dates)? Coen Excel Discussion (Misc queries) 8 July 13th 06 08:08 AM
Automatic backup copy craigq Charts and Charting in Excel 2 April 11th 05 04:57 PM
Automatic Chart Update? Mike Fox Charts and Charting in Excel 7 January 6th 05 02:29 AM
Is there an automatic way to change numbers such as 12312001 t curtev Excel Discussion (Misc queries) 1 December 29th 04 11:32 PM
Autofit in excel - cell is large than what is needed or wanted & . seezzell Excel Discussion (Misc queries) 2 December 3rd 04 07:39 AM


All times are GMT +1. The time now is 08:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"