Results 1 to 5 of 5
  1. #1
    New Lounger
    Join Date
    Feb 2003
    Location
    West Hollywood, California, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    VLOOKUP & Automatically Adjust Row Height (Excel 2003)

    I have a sheet where I enter in data in columns. Let

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLOOKUP & Automatically Adjust Row Height (Excel 2003)

    You'd have to create a Worksheet_Change event procedure for the data entry sheet:
    - Right-click the sheet tab of the data entry sheet.
    - Select View Code.
    - Copy the following code into the module:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:B")) Is Nothing Then
    Application.EnableEvents = False
    Worksheets("ViewSheet").Range("1:2").EntireRow.Aut oFit
    Application.EnableEvents = True
    End If
    End Sub

    - Change Columns("A:A") to the appropriate reference on the entry sheet, e.g. Colums("D:H")
    - Change "ViewSheet" to the name of the view sheet.
    - Change Range("1:2") to the appropriate reference on the view sheet, e.g. Range("4:8").

  3. #3
    New Lounger
    Join Date
    Feb 2003
    Location
    West Hollywood, California, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP & Automatically Adjust Row Height (Excel 2003)

    Worked like a champ. My boss and co-worker (both total Excel wonks) were very impressed! Thanks!

    -kevin

  4. #4
    New Lounger
    Join Date
    Feb 2003
    Location
    West Hollywood, California, USA
    Posts
    8
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: VLOOKUP & Automatically Adjust Row Height (Excel 2003)

    Actually, I spoke too soon. The code works fine if the users edit a cell that is in the Range as defined on the second line.

    But if I just do a sort, the code is not triggered. Even if cells with in the range are relocated by the sort.

    I tried commenting out the If... and End If lines -- so that the code would engage if any change to the work sheet was made, but simply using the sort function did not trigger the code.

    Is there another way to trigger the code if the user only performs a sort (and does not edit any cell)?

    My back up solution is to just make a sort "button" on the Entry Sheet that does the sort and uses this code at the end, but I would rather that users just use the normal sort function and have this code run without any user intervention.

    Any ideas?

    -kevin

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: VLOOKUP & Automatically Adjust Row Height (Excel 2003)

    Sorting the worksheet doesn't trigger the Worksheet_Update event. I think you'll have to create a command button that sorts, then AutoFits.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •