Why wont 'autofit' and 'wrap text' remain in tact?

[Bermi Girl]

I am trying to reference a cell in another worksheet that is formatted to
'autofit' and 'wrap text', but when the information of the original cell
translates to the destination cell, it loses its 'autofit' and 'wrap text'
format. Someone suggested the following, but I am still not having any luck
unless I am applying the code incorrectly.

Right-click the sheet tab, select View Code, enter this:
Private Sub Worksheet_Change(ByVal Target As Range)
Rows(Target.Row).AutoFit
End Sub

HELP!!!

Bermi Girl

 

[Dave Peterson]

How does the value get "translated" to that other sheet?

If you're using a formula, then if you knew where the cell was in that receiving
worksheet, you may be able to tie it that way.

Or maybe you could tie into that worksheet's calculate event.

Or maybe you could just autofit all the rows each time you activate that
sheet????

 

[Bermi Girl]

Hi Dave,

I am making a little bit of progress. The following code was suggested,
which successfully formats the receiving cell with autofit and wrap text, but
it only takes effect when I type the information in the original cell, close
out Excel and re-open Excel. I need something that takes effect immediately
and does not require me to close out the application to work.

Private Sub Worksheet_Activate()
Rows.AutoFit
End Sub

 

[Dave Peterson]

The worksheet_activate should fire whenever you change to that sheet.

In fact, this event won't fire when you open excel (unless you change to this
sheet in some open procedure).

So either you have a macro that turns off events and forgets to turn it back on
or some other problem.

Try this next time the rows don't autofit when you change to this sheet.

Hit alt-f8 to get to the VBE
hit ctrl-g to see the immediate window
type this and hit enter:
application.enableevents = true

Then back to excel, change to a different sheet, then change back.

In fact, to double check that the procedure is firing, add a (temporary) msgbox:

Private Sub Worksheet_Activate()
msgbox "It's firing!"
me.Rows.AutoFit
End Sub

====
And you did put this into the worksheet module that should be autofit, right????