Darren
This line
Me.ImageBox.Left = .Item(.Cells.Count - 2).Left
sets the left of the ImageBox to the left of a certain cell. To find that
cell, it finds the last cell that's in the visible range and goes two cells
to the left. If your screen shows A1:S48, then the left of the ImageBox
would be the same as the left of Q48. The -2 is abitrary because my columns
were of a sufficient width compared to the ImageBox that it showed the whole
thing.
If your column widths are constant throughout the worksheet, you should, by
trial and error, be able to find the right number. If they're not constant,
I think it will be difficult to position that ImageBox with any reliability.
However, you can try this
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.Parent.Windows(1).VisibleRange
Me.Image1.Top = .Item(1).Top
With .Item(.Cells.Count - 1)
Me.Image1.Left = .Left + .Width - Me.Image1.Width
End With
End With
End Sub
This will align the right edge of the ImageBox with the right edge of the
right-most fully visible cell. If you don't have a partially visible cell,
this may end up to far to the left, but I'm not sure how to test for that.
The *.4 that works on your screen may not work on another screen. I think
aligning the right edges will serve you better.
Let me know what you think.
--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.
"Darren Hill" wrote in message
...
Thanks for the explanation, that Hyperlink_Base method look interesting
and
your Follow_Hyperlink method is neat (once I got my head round what was
going on).
You asked how I was triggering the macro - I am using Selection Change and
I
discovered the VisibleRange.
The code you suggested operates a little oddly.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.Parent.Windows(1).VisibleRange
Me.ImageBox.Top = .Item(1).Top
Me.ImageBox.Left = .Item(.Cells.Count - 2).Left
'Me.ImageBox.Height = .Height / 2
'Me.ImageBox.Width = .Width / 2
End With
End Sub
The image appears off to the right of the window, only partially in view
(if
at all). This occurs with or without the resizing intructions. Any idea
why?
The macro I had been using (below) does work, with sizing instructions to
ensure it always remains fully in the window. The *.4 & other multiples
are
there because direct halves like those above resulted in small parts of
the
image being lost around the edges. This suggests VisibleRange includes
scrollbars - how do I take their size into account without clumsy
multiples
like those below?
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.ImageBox
.Top = Application.ActiveWindow.VisibleRange.Top
.Left = (Application.ActiveWindow.VisibleRange.Left _
+ Application.ActiveWindow.Width) * 0.4
.Height = Application.ActiveWindow.Height * 0.9
.Width = Application.ActiveWindow.Width / 2
End With
End Sub
Thanks, Tom and Dick.
--
Darren
""Dick Kusleika" wrote in
message
...
Darren
Mind if I jump in?
I see, so I was wrong about why it was failing - not too surprising :)
By the way, what's the difference between Address and SubAddress? Help
isn't
very helpful.
The Address is the webpage or document to which the hyperlink points.
The
SubAddress is the place in the document to which it points. Take this
web
example
http://www.dicks-clicks.com/excel/ol...arly%20Binding
The address is "http:\\www.dicks-clicks.com/excel/olBinding.htm"
The SubAddress is "Early Binding"
Early Binding is a bookmark on that page so while the hyperlink takes
you
to
that page, it also takes you to the place on that page that you specify
in
SubAddress.
For files, you can have a hyperlink like this
C:\Book3.xls#Sheet1!A10
where C:\Book3.xls is the Address and Sheet1!A10 is the SubAddress. The
hyplerlink opens Book3, then selects Sheet1!A10.
Then when the code faulted at the Me.Imagebox line, I used the
Locals
window
to check the value of of sStr, and it was reported as a relative
reference,
for example
"..\..\..\My Pictures\01.jpg"
I disagree with Tom (although will be happy to know if I'm wrong). I
think
this is relative. It says to go back three directory levels, then
forward
through My Pictures to find the file. All relative to the current
workbooks
location.
My experience with relative vs. absolute hyperlinks in Excel is that you
can't have absolute hyperlinks. There used to be a checkbox in Excel97
(I
think) that appeared to allow you to make an absolute hyperlink. But
that
didn't work and I think they just removed the option instead of fixing
it.
One option you have is to use the Hyperlink Base (under File -
Properties).
With this, all hyperlinks will be relative to this base instead of to
the
files location. That works in some situations, but I've never seen the
need
to use it.
For your situation, if you were still using hyperlinks, I think the way
to
go is to use dummy hyperlinks and the FollowHyperlink Event. Instead of
creating hyperlinks to those files, you would create a hyperlink that
pointed to the cell that holds the hyperlink and use the TextToDisplay
property to hold the filename. This does a few things for you: First,
by
pointing to cell containing the link, the hyperlink doesn't really do
anything, that is, it selects the cell that's already selected and
therefore
there's nothing to cancel; second, using the TestToDisplay property
makes
the link look (to the user) like it's pointing to that file; third, you
store the file path in the TextToDisplay and you don't have to worry
about
relative links.
Once you've set up your links like that, you can use the FollowHyperlink
event like this:
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Me.Image1.Picture = LoadPicture(Target.TextToDisplay)
End Sub
Because the link is pointing to itself ( or at least the cell) there's
nothing to cancel and you use the path stored in TextToDisplay to load
the
picture.
I've hit another snag. I planned to have the imagebox control
reposition
itself at the top and right of the active window, so that it remained
there
as you scroll about the sheet.
The following code does this as long as I don't scroll right.
With Me.ImageBox
.Top = Application.ActiveWindow.Top
.Left = Application.ActiveWindow.Width / 2
.Height = Application.ActiveWindow.Height / 2
.Width = Application.ActiveWindow.Width / 2
End With
But if I scroll to the right, the picture doesn't continue to move
right.
I
need to adjust the ".left" line to take the current window position
into
account. Can you show me how to fix it?
How are you triggering this macro? There's no scroll event that I know
of,
so I wouldn't expect it to move when you scroll. You could use the
selection change event like this
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Me.Parent.Windows(1).VisibleRange
Me.Image1.Top = .Item(1).Top
Me.Image1.Left = .Item(.Cells.Count - 2).Left
End With
End Sub
to position it based on the VisibleRange, but this will only fire when
you
select a different cell, not when you scroll.
--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.