Picture to appear in Excel, conditionally

[m1eyp]

I have a revision question bank on Excel. Two of these questions (160 & 161) have accompanying pictures, that I want to appear if either question is randomly selected.

These images have filenames 160.jpg and 161.jpg respectively, and are in a folder on my Desktop called "FRT images". My randomly selected question number is in Cells(2,8). Here is my attempt (that doesn't work):

If Cells(2, 8) > 159 Then Qpic = "C:\WINDOWS\Des ktop\FRT images\" & Cells(2, 8)

...the idea being that if the randomly selected question (out of Q1 to Q161) is 160 or 161 (the only two currently with pictures), then in the image box called "Qpic" (set up from the control toolbar) will appear 160.jpg or 161.jpg as required. It doesn't work; the image box remains blank when these questions are selected.

I will also need to add in a further bit to select a blank image for the other questions, in order to blank out the pictures where no image is required. Any help?

[m1eyp]

I've done some more work and tried things a different way. I now have 9 questions in the question bank that have diagrams, these being questions 160 to 168, the pictures being stored as 160.jpg through 168.jpg in a folder on my Desktop called FRT images. I now also have a blank image 999.jpg.
Cells(2,8) [h2] is where the randomly generated question number is housed.

My code now is:

Qno = Cells(2, 8)
Select Case Qno
Case 160
qpicno = 160
Case 161
qpicno = 161
Case 162
qpicno = 162
Case 163
qpicno = 163
Case 164
qpicno = 164
Case 165
qpicno = 165
Case 166
qpicno = 166
Case 167
qpicno = 167
Case 168
qpicno = 168
Case Else
qpicno = 999
End Select
Qpic.Picture = "C:\WINDOWS\Des ktop\FRT images\" & qpicno

Qpic is the name of my image box where I want the diagrams to appear. My code still doesn't work. The error message is "Run-time error 242; Object required".

Any suggestions?

[AlanHill1965]

HI,

Just been nosing around and noticed this post.

The following code uses an image fram called Qpic and a button called start.

Private Sub Start_Click()
Dim FIFiles()
Dim PAFiles As String
Dim CurWorkBook As String
Dim CurFile As String
Dim FiCount As Single
FiCount = 0

PAFiles = Application.Act iveWorkbook.Ful lName 'gets your current path
CurWorkBook = Application.Act iveWorkbook.Nam e ' get your current work book name
PAFiles = Left(PAFiles, Len(PAFiles) - Len(CurWorkBook )) ' shortens to just the path
CurFile = Dir$(PAFiles & "\*.bmp", vbNormal) ' uses the path and add the picture type to look for
Do Until CurFile = "" 'performs a loop until there are no more file names
ReDim Preserve FIFiles(FiCount ) ' resizes the array without loosing any information
FIFiles(FiCount ) = CurFile ' populates the file names into an array
FiCount = FiCount + 1 ' adds 1 to the count
CurFile = Dir$ ' looks for the next file name
Loop ' end of conditional loop

RNDPICNum = Int(Rnd() * FiCount) ' pics a random item from the array

Me.Qpic.Picture = LoadPicture(FIF iles(RNDPICNum) ) ' this loads the picture into the image object

Range("a1").Sel ect ' selects the cell A1
ActiveCell.Valu e = RNDPICNum ' shows which item was selected


End Sub

You wil see the comments show what happens at each line. the part you have missed is the part after where the Qpic picture is replaced.

Me.Qpic.Picture = LoadPicture(FIF iles(RNDPICNum) )

here the object QPIC the command to LoadPicture after the '=' sign. This tells it to load a picture with a the file name ( inside the brackets ) into the image frame, there is no need to refresh.

The code above activates with the push of the button, selects all of the images that have the extension BMP and loads these into an array. It then randomly selects a picture and loads this into the image object.

The path part will use just the current path of the saved workbook, NOTE it has to be saved before running or the path will be empty. The path can be changed by adding any sudirectory onto it (path & "\mypics") then adding the file name, or you can replace it with the full path name. This has to be set before the directory is scanned using the DIR$ command.

Any further questions I'm sure either myself or others can come up with further ideas

Alan Hill
'Blue. Eyes and Soul'