Excel as Image Browser

[Darren Hill]

(Excel2000)

I have a list of image files and their hyperlinks in a spreadsheet.
I'd like to have an image control which floats at the top right of the sheet
(I can do that).

When the Hyperlink is clicked, it is cancelled, and the image that would
have opened is instead loaded into the image control.

1) How do I stop the Hyperlink activating?
2) What code do I need to change the image box picture. The following
doesn't work.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Me.ImageBox.Picture = Target
End Sub

Thanks,

Darren

[Tom Ogilvy]

There is no built in capability to cancel a hyperlink action.

sStr = "C:\Windows\Mypicture.jpg"
Me.ImageBox.Picture = LoadPicture(sStr)

--
Regards,
Tom Ogilvy

"Darren Hill" <star...@btinternet.com> wrote in message
news:bqnrbh$ip4$1...@hercules.btinternet.com...

[Darren Hill]

Thanks.
I'll use the SelectionChange to simulate hyperlink behaviour.
Once puzzle: I have the following event, but when I click on a hyperlink
nothing happens.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim sStr As String
sStr = "C:\Documents and Settings\Darren\My Documents\My
Pictures\01.jpg"
Me.ImageBox.Picture = LoadPicture(sStr)

End Sub

I've put a breakpoint in the code to test of the event is actually firing,
and it isn't. Any ideas?

--
Darren
"Tom Ogilvy" <twog...@msn.com> wrote in message
news:vsut5le...@news.supernews.com...

[Darren Hill]

I'm embarrassed to admit - the puzzle below was because I was in design mode
by accident. Oops :)

But another problem - I created my hyperlinks with VBA. They were definitely
absolute references then. But now they same to have changed to relative
references and so the followhyperlink event brings up an error.

Is there a way to force Hyperlinks to remain as absolute references?

--
Darren

"Darren Hill" <star...@btinternet.com> wrote in message

news:bqntf0$6k9$1...@titan.btinternet.com...

[Tom Ogilvy]

Are you in Design Mode?

The following worked for me:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim sStr As String

MsgBox "in followhyperlink " & Target.SubAddress
sStr = "C:\Documents and Settings\" & _
"ogilvtw\My Documents\My Pictures\Part1.jpg"
Me.Image1.Picture = LoadPicture(sStr)
End Sub

--
Regards,
Tom Ogilvy

"Darren Hill" <star...@btinternet.com> wrote in message

news:bqntf0$6k9$1...@titan.btinternet.com...

[Tom Ogilvy]

I haven't run up against this although I don't do much with hyperlinks.
Where are you hyperlinking to? Post your code that creates the hyperlink.

How are you determining that it is now relative - are you editing it
manually or using code to pull the attribute values for address and
subaddress?

Regards,
Tom Ogilvy

"Darren Hill" <star...@btinternet.com> wrote in message

news:bqnuc2$8km$1...@titan.btinternet.com...

[Darren Hill]

You asked

> How are you determining that it is now relative - are you editing it
> manually or using code to pull the attribute values for address and
> subaddress?

I was using the Address property not SubAddress, in the following code:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim sStr As String

sStr = Target.Address
Me.ImageBox.Picture = LoadPicture(sStr)

End Sub

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"

What's the difference between Address and SubAddress?

I've posted the code used to build the hyperlinks below, but don't put
yourself out trying to solve the problem if it's not obvious. I'm not using
hyperlinks in this project anymore, but I'm curious because I may want to
use Hyperlinks in the future.

THE CODE
(For clarity, I've put ======= around the areas directly related to building
the hyperlinks).

Macro: CreateHyperlinkFileList - this calls "CreateFileList" to create an
array of addresses. It then loops through the array, extracts data from the
picture file names (they are named in a very particular pattern), and then
creates the hyperling (the second set of "===").
The CreateFileList macro: simply builds the array of addresses. I'm not
entirely sure how it does this ;) since I copied it from a website, but it
works.

================
================
Function CreateFileList(FileFilter As String, _
IncludeSubFolder As Boolean) As Variant
' returns the full filename for files matching
' the filter criteria in the current folder
Dim FileList() As String, FileCount As Long
CreateFileList = ""
Erase FileList
If FileFilter = "" Then FileFilter = "*.*" ' all files
With Application.FileSearch
.NewSearch
.LookIn = CurDir
.FileName = FileFilter
.SearchSubFolders = IncludeSubFolder
.FileType = msoFileTypeAllFiles
If .Execute(SortBy:=msoSortByFileName, _
SortOrder:=msoSortOrderAscending) = 0 Then Exit Function
ReDim FileList(.FoundFiles.Count)
For FileCount = 1 To .FoundFiles.Count
FileList(FileCount) = .FoundFiles(FileCount)
Next FileCount
.FileType = msoFileTypeExcelWorkbooks ' reset filetypes
End With
CreateFileList = FileList
Erase FileList
End Function
================
================
Sub CreateHyperlinkFileList()
Dim FileNamesList As Variant, i As Integer, j As Integer
Dim FileName As String, ArtistName As String, FileAddress As String
ChDir "C:\Documents and Settings\Darren\My Documents\My Pictures"
' activate the desired startfolder for the filesearch
'===================================
FileNamesList = CreateFileList("*.*", True)
'===================================
' performs the filesearch, includes any subfolders
' present the result
Range("A:B").Clear
For i = 1 To UBound(FileNamesList)
' create hyperlinks here
FileAddress = FileNamesList(i)
FileName = FileOrFolderName(FileAddress, True)

j = 0
While InStr(j + 1, FileName, "_") > 0
j = InStr(j + 1, FileName, "_")
Wend
ArtistName = Left(FileName, j - 1)
If ArtistName = "" Or ArtistName = "_" Then ArtistName = "Unknown"
FileName = Right(FileName, Len(FileName) - j)
FileName = Left(FileName, InStr(FileName, ".") - 1)

With ActiveSheet
.Range("a" & i + 1).Formula = ArtistName
'=============================================
.Hyperlinks.Add Anchor:=.Range("b" & i + 1), _
Address:=FileNamesList(i), _
ScreenTip:=ArtistName, _
TextToDisplay:=FileName
'=============================================
End With
Next i
Columns("A:B").EntireColumn.AutoFit
Columns("A:B").HorizontalAlignment = xlLeft
Range("A2").CurrentRegion.Sort Key1:=Range("A2"), Order1:=xlAscending,
Key2:=Range("B2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:=
_
False, Orientation:=xlTopToBottom

End Sub

[Tom Ogilvy]

That isn't a relative reference - it is a method of showing a long string in
a limited space - it omits non-essential information.

--
Regards,
Tom Ogilvy

"Darren Hill" <star...@btinternet.com> wrote in message

news:bqo3k4$7p0$1...@hercules.btinternet.com...

[Darren Hill]

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.

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?

Darren

"Tom Ogilvy" <twog...@msn.com> wrote in message

news:vsv576j...@news.supernews.com...

[Darren Hill]

Aha, I figured out the scrolling thing using the VisibleRange property.
The only mystery remaining: the difference between Address and SubAddress.
When should I use the latter?

--
Darren

"Darren Hill" <star...@btinternet.com> wrote in message

news:bqo6h5$t1$1...@sparta.btinternet.com...

[Tom Ogilvy]

I can't tell you that. Record a macro setting up the hyperlink manually and
see how it gets set up. As an example, in the same workbook, the Address
isn't used.

Since there isn't any scroll event, what event are you using to position
your image control?

--
Regards,
Tom Ogilvy

"Darren Hill" <star...@btinternet.com> wrote in message

news:bqo8fs$hq6$1...@hercules.btinternet.com...

[Dick Kusleika]

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/olBinding.htm#Early%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.

[Tom Ogilvy]

I stand corrected - I believe Dick is correct that it is a relative
reference. I guess I haven't been creating hyperlinks to the same drive, so
I have never had a problem with it.

Sorry about the misinformation.

--
Regards,
Tom Ogilvy

Darren Hill <star...@btinternet.com> wrote in message

news:bqo6h5$t1$1...@sparta.btinternet.com...

[PaulPW]

> 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

This is *GOLD*! I'm so glad I stumbled on this thread (my first visit
to this website and first thread I looked at!)

I have been struggling for ages with hyperlinks, which would not always
work since they were to different locations on a network, and excel (on
saving) would change the absolute references to relative, which would
not all be correct, if that makes sense. I ended up writing code to
re-paste the links back whenever the sheet was opened, but even that
didn't always work, seemingly because I had set up drive letters mapped
to different parts of the network?!
Anyway, the Hyperlink Base seemed to solve all that - wish I'd have
found it earlier on (like, in the Help pages!!)

Thanks for that one Dick :)

Regards, Paul

------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

[Dick Kusleika]

Glad to help Paul.

I have nothing against ExcelTip.com, but if you want to connect directly to
Microsoft's news server, you can read about it here

http://www.cpearson.com/excel/DirectConnect.htm

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"PaulPW" <PaulPW...@excelforum-nospam.com> wrote in message
news:PaulPW...@excelforum-nospam.com...

[Darren Hill]

I'm using the Selection Change event. This way I click on the row containing
an image's data, and it changes to that image.

"Tom Ogilvy" <twog...@msn.com> wrote in message

news:vsvblui...@news.supernews.com...

[Darren Hill]

Oh, sad day. I must somehow accept that the mighty Tom Ogilvy is fallible ;)

--

Darren
"Tom Ogilvy" <twog...@msn.com> wrote in message

news:vsvorot...@news.supernews.com...

[Darren Hill]

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" <di...@paragonUNMUNGEconstructioninc.com> wrote in message
news:%23PR3oNr...@tk2msftngp13.phx.gbl...

[Dick Kusleika]

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" <star...@btinternet.com> wrote in message
news:bqqhig$6cg$2...@titan.btinternet.com...

[Darren Hill]

Thanks, Dick - that works perfectly.
(And yes, I did have some columns of irregular width.)

--
Darren
"Dick Kusleika" <di...@paragonUNMUNGEconstructioninc.com> wrote in message

news:OuKY4P2...@TK2MSFTNGP09.phx.gbl...