Help for how to pop-up images &/or use text-to-speech on distant c

[Chris Gregory]

Hi All,

The set-up: as part of my research, I have a datasheet in Excel for
collecting morphological measurements, (a made-up example described below):

A B C D E F
1 Bob Mary Jane Chuck Etc.
2 Big Toe Width
3 Big Toe Length
4 Height
5 Arm Span
6 Etc.

My sheet has ~100 measurements (column A) and the number of individuals to
be measured per worksheet will vary. I am lucky enough to have a measuring
tool which inputs directly to the computer & enters data a click of a button.
Thus, if I started measuring Bob at cell B2, each click would send the cursor
down one cell (to B3, then B4, etc.). Very handy!

The question(s): While in each cell, I would like the option of having one
or two things happen. First, (and most importantly), for people who know
exactly how to take the measurements, but can't keep track of the order they
are supposed to measure & don't want to keep looking up at the computer
screen, I would like the option to utilize the text-to-speech functionality
of Excel (as a last resort, perhaps have an audio clip play). Thus, when the
cursor moved to any cell in row 2, Excel could speak the text in cell A2
("big toe width"), when the cursor jumps to any cell in row 3, Excel could
speak the text in A3 ("big toe length"), etc. Second, since a variety of
people may take these measurements (not just me), I would like the option to
display a picture (showing exactly where to take the measurements). So, when
the cursor moved to any cell in row 2, a picture could display showing
exactly how to measure big toe width (row 3 would have a picture of how to
measure big tow length, and so on).

I have read about changing a cell's Comment to display an image, but given
that I have 100 measurements & will end up with 1000 individuals, I'd rather
not change 100,000 cell Comments. Perhaps just some code of with a dynamic
range...? Finally, my automatic measurement input tool needs Excel to be
active for data to come across. Thus, not sure of the potential solutions on
how the image might get called up if not in Excel (i.e. via Microsoft Paint
or Internet Explorer), but no matter which software, the Excel spreadsheet
must remain/end up as the active window. Hopefully all of this has made
sense.

I am running Excel/Office 2003 on Windows XP. Again, the first half of the
question (text-to-speech on a distant cell) is the most important. Thanks for
any ideas, suggestions, and code you might have,

Chris

 

[JNW]

try what is below to get you started. It'll take a lot of entry if you have
100 measurements but should work well. For the code below, it looks for the
pictures and sounds in the same file that the workbook is located in. You
can change this if you like. I didn't test the sounds but the pictures work.
You'll obviously need to record all of the sound files yourself and save
them as wav files. The pictures can be bmp, jpg, gif.

Some things I couldn't figure out that you might like to change:
-I've only referenced column B, you'll want to find a way to have the sound
play and picture show when ever a cell in a particular row is selected
-Look on the forum for a way to place the userform next to the cell that is
highlighted.

THIS CODE GOES IN THE WORKSHEET CODE (for as many worksheets as you need it
for):
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
PlaySounds
End Sub


THIS CODE GOES IN A REGULAR MODULE:
Option Explicit
Private Declare Function PlaySound Lib "winmm.dll" _
Alias "PlaySoundA" (ByVal lpszName As String, _
ByVal hModule As Long, ByVal dwFlags As Long) As Long

Const SND_SYNC = &H0
Const SND_ASYNC = &H1
Const SND_FILENAME = &H20000

Sub PlaySounds()
'Dim Target As Range
Dim ThisWbPic As String
Dim BigToeWidth_Snd As String, BigToeWidth_Pic As String
Dim BigToeLength_Snd As String, BigToeLength_Pic As String

'Target = ActiveCell
ThisWbPic = ThisWorkbook.Path & "\" & "Medical.jpg"
BigToeWidth_Snd = ThisWorkbook.Path & "\" & "BigToeWidth.wav"
BigToeWidth_Pic = ThisWorkbook.Path & "\" & "BigToeWidth.gif"
BigToeLength_Snd = ThisWorkbook.Path & "\" & "BigToeLength.wav"
BigToeLength_Pic = ThisWorkbook.Path & "\" & "BigToeLength.jpg"

If Not Application.CanPlaySounds Then
MsgBox "Sorry, sound is not supported on your system."
Exit Sub
End If

'Show Picture userform in modal mode
PictureForm.Show False

If ActiveCell.Address(0, 0) = "B2" Then
'plays sound
'Call PlaySound(BigToeWidth_Snd, 0&, SND_ASYNC Or SND_FILENAME)
'shows picture
PictureForm.Picture = LoadPicture(BigToeWidth_Pic)
ElseIf ActiveCell.Address(0, 0) = "B3" Then
'Call PlaySound(BigToeLength_Snd, 0&, SND_ASYNC Or SND_FILENAME)
PictureForm.Picture = LoadPicture(BigToeLength_Pic)
''etc...
Else:
PictureForm.Picture = LoadPicture(ThisWbPic)
End If
End Sub