Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup problem: Row height doesnt adjust
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup problem: Row height doesnt adjust
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 Kevryl wrote: 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup problem: Row height doesnt adjust
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" wrote: 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 Kevryl wrote: 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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup problem: Row height doesnt adjust
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???? Kevryl wrote: 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" wrote: 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 Kevryl wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup problem: Row height doesnt adjust
Thanks Dave, reply IN TEXTbelow
"Dave Peterson" wrote: 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. 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???? Kevryl wrote: 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" wrote: 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 Kevryl wrote: 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 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup problem: Row height doesnt adjust
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 Kevryl wrote: Thanks Dave, reply IN TEXTbelow "Dave Peterson" wrote: 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. 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???? Kevryl wrote: 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" wrote: 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 Kevryl wrote: 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 -- Dave Peterson -- Dave Peterson |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Lookup problem: Row height doesnt adjust
Thank you Dave ...PAL! You came thru for me :-)
"Dave Peterson" wrote: 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 Kevryl wrote: Thanks Dave, reply IN TEXTbelow "Dave Peterson" wrote: 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. 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???? Kevryl wrote: 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" wrote: 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 Kevryl wrote: 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 -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup Problem | Excel Worksheet Functions | |||
automatically adjust row height in excel 2000 | Excel Discussion (Misc queries) | |||
Excel adjust row height upon merging 2 cell with word wrap | Excel Discussion (Misc queries) | |||
Lookup function problem (kg) | Excel Worksheet Functions | |||
problem lookup function | Excel Worksheet Functions |