Change column width automatically based on data imported from another sheet/cell

[shco]

Hi

My cell A1 has the following formula:
"='SheetX'!C17"

So, if someone enters a word in cell C17 of "SheetX", that word will appear in A1.

Is it possible to have excel automatically adjust the width of column A based on the length of the word that is entered? For example, if someone enters "a very very long word" in cell C17, then column A will need to be wide. If someone enters "short" in C17, then Column A can be narrower. Is there a way I can set things so that the column width is adjusted without me having to do anything after the word is entered into C17?

Many thanks
Sam

 

[VoG]

Right click the tab of the sheet containing that formula, sel;ect View Code and paste in

Private Sub Worksheet_Calculate()
Columns("A").AutoFit
End Sub

then press ALT + Q to close the code window.

 

[lenze]

OR, use a Change Event on the second sheet

Private Sub Worksheet_Change(ByVal Target as Range)
If Target.Address = "$C$17" Then
   Sheet1.Columns("A").AutoFit
End If
End Sub

lenze

 

[shco]

Thank you very much!

I noticed that after inserting this script I've lost the ability to "undo" things in my worksheets. Is there something I can do about this?

Thanks again
Sam

 

[VoG]

Sorry, no. Macros effectively delete the undo stack.

 

[shco]

Ok, thanks again for your prompt help - it's very much appreciated...
Sam