Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?


thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Are you saying the rowheight resized, but not enough?

If there is lots of text in that cell, then try adding alt-enter's every 80-100
characters in the values to be brought back. This may help.



Zak wrote:

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi,

Thats right - it resized but not enough.

Sorry but im not too sure i know what u mean by "then try adding alt-enter's
every 80-100 characters in the values to be brought back"... does this mean u
require me to shorten my text manually so that the cell is resized
accordingly? i am unable to do this as i require all info that is there.

thanks..

"Dave Peterson" wrote:

Are you saying the rowheight resized, but not enough?

If there is lots of text in that cell, then try adding alt-enter's every 80-100
characters in the values to be brought back. This may help.



Zak wrote:

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

When you have long text in a cell (just typing), excel can have trouble
displaying all the text. This usually happens if the length of the string is
over 1000 (or so) characters. The text wraps nicely until it just seems to
fizzle out on the last line displayed.

You can add alt-enters (to force new lines within the cell) every 80-100
characters to see lots more characters.

I'm suggesting that you go back to the table and add those alt-enters to one of
those long strings. Then go back to the worksheet with the formula and change
the data to bring back the value from that test cell.

If it works, then you'll have to modify all those cells with lots and lots of
characters.

Be aware that lots of people have complained that the last line in a cell with a
large amount of text can seem to be chopped. One fix is to add an extra
alt-enter at the end of the cell.

Zak wrote:

Hi,

Thats right - it resized but not enough.

Sorry but im not too sure i know what u mean by "then try adding alt-enter's
every 80-100 characters in the values to be brought back"... does this mean u
require me to shorten my text manually so that the cell is resized
accordingly? i am unable to do this as i require all info that is there.

thanks..

"Dave Peterson" wrote:

Are you saying the rowheight resized, but not enough?

If there is lots of text in that cell, then try adding alt-enter's every 80-100
characters in the values to be brought back. This may help.



Zak wrote:

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi Dave,

I have tried your suggestion but have no luck. I changed a item of data and
put in a few alt-enters but the cell just doesnt budge.. not even one bit! it
still shows me how many words it did before i put in the alt-enters.

are there any other suggestions?

really appreciate your help.

thanks.

"Dave Peterson" wrote:

When you have long text in a cell (just typing), excel can have trouble
displaying all the text. This usually happens if the length of the string is
over 1000 (or so) characters. The text wraps nicely until it just seems to
fizzle out on the last line displayed.

You can add alt-enters (to force new lines within the cell) every 80-100
characters to see lots more characters.

I'm suggesting that you go back to the table and add those alt-enters to one of
those long strings. Then go back to the worksheet with the formula and change
the data to bring back the value from that test cell.

If it works, then you'll have to modify all those cells with lots and lots of
characters.

Be aware that lots of people have complained that the last line in a cell with a
large amount of text can seem to be chopped. One fix is to add an extra
alt-enter at the end of the cell.

Zak wrote:

Hi,

Thats right - it resized but not enough.

Sorry but im not too sure i know what u mean by "then try adding alt-enter's
every 80-100 characters in the values to be brought back"... does this mean u
require me to shorten my text manually so that the cell is resized
accordingly? i am unable to do this as i require all info that is there.

thanks..

"Dave Peterson" wrote:

Are you saying the rowheight resized, but not enough?

If there is lots of text in that cell, then try adding alt-enter's every 80-100
characters in the values to be brought back. This may help.



Zak wrote:

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

How many is a few? Did you do it every 80-100 characters until the end of the
string?

If you did, then I don't have any other suggestions.



Zak wrote:

Hi Dave,

I have tried your suggestion but have no luck. I changed a item of data and
put in a few alt-enters but the cell just doesnt budge.. not even one bit! it
still shows me how many words it did before i put in the alt-enters.

are there any other suggestions?

really appreciate your help.

thanks.

"Dave Peterson" wrote:

When you have long text in a cell (just typing), excel can have trouble
displaying all the text. This usually happens if the length of the string is
over 1000 (or so) characters. The text wraps nicely until it just seems to
fizzle out on the last line displayed.

You can add alt-enters (to force new lines within the cell) every 80-100
characters to see lots more characters.

I'm suggesting that you go back to the table and add those alt-enters to one of
those long strings. Then go back to the worksheet with the formula and change
the data to bring back the value from that test cell.

If it works, then you'll have to modify all those cells with lots and lots of
characters.

Be aware that lots of people have complained that the last line in a cell with a
large amount of text can seem to be chopped. One fix is to add an extra
alt-enter at the end of the cell.

Zak wrote:

Hi,

Thats right - it resized but not enough.

Sorry but im not too sure i know what u mean by "then try adding alt-enter's
every 80-100 characters in the values to be brought back"... does this mean u
require me to shorten my text manually so that the cell is resized
accordingly? i am unable to do this as i require all info that is there.

thanks..

"Dave Peterson" wrote:

Are you saying the rowheight resized, but not enough?

If there is lots of text in that cell, then try adding alt-enter's every 80-100
characters in the values to be brought back. This may help.



Zak wrote:

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

yes, i tried everything as stated but still doesnt work.

thanks for your help



"Dave Peterson" wrote:

How many is a few? Did you do it every 80-100 characters until the end of the
string?

If you did, then I don't have any other suggestions.



Zak wrote:

Hi Dave,

I have tried your suggestion but have no luck. I changed a item of data and
put in a few alt-enters but the cell just doesnt budge.. not even one bit! it
still shows me how many words it did before i put in the alt-enters.

are there any other suggestions?

really appreciate your help.

thanks.

"Dave Peterson" wrote:

When you have long text in a cell (just typing), excel can have trouble
displaying all the text. This usually happens if the length of the string is
over 1000 (or so) characters. The text wraps nicely until it just seems to
fizzle out on the last line displayed.

You can add alt-enters (to force new lines within the cell) every 80-100
characters to see lots more characters.

I'm suggesting that you go back to the table and add those alt-enters to one of
those long strings. Then go back to the worksheet with the formula and change
the data to bring back the value from that test cell.

If it works, then you'll have to modify all those cells with lots and lots of
characters.

Be aware that lots of people have complained that the last line in a cell with a
large amount of text can seem to be chopped. One fix is to add an extra
alt-enter at the end of the cell.

Zak wrote:

Hi,

Thats right - it resized but not enough.

Sorry but im not too sure i know what u mean by "then try adding alt-enter's
every 80-100 characters in the values to be brought back"... does this mean u
require me to shorten my text manually so that the cell is resized
accordingly? i am unable to do this as i require all info that is there.

thanks..

"Dave Peterson" wrote:

Are you saying the rowheight resized, but not enough?

If there is lots of text in that cell, then try adding alt-enter's every 80-100
characters in the values to be brought back. This may help.



Zak wrote:

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

The vlookup in the cell returns a very long string.

Is the cell a merged cell?

If so, merged cells won't autofit without further code.


Gord Dibben MS Excel MVP

On Wed, 17 Dec 2008 08:28:08 -0800, Zak
wrote:

yes, i tried everything as stated but still doesnt work.

thanks for your help



"Dave Peterson" wrote:

How many is a few? Did you do it every 80-100 characters until the end of the
string?

If you did, then I don't have any other suggestions.



Zak wrote:

Hi Dave,

I have tried your suggestion but have no luck. I changed a item of data and
put in a few alt-enters but the cell just doesnt budge.. not even one bit! it
still shows me how many words it did before i put in the alt-enters.

are there any other suggestions?

really appreciate your help.

thanks.

"Dave Peterson" wrote:

When you have long text in a cell (just typing), excel can have trouble
displaying all the text. This usually happens if the length of the string is
over 1000 (or so) characters. The text wraps nicely until it just seems to
fizzle out on the last line displayed.

You can add alt-enters (to force new lines within the cell) every 80-100
characters to see lots more characters.

I'm suggesting that you go back to the table and add those alt-enters to one of
those long strings. Then go back to the worksheet with the formula and change
the data to bring back the value from that test cell.

If it works, then you'll have to modify all those cells with lots and lots of
characters.

Be aware that lots of people have complained that the last line in a cell with a
large amount of text can seem to be chopped. One fix is to add an extra
alt-enter at the end of the cell.

Zak wrote:

Hi,

Thats right - it resized but not enough.

Sorry but im not too sure i know what u mean by "then try adding alt-enter's
every 80-100 characters in the values to be brought back"... does this mean u
require me to shorten my text manually so that the cell is resized
accordingly? i am unable to do this as i require all info that is there.

thanks..

"Dave Peterson" wrote:

Are you saying the rowheight resized, but not enough?

If there is lots of text in that cell, then try adding alt-enter's every 80-100
characters in the values to be brought back. This may help.



Zak wrote:

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Nice guess, Gord!

Gord Dibben wrote:

The vlookup in the cell returns a very long string.

Is the cell a merged cell?

If so, merged cells won't autofit without further code.

Gord Dibben MS Excel MVP

On Wed, 17 Dec 2008 08:28:08 -0800, Zak
wrote:

yes, i tried everything as stated but still doesnt work.

thanks for your help



"Dave Peterson" wrote:

How many is a few? Did you do it every 80-100 characters until the end of the
string?

If you did, then I don't have any other suggestions.



Zak wrote:

Hi Dave,

I have tried your suggestion but have no luck. I changed a item of data and
put in a few alt-enters but the cell just doesnt budge.. not even one bit! it
still shows me how many words it did before i put in the alt-enters.

are there any other suggestions?

really appreciate your help.

thanks.

"Dave Peterson" wrote:

When you have long text in a cell (just typing), excel can have trouble
displaying all the text. This usually happens if the length of the string is
over 1000 (or so) characters. The text wraps nicely until it just seems to
fizzle out on the last line displayed.

You can add alt-enters (to force new lines within the cell) every 80-100
characters to see lots more characters.

I'm suggesting that you go back to the table and add those alt-enters to one of
those long strings. Then go back to the worksheet with the formula and change
the data to bring back the value from that test cell.

If it works, then you'll have to modify all those cells with lots and lots of
characters.

Be aware that lots of people have complained that the last line in a cell with a
large amount of text can seem to be chopped. One fix is to add an extra
alt-enter at the end of the cell.

Zak wrote:

Hi,

Thats right - it resized but not enough.

Sorry but im not too sure i know what u mean by "then try adding alt-enter's
every 80-100 characters in the values to be brought back"... does this mean u
require me to shorten my text manually so that the cell is resized
accordingly? i am unable to do this as i require all info that is there.

thanks..

"Dave Peterson" wrote:

Are you saying the rowheight resized, but not enough?

If there is lots of text in that cell, then try adding alt-enter's every 80-100
characters in the values to be brought back. This may help.



Zak wrote:

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Hi,
yes it is a merged cell!

Please can you help me with the further code? also, would i still need to
alt-enter?

thanks guys!

"Dave Peterson" wrote:

Nice guess, Gord!

Gord Dibben wrote:

The vlookup in the cell returns a very long string.

Is the cell a merged cell?

If so, merged cells won't autofit without further code.

Gord Dibben MS Excel MVP

On Wed, 17 Dec 2008 08:28:08 -0800, Zak
wrote:

yes, i tried everything as stated but still doesnt work.

thanks for your help



"Dave Peterson" wrote:

How many is a few? Did you do it every 80-100 characters until the end of the
string?

If you did, then I don't have any other suggestions.



Zak wrote:

Hi Dave,

I have tried your suggestion but have no luck. I changed a item of data and
put in a few alt-enters but the cell just doesnt budge.. not even one bit! it
still shows me how many words it did before i put in the alt-enters.

are there any other suggestions?

really appreciate your help.

thanks.

"Dave Peterson" wrote:

When you have long text in a cell (just typing), excel can have trouble
displaying all the text. This usually happens if the length of the string is
over 1000 (or so) characters. The text wraps nicely until it just seems to
fizzle out on the last line displayed.

You can add alt-enters (to force new lines within the cell) every 80-100
characters to see lots more characters.

I'm suggesting that you go back to the table and add those alt-enters to one of
those long strings. Then go back to the worksheet with the formula and change
the data to bring back the value from that test cell.

If it works, then you'll have to modify all those cells with lots and lots of
characters.

Be aware that lots of people have complained that the last line in a cell with a
large amount of text can seem to be chopped. One fix is to add an extra
alt-enter at the end of the cell.

Zak wrote:

Hi,

Thats right - it resized but not enough.

Sorry but im not too sure i know what u mean by "then try adding alt-enter's
every 80-100 characters in the values to be brought back"... does this mean u
require me to shorten my text manually so that the cell is resized
accordingly? i am unable to do this as i require all info that is there.

thanks..

"Dave Peterson" wrote:

Are you saying the rowheight resized, but not enough?

If there is lots of text in that cell, then try adding alt-enter's every 80-100
characters in the values to be brought back. This may help.



Zak wrote:

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

We'll see<g


On Wed, 17 Dec 2008 14:13:10 -0600, Dave Peterson
wrote:

Nice guess, Gord!

Gord Dibben wrote:

The vlookup in the cell returns a very long string.

Is the cell a merged cell?

If so, merged cells won't autofit without further code.

Gord Dibben MS Excel MVP

On Wed, 17 Dec 2008 08:28:08 -0800, Zak
wrote:

yes, i tried everything as stated but still doesnt work.

thanks for your help



"Dave Peterson" wrote:

How many is a few? Did you do it every 80-100 characters until the end of the
string?

If you did, then I don't have any other suggestions.



Zak wrote:

Hi Dave,

I have tried your suggestion but have no luck. I changed a item of data and
put in a few alt-enters but the cell just doesnt budge.. not even one bit! it
still shows me how many words it did before i put in the alt-enters.

are there any other suggestions?

really appreciate your help.

thanks.

"Dave Peterson" wrote:

When you have long text in a cell (just typing), excel can have trouble
displaying all the text. This usually happens if the length of the string is
over 1000 (or so) characters. The text wraps nicely until it just seems to
fizzle out on the last line displayed.

You can add alt-enters (to force new lines within the cell) every 80-100
characters to see lots more characters.

I'm suggesting that you go back to the table and add those alt-enters to one of
those long strings. Then go back to the worksheet with the formula and change
the data to bring back the value from that test cell.

If it works, then you'll have to modify all those cells with lots and lots of
characters.

Be aware that lots of people have complained that the last line in a cell with a
large amount of text can seem to be chopped. One fix is to add an extra
alt-enter at the end of the cell.

Zak wrote:

Hi,

Thats right - it resized but not enough.

Sorry but im not too sure i know what u mean by "then try adding alt-enter's
every 80-100 characters in the values to be brought back"... does this mean u
require me to shorten my text manually so that the cell is resized
accordingly? i am unable to do this as i require all info that is there.

thanks..

"Dave Peterson" wrote:

Are you saying the rowheight resized, but not enough?

If there is lots of text in that cell, then try adding alt-enter's every 80-100
characters in the values to be brought back. This may help.



Zak wrote:

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson

Jim Rech wrote a macro called AutoFitMergedCellRowHeight:
http://groups.google.com/groups?thre...%40tkmsftngp05

maybe you can merge it into your event procedure.

Zak wrote:

Hi,
yes it is a merged cell!

Please can you help me with the further code? also, would i still need to
alt-enter?

thanks guys!

"Dave Peterson" wrote:

Nice guess, Gord!

Gord Dibben wrote:

The vlookup in the cell returns a very long string.

Is the cell a merged cell?

If so, merged cells won't autofit without further code.

Gord Dibben MS Excel MVP

On Wed, 17 Dec 2008 08:28:08 -0800, Zak
wrote:

yes, i tried everything as stated but still doesnt work.

thanks for your help



"Dave Peterson" wrote:

How many is a few? Did you do it every 80-100 characters until the end of the
string?

If you did, then I don't have any other suggestions.



Zak wrote:

Hi Dave,

I have tried your suggestion but have no luck. I changed a item of data and
put in a few alt-enters but the cell just doesnt budge.. not even one bit! it
still shows me how many words it did before i put in the alt-enters.

are there any other suggestions?

really appreciate your help.

thanks.

"Dave Peterson" wrote:

When you have long text in a cell (just typing), excel can have trouble
displaying all the text. This usually happens if the length of the string is
over 1000 (or so) characters. The text wraps nicely until it just seems to
fizzle out on the last line displayed.

You can add alt-enters (to force new lines within the cell) every 80-100
characters to see lots more characters.

I'm suggesting that you go back to the table and add those alt-enters to one of
those long strings. Then go back to the worksheet with the formula and change
the data to bring back the value from that test cell.

If it works, then you'll have to modify all those cells with lots and lots of
characters.

Be aware that lots of people have complained that the last line in a cell with a
large amount of text can seem to be chopped. One fix is to add an extra
alt-enter at the end of the cell.

Zak wrote:

Hi,

Thats right - it resized but not enough.

Sorry but im not too sure i know what u mean by "then try adding alt-enter's
every 80-100 characters in the values to be brought back"... does this mean u
require me to shorten my text manually so that the cell is resized
accordingly? i am unable to do this as i require all info that is there.

thanks..

"Dave Peterson" wrote:

Are you saying the rowheight resized, but not enough?

If there is lots of text in that cell, then try adding alt-enter's every 80-100
characters in the values to be brought back. This may help.



Zak wrote:

I am seeing the message box fine and now see the changes take place too
(however might be removing the message box from the code as its not really
required) but i noticed it didnt auto-fit something accordingly. To test it i
selected something from my drop down list which was small - obvioulsy this
made the boxes small.. but when i selected something bigger from the drop
down the box didnt re-size (it had a few words missing until i had to
manually increase the size of it (a box 4 rows deep)).??

sorry for this! thanks for your help

"Dave Peterson" wrote:

No.

This code should fire when the worksheet recalculates.

Put the code in the correct worksheet module (described in an earlier post).

Then go back to excel and put this in an empty cell:
=rand()
and hit F9

You should see that msgbox each time you hit F9. If you don't, then you're
doing something wrong.

Zak wrote:

Hi,

Thanks, i got the message box to show but i dont understand - i dont see any
changes to cell sizes?? nothing appears to be changing.. also from my
understanding this code is 'prompt driven' right? so it wont run unless i
click a button?

thanks.

"Dave Peterson" wrote:

Try this version:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
msgbox "event is running"
Me.Rows.AutoFit
application.enableevents = true
End Sub

If you don't get the msgbox, then you either:
1. disallowed macros to run
2. put the code in the wrong location
3. turned off events and didn't re-enable them

#1, make sure you allow macros to run when you open the workbook.

#2, make sure you rightclicked on the worksheet tab that should have this
behavior and select view code. The code goes in the righthand side code window.

#3, Open the VBE
Hit ctrl-g
type this
application.enableevents = true
and hit enter

Then back to excel and recalculate to test.


Zak wrote:

Hi,

I want to apply this 'auto fit' rule the whole sheet.. how do i change the
code to reflect this? Also, i ran the macro as it is but i dont see any
changes? i have deliberately put in a whole load of text into 1 cell to see
if it auto-fits it but no luck..

What am i doing wrong?

Thanks in advance.

"Dave Peterson" wrote:

Make sure you save the file as a macro-enabled workbook. Do a file|save as (or
whereever it is in xl2007) and choose the file type with an extension of .xlsm.

And then save and close that workbook.

Then reopen the workbook to test.

Depending on your security settings, you may have to choose to allow macros to
run. That prompt will either be a popup window (like in xl2003) or it'll be on
a "bar" between the worksheet cells and the ribbon.



Zak wrote:

Hi,

Thanks for this info - i tried to make it work but as i entered the code
into the VB window for the required worksheet i kept getting the sign "The
following features cannot be saved in macro-free workbooks - VB Project"... i
have word 2007, not sure why i am getting this sign are you able to shed any
light please?

And does this macro work automatically? i.e it doesnt require a user to
click on any button? it will just run in the background and adjust to cells
widths when the vlookup is used?

Thanks in advance.

"Dave Peterson" wrote:

Maybe you can tie into that worksheet's calculation event to resize the rows.

If you want to try, right click on the worksheet that should have this
behavior. Select View code. Paste this into the code window:

Option Explicit
Private Sub Worksheet_Calculate()
application.enableevents = false
Me.Rows.AutoFit
'or be specific
Me.Rows("1:33").AutoFit
application.enableevents = true
End Sub

Changing the rowheight in some versions of excel (xl2003+, IIRC) will cause the
excel to want to recalc again. The .enableevents stuff stops excel from going
into a loop--recalc, loop, recalc, loop, ....

Zak wrote:

Hi,

I have a simple vlookup which is used to pull across data.. so i have one
cell which has a drop down list, the user selects something from there - a
cell a few rows down is auto populated with the required data depening on
what was selected from this first cell (what would be pulled across is a list
(small or big)).

I think i have a simple problem here - i want the cells to auto-fit to the
contents of the cell, for example, i am finding if the vlookup brings across
a long list then the cell doesnt change size to fit all the text (doesnt wrap
text) and instead the user has to manually change the size or have it
permanently big, which isnt very nice.

is there a formula or an extention to the vlookup or any other way i can do
this?
please help. thanks.

--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson