Results 1 to 4 of 4
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Sample code exists on the web for detecting overlapping ranges, but I couldn't find any code that resolved overlapping ranges.

    The attached workbook demonstrates crude code for relocating one range with respect to another.

    Typical example:
    The user runs an application that generates a tabled report, and which also generates a legend block. The user configures the legend block to appear above, below, or besides the report. (This is a separate issue).
    The user might select parameters which would cause the legend to overlap the report.

    Our job is to detect overlap and move the legend to a better place, by re-adjusting its range.

    The code is incomplete, but it does appear to move the legend away from the report.
    Better code would optimize the movement so that the movement traveled the minimal distance to exit the report range.
    Better code would cater for the legend wanting to move off the edge of the spreadsheet, e.g. to the left of column “A” or above row “1”, and so on.

    The supplied workbook has a light-green range “Report” and a dark green range “Legend” (use the F5 function key).
    Running the macro “TESTFixRangeOverlap” will highlight a set of cells outside the report, and will reset the named range “Legend”.
    As you test with different positions, you’ll need to reset the Legend range to re-cover the dark green cells, and then drag those dark green cells to a new position, or else you may get confused as to whether the legend really has been moved or not!

    If this doesn’t work for you I’ll buy you lunch at The Montreal Deli, Dundas street in Mississauga. Phone to arrange an appointment.
    Attached Files Attached Files

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    If the user parameters are only above, below or beside, then the legend could not overlap the table unless the application were poorly written...
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='776410' date='22-May-2009 09:16']If the user parameters are only above, below or beside, then the legend could not overlap the table unless the application were poorly written... [/quote]

    ... or poorly specified.
    Thanks Rory for the earthquake-like nudge of the old elbow.
    Besides "quick-placement" buttons called "N", "NE, "E", "SE" etc there is^H^H was a scheme to allow the users to specify an (x,y) displacement, where x+ve meant rows displacement above above, y-ve meant columns displacement to the left.
    I suspect (and am back to paper-and-pencil) that there is a better way of specifying the displacement, and I ought to go back and check that out.

    Bother.
    It was rather fun devising that little bit of code .....

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='rory' post='776410' date='22-May-2009 09:16']If the user parameters are only above, below or beside,[/quote]
    Thanks again, Rory.
    I did some more work on the user interface.
    If I've got it right, the user can place the legend anywhere they like near/around, but not overlapping, the report.

    The workbook demonstrates some prototype code for placing a legend near a report without overlap.
    The code was tested in Excel 2000, and can be streamlined, for sure.
    The code does not include tests for sheet boundaries; that is, if you choose an offset that takes you above row 1 or to the left of column A you will land in trouble!
    Attached Files Attached Files

Posting Permissions

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