MACRO TO ADJUST COLOUMN WIDTH BY VALUE
 

Hi, Please if anybody tell me the macro that if I put any thing in
any cell of any coloumn
of whole sheet then coloumn should get adjusted by that value width.
Thanks

MACRO TO ADJUST COLOUMN WIDTH BY VALUE
 

On 25 Feb, 11:55, K wrote:
Hi, *Please if anybody tell me the macro that if I put any thing in
any cell of any coloumn
of whole sheet then coloumn should get adjusted by that value width.
Thanks

Hi K,

Paste this code into the sheet code module (copy code, right click
sheet name tab, click view code, paste code)

**Beware word wrapping**

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ActiveSheet.Cells) Then
ActiveCell.ColumnWidth = Target.Value
End If
End Sub

Regards

Michael

MACRO TO ADJUST COLOUMN WIDTH BY VALUE
 

On Feb 25, 12:46*pm, "michael.beckinsale"
wrote:
On 25 Feb, 11:55, K wrote:

Hi, *Please if anybody tell me the macro that if I put any thing in
any cell of any coloumn
of whole sheet then coloumn should get adjusted by that value width.
Thanks

Hi K,

Paste this code into the sheet code module (copy code, right click
sheet name tab, click view code, paste code)

**Beware word wrapping**

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, ActiveSheet.Cells) Then
* * ActiveCell.ColumnWidth = Target.Value
End If
End Sub

Regards

Michael

Hi michael, thanks for replying. i used your macro but its giving
"Run time error 13" and highlighting this line
If Intersect(Target, ActiveSheet.Cells) Then
is there any sultion for this please

MACRO TO ADJUST COLOUMN WIDTH BY VALUE
 

Hi K,

Are you sure you have pasted the code into the correct place as per my
email/posting ?

I have tested this and it works perfectly on my set-up.

MACRO TO ADJUST COLOUMN WIDTH BY VALUE
 

On Feb 25, 2:09*pm, "michael.beckinsale"
wrote:
Hi K,

Are you sure you have pasted the code into the correct place as per my
email/posting ?

I have tested this and it works perfectly on my set-up.

yes i am sure i did exactely the way you told me and its giving error
message. does this make difference as i am using excel 2007

MACRO TO ADJUST COLOUMN WIDTH BY VALUE
 

Hi K,

I think l have identified the problem by re-reading your original
post. If my interpretation is correct you want to autofit the column
to an entry in any cell, whether you enter text or numbers. My
oroginal solution was for numbers only. The code posted below will set
the column to the width od the widest entry.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyCol As Integer
MyCol = Intersect(Target, ActiveSheet.Cells).Column
Columns(MyCol).AutoFit

End Sub

This is tried & tested in XL2007

Regards

Michael



I

MACRO TO ADJUST COLOUMN WIDTH BY VALUE
 

On Feb 25, 2:58*pm, "michael.beckinsale"
wrote:
Hi K,

I think l have identified the problem by re-reading your original
post. If my interpretation is correct you want to autofit the column
to an entry in any cell, whether you enter text or numbers. My
oroginal solution was for numbers only. The code posted below will set
the column to the width od the widest entry.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MyCol As Integer
* * * *MyCol = Intersect(Target, ActiveSheet.Cells).Column
* * * *Columns(MyCol).AutoFit

End Sub

This is tried & tested in XL2007

Regards

Michael

I

Thanks Michael. this the one i wanted.