Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I switch off automatic formatting (e.g. dates)? | Excel Discussion (Misc queries) | |||
Automatic backup copy | Charts and Charting in Excel | |||
Automatic Chart Update? | Charts and Charting in Excel | |||
Is there an automatic way to change numbers such as 12312001 t | Excel Discussion (Misc queries) | |||
Autofit in excel - cell is large than what is needed or wanted & . | Excel Discussion (Misc queries) |