Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit column Help?
Hi there,
The code below works great ... BUT ... I need it to apply to only columns G and H ...PLUS ... I would like to make this across the 25 worksheets I have. Any ideas? Thanks very much. Private Sub Worksheet_Change(ByVal Target As Range) Me.Application.ActiveWorkbook.RefreshAll Target.EntireColumn.AutoFit On Error Resume Next Target.Dependents.EntireColumn.AutoFit End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit column Help?
Hi Tim,
Try the following Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Dim ws As Worksheet Set isect = Application.Intersect _ (Target, Columns("G:H")) If Not isect Is Nothing Then Me.Application.ActiveWorkbook.RefreshAll For Each ws In Worksheets ws.Columns("G:H").AutoFit Next ws 'Not sure if you still want the following On Error Resume Next Target.Dependents.EntireColumn.AutoFit End If End Sub If you want to exclude any sheets then use the following code in the loop. You can expand the if statement if more than one sheet to excleude. For Each ws In Worksheets If ws.Name < "MyShtToExclude" Then ws.Columns("G:H").AutoFit End If Next ws -- Regards, OssieMac "Tim" wrote: Hi there, The code below works great ... BUT ... I need it to apply to only columns G and H ...PLUS ... I would like to make this across the 25 worksheets I have. Any ideas? Thanks very much. Private Sub Worksheet_Change(ByVal Target As Range) Me.Application.ActiveWorkbook.RefreshAll Target.EntireColumn.AutoFit On Error Resume Next Target.Dependents.EntireColumn.AutoFit End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit column Help?
Hi again Tim,
Not sure now whether you meant that you wanted all worksheets to have the auto column fit if the active one was changed or if you only want the active sheet changed but whatever worksheet is the activesheet. The following just changes the activesheet (whatever one that might be) but you need to copy it into ThisWorkbook module. Private Sub Workbook_SheetChange _ (ByVal Sh As Object, ByVal Target As Range) Dim isect As Range Set isect = Application.Intersect _ (Target, Sh.Columns("G:H")) If Not isect Is Nothing Then ActiveWorkbook.RefreshAll Sh.Columns("G:H").AutoFit 'Not sure if you still want the following On Error Resume Next Target.Dependents.EntireColumn.AutoFit End If End Sub -- Regards, OssieMac "Tim" wrote: Hi there, The code below works great ... BUT ... I need it to apply to only columns G and H ...PLUS ... I would like to make this across the 25 worksheets I have. Any ideas? Thanks very much. Private Sub Worksheet_Change(ByVal Target As Range) Me.Application.ActiveWorkbook.RefreshAll Target.EntireColumn.AutoFit On Error Resume Next Target.Dependents.EntireColumn.AutoFit End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit column Help?
Hi there,
This code works great....I tried your next post, but it kinda of went for a loop that I could not break. I do want this code to apply to all other worksheets, but only when they are active. I will copy this code to all sheets unless, you can think of another way. Thanks for your help. "OssieMac" wrote: Hi Tim, Try the following Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Dim ws As Worksheet Set isect = Application.Intersect _ (Target, Columns("G:H")) If Not isect Is Nothing Then Me.Application.ActiveWorkbook.RefreshAll For Each ws In Worksheets ws.Columns("G:H").AutoFit Next ws 'Not sure if you still want the following On Error Resume Next Target.Dependents.EntireColumn.AutoFit End If End Sub If you want to exclude any sheets then use the following code in the loop. You can expand the if statement if more than one sheet to excleude. For Each ws In Worksheets If ws.Name < "MyShtToExclude" Then ws.Columns("G:H").AutoFit End If Next ws -- Regards, OssieMac "Tim" wrote: Hi there, The code below works great ... BUT ... I need it to apply to only columns G and H ...PLUS ... I would like to make this across the 25 worksheets I have. Any ideas? Thanks very much. Private Sub Worksheet_Change(ByVal Target As Range) Me.Application.ActiveWorkbook.RefreshAll Target.EntireColumn.AutoFit On Error Resume Next Target.Dependents.EntireColumn.AutoFit End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit column Help?
I may have spoke too soon...I copied the code to other worksheets and it does
not work. Not sure what I am doing wrong? Any ideas? "OssieMac" wrote: Hi Tim, Try the following Private Sub Worksheet_Change(ByVal Target As Range) Dim isect As Range Dim ws As Worksheet Set isect = Application.Intersect _ (Target, Columns("G:H")) If Not isect Is Nothing Then Me.Application.ActiveWorkbook.RefreshAll For Each ws In Worksheets ws.Columns("G:H").AutoFit Next ws 'Not sure if you still want the following On Error Resume Next Target.Dependents.EntireColumn.AutoFit End If End Sub If you want to exclude any sheets then use the following code in the loop. You can expand the if statement if more than one sheet to excleude. For Each ws In Worksheets If ws.Name < "MyShtToExclude" Then ws.Columns("G:H").AutoFit End If Next ws -- Regards, OssieMac "Tim" wrote: Hi there, The code below works great ... BUT ... I need it to apply to only columns G and H ...PLUS ... I would like to make this across the 25 worksheets I have. Any ideas? Thanks very much. Private Sub Worksheet_Change(ByVal Target As Range) Me.Application.ActiveWorkbook.RefreshAll Target.EntireColumn.AutoFit On Error Resume Next Target.Dependents.EntireColumn.AutoFit End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Autofit column Help?
Hi Tim,
If you use the code from my second post then you must copy it into ThisWorkbook module; not the Sheet module. Also you must delete my other code from the Sheet modules. In case you don't understand what I mean then when you are in the VBA Editor, on the left hand side there is a column (Window) that is called the Project Explorer with a list of the modules. (If you can't see this Window then Select menu item View - Project Explorer.) In this window you will see a list of the Sheet modules and also one called ThisWorkbook. Double Click ThisWorkbook and copy the code from my second post into it. Double click each of the other sheet modules in turn and delete my other code from them. (You cannot have any subs called Private Sub Worksheet_Change(ByVal Target As Range) in the individual sheet modules.) I have tested the code in my second post and it works on whatever is the active sheet. Hope this helps. -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Column Autofit | Excel Discussion (Misc queries) | |||
Column AutoFit C API | Excel Programming | |||
how to autofit column widths | New Users to Excel | |||
Column Autofit | Excel Programming | |||
Column Autofit Problem | Excel Programming |