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.