Hi all,
I have a question.
Is it possible that a cell can auto size when u write details on it . Sothat the details fit in a cell and that the person can read all data?
I tried wrap text doesnot help?
Thanks tuck.
Full Version: Text that fit in a cell
Do you want the cell to resize or do you want the text to wrap? Resizing will make the column wider, while wrapping will make the row taller.
To resize, you could run VBA code like this in the SheetChange event for your workbook:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "Sheet1" Then 'Only adjust if they are on Sheet1.
If Target.Row = 2 And Target.Column = 2 Then 'Only adjust if cell B2 has been changed.
Columns("B:B").EntireColumn.AutoFit
End If
End If
End Sub
Hope this helps!
Dennis
To resize, you could run VBA code like this in the SheetChange event for your workbook:
CODE
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If ActiveSheet.Name = "Sheet1" Then 'Only adjust if they are on Sheet1.
If Target.Row = 2 And Target.Column = 2 Then 'Only adjust if cell B2 has been changed.
Columns("B:B").EntireColumn.AutoFit
End If
End If
End Sub
Hope this helps!
Dennis
Thanks doctor.
I want to auto rezise.
The code you provided.
I Suppose its for the whole sheet?
Not only for B2?
thanks... Thats y iluv Utter......
Tuck
Edited by: Tucka01 on Thu May 17 10:10:00 EDT 2007.
I want to auto rezise.
The code you provided.
I Suppose its for the whole sheet?
Not only for B2?
thanks... Thats y iluv Utter......
Tuck
Edited by: Tucka01 on Thu May 17 10:10:00 EDT 2007.
If you want this sort of thing to happen for ALL cells on a sheet, just remove the inner IF test, and use the target column as the argument for the autosize:
If ActiveSheet.Name = "Sheet1" Then 'Only adjust if they are on Sheet1.
Columns(Target.Column).EntireColumn.AutoFit
End If
Is that what you were asking?
Dennis
CODE
If ActiveSheet.Name = "Sheet1" Then 'Only adjust if they are on Sheet1.
Columns(Target.Column).EntireColumn.AutoFit
End If
Is that what you were asking?
Dennis
Yes Doctor thanks for the help.
And if i want to auto wrap the text in all cells.
Can you helpmewith that?
I mean that the Row auto rezise ?
Thanks in advance....
Tuck
Edited by: Tucka01 on Thu May 17 10:41:14 EDT 2007.
And if i want to auto wrap the text in all cells.
Can you helpmewith that?
I mean that the Row auto rezise ?
Thanks in advance....
Tuck
Edited by: Tucka01 on Thu May 17 10:41:14 EDT 2007.
Tuck,
To do the autowrap, you should really consider keeping the IF test on which cell/columns was just changed. It could get out of hand very quickly. However, it's your call in the end!
If ActiveSheet.Name = "Sheet1" Then 'Only adjust if they are on Sheet1.
If Target.Row = 2 And Target.Column = 2 Then 'Only adjust if cell B2 has been changed.
Target.WrapText = True
End If
End If
The key to figuring these things out is to record a macro, then perform the task, then stop recording, and look at the code generated. That's all I did to figure out the syntax for these examples.
Dennis
To do the autowrap, you should really consider keeping the IF test on which cell/columns was just changed. It could get out of hand very quickly. However, it's your call in the end!
CODE
If ActiveSheet.Name = "Sheet1" Then 'Only adjust if they are on Sheet1.
If Target.Row = 2 And Target.Column = 2 Then 'Only adjust if cell B2 has been changed.
Target.WrapText = True
End If
End If
The key to figuring these things out is to record a macro, then perform the task, then stop recording, and look at the code generated. That's all I did to figure out the syntax for these examples.
Dennis
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.