Lookup problem: Row height doesnt adjust

[Guest]

Using Vlookup.

Rows in referring worksheet are set to autofit.

When I make a new entry in the table, the height of the dependant cell in
another worksheet referring to the entry in the table will not expand to
accommodate the height of the new entry. But if I then do some minor thing
while in that cell, like clicking the text colour drop-down the row height
readjusts.

What can I do to make the dependent row height automatically expand when a
new entry is made in the lookup table?
Regards,
Keith

 

[Dave Peterson]

Maybe you could you could use an event macro that resizes the rows whenever the
sheet recalculates.

If you want to try, rightclick on the worksheet tab that should have this
behavior and select view code.

Paste this in:

Option Explicit
Private Sub Worksheet_Calculate()
Me.Rows.AutoFit
End Sub

 

[Guest]

Dave, thanks. I think you're on the right track here, and it works if I
manually unprotect the sheet. Problem is the debugger says I can't use that
command on a protected sheet. I'm very green with VB, but I have tried
inserting the Activesheet.Unprotect command. It didn't work, I guess because
the worksheet wasn't strictly speaking "active" while I was making an entry
into the lookup table worksheet. My version of your instruction looks like
this:

Private Sub CommandButton1_Click()

End Sub
Option Explicit
Private Sub Worksheet_Calculate()
ActiveSheet.Unprotect
Me.Rows.AutoFit
End Sub

I suspect that I have to specify which sheet to unprotect (and then
reprotect), but I haven't a clue as to the command or syntax.

Thanks for your help.
Regards,
Keith

 

[Dave Peterson]

This code goes behind the worksheet with the formula--not the worksheet with the
table.

Option Explicit
Private Sub Worksheet_Calculate()
me.Unprotect password:="yourpasswordhere"
Me.Rows.AutoFit
me.protect password:="yourpasswordhere"
End Sub

And if that worksheet is protected, I'd bet you'd want to reprotect it, too.

And you may have a password applied????

 

[Guest]

Thanks Dave, reply IN TEXTbelow

This code goes behind the worksheet with the formula--not the worksheet with the
table.

YES, THAT WAS THE WAY I DID IT. BUT ON MAKING AN ENTRY IN THE LOOKUP SHEET
THE OTHER SHEET RECALCULATES AS SOON AS YOU MOVE THE CURSOR FROM THE CELL.I HAVE TRIED YOUR CODE, MODIFYING IT TO EXCLUDE PASSWORD - NOT NEEDED - AND
IT RESIZES ROW HEIGHT. PROBLEM IS IT RESIZES THE WHOLE SHEET INCLUDING HEADER
ROWS THAT CONTAIN A COUPLE OF DOZEN MACRO BUTTONS. I GUESS LIMITING THE
HEIGHT RESIZE TO JUST THE DEPENDANT ROW IS A BIT MORE COMPLEX.

 

[Dave Peterson]

You can limit the range to exclude the header rows -- or any other rows you
want.

Option Explicit
Private Sub Worksheet_Calculate()
me.Unprotect
Me.Rows("2:87").AutoFit
me.rows("95:107").autofit
'add more if you need to.
me.protect
End Sub

Thanks Dave, reply IN TEXTbelow

This code goes behind the worksheet with the formula--not the worksheet with the
table.

YES, THAT WAS THE WAY I DID IT. BUT ON MAKING AN ENTRY IN THE LOOKUP SHEET
THE OTHER SHEET RECALCULATES AS SOON AS YOU MOVE THE CURSOR FROM THE CELL.I HAVE TRIED YOUR CODE, MODIFYING IT TO EXCLUDE PASSWORD - NOT NEEDED - AND
IT RESIZES ROW HEIGHT. PROBLEM IS IT RESIZES THE WHOLE SHEET INCLUDING HEADER
ROWS THAT CONTAIN A COUPLE OF DOZEN MACRO BUTTONS. I GUESS LIMITING THE
HEIGHT RESIZE TO JUST THE DEPENDANT ROW IS A BIT MORE COMPLEX.

 

[Guest]

Thank you Dave ...PAL! You came thru for me

You can limit the range to exclude the header rows -- or any other rows you
want.

Option Explicit
Private Sub Worksheet_Calculate()
me.Unprotect
Me.Rows("2:87").AutoFit
me.rows("95:107").autofit
'add more if you need to.
me.protect
End Sub