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.