Auto Column width

royhern

Board Regular
Joined
Feb 21, 2002
Messages
158
Hey team, is there a way to adjust the width of select columns using a macro/formula. I have a sheet with several columns that require filling but as the imput is being keyed i would like the column to get wider. The solution must also work when the sheet is protected.

Any ideas !!

Thanks, Roy
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
you ould try this code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target.Cells(1, 1), Sheet1.Range("A:A")) Is Nothing Then
Columns(Target.Cells(1, 1).Column).Select
Sheet1.unprotect
Selection.EntireColumn.AutoFit
Sheet1.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub

This checks the changes in col A in sheet1 and auto sizes the column width. Just change the range("A:A") depending on what range you want to use.

Hope this helps.
This message was edited by bolo on 2002-10-06 09:04
 
Upvote 0
Bolo, thanks it works great !!

Have a question for you.

When this macro activates it highlights the whole column from top to bottom and takes the cursor to the top. After i key the info in a row how get i get it to go to the row below or to the side columns without actually going from all the way from the top to the desired row.

Thanks, Roy
 
Upvote 0
howdy glad its all working.

if you put this line in before the last 'end if' you should get the next available row

Range("a1").Offset((Range("A:A").End(xlDown).Row), 0).Select
to get to the next column try
use
Range("a1").Offset((Range("A:A").End(xlDown).Row), 1).Select

Laters. This may not be the most efficient code but it works for me!
 
Upvote 0
Bolo it works great the only thing is that when data is keyed lets say in column B and after i press enter it goes back to column A and the available empty row. I would like to key the data and have the cursor go the next row in the same column. Can you let me know how to accomplish this...thanks, Roy

This is the code:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target.Cells, Sheet1.Range("A:A,B:B")) Is Nothing Then
Columns(Target.Cells.Column).Select
Selection.EntireColumn.AutoFit
Range("a1,b1").Offset((Range("A:A,B:B").End(xlDown).Row), 0).Select
End If
End Sub
 
Upvote 0
Hi there try this instead:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target.Cells, Sheet1.Range("A:B")) Is Nothing Then
Application.ScreenUpdating = False
Columns(Target.Cells.Column).Select
Selection.EntireColumn.AutoFit
Target.Cells.Offset(1, 0).Select
Application.ScreenUpdating = True
End If
End Sub

This should do what you want. The application screen updating thing just stops the screen from flashing when the column autowidth takes place.

Regards

HTH
 
Upvote 0
Bolo hey..thanks for the help it works great. Have a ? for you. When i copy this macro into the sheet and i changed the sheet name and columns that i need to be auto fit i get a debug error " Run-time 424, object required"

The sheet name is Data and the columns that need to be auto fit is AX to DD

Can you check this for me if this is correct?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target.Cells, Data.Range("AX:DD")) Is Nothing Then
Application.ScreenUpdating = False
Columns(Target.Cells.Column).Select
Me.Unprotect "payless"
Selection.EntireColumn.AutoFit
Me.Protect "payless"
Target.Cells.Offset(1, 0).Select
Application.ScreenUpdating = True
End If
End Sub
 
Upvote 0
what is payless? Why is that sheet unprotected and not data. the worksheet Data needs to be unprotected for the autofit to take place. thats probably causing the run time error.
 
Upvote 0
Bolo, did what you recommended by having the Data sheet unprotected and still i got the error: RUN-TIME ERROR 424- OBJECT REQUIRED

I would like to be able to have the Data sheet protected if at all possible after the autofit.


When i debug the line below is highlighted:

If Not Intersect(Target.Cells, Data.Range("A:B")) Is Nothing Then


This is the code after i modified it:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target.Cells, Data.Range("AX:DD")) Is Nothing Then
Application.ScreenUpdating = False
Columns(Target.Cells.Column).Select
Selection.EntireColumn.AutoFit
Target.Cells.Offset(1, 0).Select
Application.ScreenUpdating = True
End If
End Sub

Thanks...Roy
 
Upvote 0
Try this

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target.Cells(1, 1), Worksheets("Data").Range("AX:DD")) Is Nothing Then
Application.ScreenUpdating = False
Worksheets("Data").Unprotect
Columns(Target.Cells.Column).Select
Selection.EntireColumn.AutoFit
Target.Cells.Offset(1, 0).Select
Worksheets("Data").Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End If
End Sub

A worksheet has two names in Excel. Theres the name that you see on the workbook and a name in VBA. the method that was previously used depended on the VBA name. As only the workbook name was data you got an error, because the VBA was still sheet1 or whatever. The above changes should sort things out.

Regards

.
 
Upvote 0

Forum statistics

Threads
1,226,287
Messages
6,190,071
Members
453,593
Latest member
Mubashar Ali

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top