Auto Bingo

I was trying to create an autobingo game for my kids which randomized the 5 by 5 grid. To do that I did combined a Randbetween and VLOOKUP to a table on a separate sheet which had the name of the object (i.e. "cow") in column 2 and a picture in column 3. Well, the VLookup will return the name of the object but won't return the pictures. Is there a way to get those embedded objects to move into my autobingo form? I'm thinking this requires a Visual basic macro.

Any thoughts?

ChemistB

Thanks Noob!
That was very helpful in helping me set up the randomizing. I am still left with the dilemma of moving pictures into the Auto Bingo board. I had tried without VB by using a VLookup table with the 1st column containing the randomized number and the second column containing the name and picture of each object in each cell (i.e. a picture of a cow with cow written beneath it). VLookup only pulls back the text however. Can I grab the entire contents of the cell with VB and plop it down into my bingo board?

Thanks
ChemistB

Thanks Noob!

I will let you know if this works. : )

ChemistB

Noob, the link you sent me was a great help. I modified the program to import pictures into 125 different cells (5 bingo boards of 25 cells each) and it works pretty well. I have the code at the end of this post. Here are my problems;
1. The program actually takes the picture it locates and moves it into the appropriate cell. I'd prefer a copy of the picture be placed in the cell since the same picture might be used on multiple boards and the program takes the picture off board 1 and moves it to board 3, leaving board 1 empty. :- / Is there a command to copy oPic into the cells?

2. The picture is aligned in the cell Top Left. Is there a way to center it?

Thanks for all your help.

Private Sub Worksheet_Calculate()
Dim oPic As Picture
Dim Check As String 'I used Check to monitor the program while I was developing it. In this version, lines relating to "Check" are disabled.

For rwIndex = 1 To 25
For colIndex = 1 To 5
With Worksheets("Travel Bingo").Cells(rwIndex, colIndex)
'Check = .Text
For Each oPic In Me.Pictures

If oPic.Name = .Text Then
oPic.Visible = True
oPic.Top = .Top
oPic.Left = .Left
Exit For
End If
Next oPic
End With
'MsgBox ("Check = " & Check)
Next colIndex
Next rwIndex

End Sub

This should work ...

Please Login or Register  to view this content.

Thanks for your help. I must have coded something incorrectly. I got this error "Runtime error 438. Object doesn't support this property or method" on the oPic.Paste line. :-/

Here's my code;
Private Sub Worksheet_Calculate()
Dim oPic, oPic2 As Picture
Dim Check As String

For rwIndex = 1 To 25
For colIndex = 1 To 5
With Worksheets("Travel Bingo").Cells(rwIndex, colIndex)
'Check = .Text
For Each oPic In Me.Pictures

If oPic.Name = .Text Then
oPic.copy
oPic.Paste
Set oPic2 = Selection
oPic2.Top = 100
oPic2.Left = 100
Exit For
End If
Next oPic
End With
'MsgBox ("Check = " & Check)
Next colIndex
Next rwIndex

End Sub

You're right. Sorry about that. I coulda sworn I tested this before I posted it.

But, this does work, and it is basically the same idea (it feels cheap and dirty!).

Please Login or Register  to view this content.

This work when I dimensioned as Picture (as you did) and also when I dimensioned as Shape (which I tend to use).

Thanks MSP!

I will try this and get back to you.

ChemistB

Hmmm, we're closer. Now it goes through the cells and when if finds matches it prints copies (a major improvement) of the associated picture but it's priinting them on top of each other rather than in the proper cells.

Each cell is populated randomly (using excel functions) with text names of each bingo object (for example "RR Crossing). I named all the pictures correspondingly (thus there's no "picture 23", it's "RR Crossing"). It then tries to match the text in each box with the corresponding picture and paste it in there.

Here's my Code:
Private Sub Worksheet_Calculate()
Dim oPic, oPic2 As Picture
Dim Check As String

. For rwIndex = 1 To 25
. For colIndex = 1 To 5
. With Worksheets("Travel Bingo").Cells(rwIndex, colIndex)
.
. For Each oPic In Me.Pictures
.
. If oPic.Name = .Text Then
. oPic.copy
. ActiveSheet.Paste 'I tried ActiveCell.Paste but nope
. Set oPic2 = Selection
. oPic2.Top = 100
. oPic2.Left = 100
. Exit For
. End If
. Next oPic
. End With
.
. Next colIndex
.Next rwIndex
.
.End Sub

Your code looks good. And, from what you wrote, it is performing perfectly.

I think you need to change the .Top and .Left from constants to variables. Try this ...

Please Login or Register  to view this content.

Since you are inside the With statement, this is referring to the Top and Left of Cells(rwIndex, colIndex).

Pardon the pun but "Bingo!" : )

I did want to center the picture in each cell but if that's not possible, I'm happy with having them in the top left of each cell. Beats having them on top of each other. Now I'm going to rework my randomization matrix and I have a lot more clip art to collect for the finished product. Thanks for all your help (and if you do figure out a way for it to center the picture in each cell, let me know).

ChemistB

To center the picture in the cell you would compare
Worksheets("Travel Bingo").Cells(rwIndex, colIndex).Width
with
oPic2.Width
and adjust the oPic2.Left (vs. the Worksheets("Travel Bingo").Cells(rwIndex, colIndex).Left) accordingly.

Ditto for .Height.

Since you are inside

Please Login or Register  to view this content.

you can find the cell's left, top, width, and height by
.Left
.Top
.Width
.Height

I think you can take it from there, right?

Yes, everything is working perfectly now. Thanks for all your help. I learned a lot!

ChemistB

CAN ANYONE GIVE ME THE PROGRAM OF PICTURE BINGO GAME?
I teach English , so I need picture to help student to review vocabulary!!