Wrap Text Problem + a little VB :)

[Fizban]

Hello everyone!

I'm an excel newbie and i need your help with something.
I have a project where i created a workspace with several sheets in it.
I use the 1st sheet as a template to enter all information and the data is
spread to the other sheets using the obvious command: =Template![A-Z][#].

My problem starts with the "Wrap Text" option, for some reason it refuse to
resize the destination cell when new data is entered.

I've read several comments here that said that "Merged" cells have a problem
with "auto fit" and seen some VB code posted as well, but i have no idea how
to use it (again, newbie here.. )

Can someone help me out and instruct me where exactly do i enter the VB code
for it to work?
Will the VB make the "auto fit" run automatically? or do i need to use some
kind of key combinations to activate it?

Thanks in advance for all the helpers!

 

[NoodNutt]

G'day Fizban

Since you are a newbie (We were all there once, I'm still learning
everyday!), I will try and walk you through it.

There is 2 ways you could do this:

A. You can setup a timer event that will trigger the Set_Column_Width
automatically at a given time interval
B. you can create a command button on one of your worksheets so that each
time new data is input you can manually do it.

If you prefer A. then do this.

Hold down the ALT key, then press F11 (this will bring up the VB window)

In the left pane column you will see a list

Step 1.

Where it says "ThisWorkbook", double click it.

In the right window pane you will see a white sheet
At the top you should see 2 words (General) & (Declarations), if not then
select them from the dropdowns.

Paste this code into it.

Private Sub Workbook_Open()

Application.OnTime Now + TimeValue("00:01:00"), "Set_Column_Width"

End Sub

The TimeValue is currently set at 1min, you can change the frequency of time
to whatever you want eg TimeValue("00:05:00") = 5mins etc......


Next: Step 2.

In the left pane column you will see "Microsoft Excel Objects"

Right click on it
Select Insert > Module

Double click on the newly create "Module1" and paste in this code.


Sub Set_Column_Width()

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Sheets(Array("Sheet1", "Sheet2", "Sheet3")).Select ' Change the name of
the sheets to your names and however many you need to add

Columns("A:L").Select 'Insert your columns you want to Select for auto
sizing (if it is just 1 column then like ("A:A") except you put your column
in it)
Selection.Columns.AutoFit

Sheets("Sheet1").Select ' Where you want the focus to be set when the
event is finished
Range("A1").Select

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

You will notice (With.Application), this turns off the screen
blinking/process, then resets to on when complete.

Don't be afraid to experiment, once you get the hang of using VB and using
code you will never look back, if you have any problems or questions you
know where to type.

Good luck

HTH
Mark.