Using excel to embed multiple transparent links to other excel...

[Guest]

.... sheets and keeping them lined up;

The problem i'm having is quite frustrating. I have 3 sheets generated each
has been sized exactly the same (each cell is 100x100 pixels)

The 1st sheet contains a hexagonal map grid. using A1 style reference the
top-left corner is indexed by C3 and the lower-right corner is referenced by
AF50. Column A and B are blank (100x100) in all sheets; Rows 1 and 2 are
blank (100x100) too.

The second sheet contains generated text in a tiny font. The columns are
staggered with the even cells (B,D,F...) are aligned horizontally on the
bottom, odd cells are aligned horizontally on the top. (this makes the text
appear to center in each hexagon)

The third sheet contains very large text (using the Character map with
eudcedit to make arrows that point in the 6 possible directions for each
hexagon. up(0°), down(180°), up-right(60°), down-right(120°),
down-left(240°), up-left(300°). The alignment is the same as the 2nd sheet
with even/odds staggered.

Now I have combined these 3 sheets using the insert>object; "create from
file"; with link checked (because all of this data changes dynamically and
the text and arrows should move to reflect the current condition of my data).

The problem is that these 3 embeded-linked objects will not seem to stay
lined up. The hexmap will sometimes shift up by one row, I can not find any
pattern with this. Is there an easier solution? The sizing seems to have a
mind of its own. (sometimes with creating the link.. the object is smaller
than the actual file and only the top left quadrant of data appear. When I
try to resize the object... the visible data just shrinks or grows with mouse
drags (instead of showing more of the data in the other 3 quadrants)

Is there a way to pick up a bitmap and put it at a very specific point in a
cell? The arrows(in a very large font) might could be saved as plain BMP
files and used that way (if i could control the placement)

Im considering using VB to generate this combination too (but i wanted to
exhaust the excel options first, then I have to learn VB...)

Any help is greatly appreciated.

(using both excel2003 and 2000; but im about to upgrade the 2nd machine)

 

[Tom Ogilvy]

Did you try lining up your 3 layers, then grouping them?

Then right click and look at the format options

 

[Guest]

Im trying to do that now... but I cant seem to select the one I want without
using the "send to back" feature to move 1 of the 3 out of the way... but
then I can't seem to keep each selected (CTRL+click) so I can use the "group"
feature.

For some reason when I set each of the 3 to 100% transparent (3 itterations
of rt-click>format object>Colors and Lines>set transparency to 100%) one of
the sheets remains opaque...

Did you try lining up your 3 layers, then grouping them?

Then right click and look at the format options

 

[Tom Ogilvy]

Might try doing the work with code.

Look at help on the ShapeRange property.

I put 3 rectangles on the worksheet and aligned and grouped them. It
recorded like this:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 2/15/2005 by togilvy
'

'
ActiveSheet.Shapes.Range(Array("Rectangle 3", "Rectangle 2", "Rectangle
1")). _
Select
Selection.ShapeRange.Align msoAlignLefts, False
Selection.ShapeRange.Align msoAlignTops, False
Selection.ShapeRange.Group.Select
End Sub

--
Regards,
Tom Ogilvy

Im trying to do that now... but I cant seem to select the one I want without
using the "send to back" feature to move 1 of the 3 out of the way... but
then I can't seem to keep each selected (CTRL+click) so I can use the "group"
feature.

For some reason when I set each of the 3 to 100% transparent (3 itterations
of rt-click>format object>Colors and Lines>set transparency to 100%) one of
the sheets remains opaque...

 

[Guest]

I havent tried the "code" method yet, frankly it looks like jibberish to me..
so far (But I have scratched the surface of macros and have seen how each
operation will translate into the VB code that will do that operation.) Will
play with that some more soon...

The problem that has popped up now is that when i link to the 3 files as an
object... I can use the "send to back" feature and get to object properties
on all 3 (the name shows in the formula bar at the top of excel...) I set
ALL 3 to 100% transparent... but 1 of the objects remains opaque. I have
triple checked each and every objects setting and they are 100%
transparent... but not..

any ideas what is happening?

Lining up the hex-map might be as simple as putting another object in the
source file such that the extreme top-left is not just blank cells... then
grouping the whole thing together.. that seemed to make the hex map on
linking.. appear that it will line up now.

Thanks for your replies to this.