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