Can anyone assist?
I have a database of at least 350 products, and would like to know how I can use a form button, when clicked it will randomly display a picture of one of the list of products. I want to use this method for a mystery prize draw.
Thanks to those who can assist.
MV22
Hello MarkV22,
Welcome to the Forum!
You could use this formula with your Form Button: =ROUNDUP(RAND()*1*350,0)
I presume you would have your images numbered accordingly, else you would have to create some sort of link.
Please consider:
Be polite. Thank those who have helped you. Then Click on the star icon in the lower left part of the contributor's post and add Reputation. Cleaning up when you're done. If you are satisfied with the help you have received, then Please do Mark your thread [SOLVED] .
Hi,
One way with the following macro attached to the button where Sheet1 contains your pictures, column A is empty - this will hold a list of your picture names. And if they're not changing once you have the list of picture names you can remove the For..Next loop that generates them. Sheet2 will receive the random picture.
Please Login or Register to view this content.
Last edited by Richard Buttrey; 08-22-2013 at 08:04 AM.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
Winon, thanks glad to be here.
I will definitely give your formula a go on my project. Thanks again.
Hi Richard
Appreciate your answer, but I have to be honest, I am fairly new to excel VBA script and all what it entails.
May I kindly ask how I have to setup my pictures? etc.
Thanks
Winon
Do I number my pictures as; 1, 2, etc. no other naming convention needed? Sorry I had to inform you firstly or in my greeting post somehow that I am fairly new to Excel and its magic. I was hoping for a more detailed step by step advice on how to get this to work.
Many thanks again.
Hello MarkV22,
Please see the attached sample workbook. It should give you a pretty good idea as how to set up your project.
Winon
A million thanks, indeed. Exactly what I needed.
Hi,Originally Posted by MarkV22
No, you don't need to worry about what your pictures are called. The macro takes care of that itself and will create its own list of names.
Upload your workbook and I'll add the macros for you. Tell me precisely what you want to do with the randomly selected picture. i.e. Do you want to copy it to somewhere else? If so where. When you next select a random picture what do you want to happen with the previous one. Remove it and replace with the latest one or what?
You are welcome.
Glad I could help. If you are satisfied with the solution I had given you, then please mark your Thread as Solved.
You may also Click on the Star to the far left, at the bottom of this Post, to Add Reputation.
Richard
The random selected picture should appear on the sheet, when the button form button is clicked the current picture disappears and is replaced with another.
I have no idea how to add the pictures to the sheet, and where.
Please check your post when I upload my workbook or if you have a sample I can work from would also suffice.
Thanks for your assistance.
Already hit the star as for your contribution to my problem. I have tested your workbook and noticed that the picture does not change, I assume that you have placed only one as for upload size to the forum.
It is exactly how I want my project to function and with a bit of tinkering I may just pull it off.
Last question: Do i import my pictures to the workbook?
Thanks
Hello MarkV22,
Maybe the attached revised WorkBook with notes, will clear things up a little bit for you.
Yes, you should import Pictures.
Thank you also for adding to my Reputation.
Winon
Appreciate your assistance. Thank you for the workbook. I will work with it tonight and return with comments.
Till tomorrow.
Good day Winon
The functionality of the workbook is great and it will definitely work, however it only works with the pictures you have in the workbook. With my imported pictures it shows none and reports an error. How do I link my pictures to the code?
Thanks
changed winons' solution so it does not require import of pictures into file itself
i have input a activeX img into the spreadsheetPlease Login or Register to view this content.
path where i stored my pictures is C:\temp\numbers
each picture is called
Number1.jpg
number2.jpg
etc
Last edited by humdingaling; 08-23-2013 at 03:35 AM.
If you are satisfied with the solution(s) provided, please mark your thread as Solved.
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
Good day 'humdingaling'
Thank you very much for your contribution to my project, every bit helps in this regard. I will work with it and return with comments.
Tks
Good day humdingaling
I have tried your code, but somehow my pictures do not show, instead a random number in the cell keeps showing up.
I would like the whole database of products to be embedded in the document as I travel a lot.
Many thanks
Good day humdingaling
I have tried your code, but somehow my pictures do not show, instead a random number in the cell keeps showing up.
I would like the whole database of products to be embedded in the document as I travel a lot.
Many thanks
Good day Winon
I have retried the picture issue and still my pictures would disappear and show a random number in the cell to where the picture should display above it.
Somehow I fail to connect the picture to a link. Is there by chance for a step by step help on this?
Many thanks.
Good day to all.
I want to know if it is possible to create a random picture generator that will simulate pages being flipped and finally stop on a picture selected. Similar to random picture generator that display a picture instantly. Imagine this result on a large screen projected in front of an audience as a prize shuffles very fast then slows down to finally stop.
Any help on this would be great.
Many thanks
Hello MarkV22,
In the last WorkBook I had sent you, do the following:
Click on the Show All Pics Button. Click on each Picture one by one, and delete it. Now place your Pictures on the same Sheet, and number them as Picture 1 through to whatever number of pictures you may have.
On Sheet 2, follow the sample data in Columns A and B, and enter your Data. In the VBA Code, change the =ROUNDUP(RAND()*1*8,0) to the number of Pictures you have entered. If it is say 100, then your =ROUNDUP(RAND()*1*8,0) formula would change to =ROUNDUP(RAND()*1*100,0)
That is all there is to it!
Good luck!
Hello Winon
Great thanks, do I number the pictures out of excel or place them on the sheet and number the cell above them?
Hi,
Also see my post #9
The macro I provided in the earlier post #3 will automatically use whatever the picture names happen to be. So just copy and paste the macros into your workbook and run the macro.
Great thanks to all for your kind assistance regarding my post request.
I will try it.
When you click on a picture, the "Name" appears in the block to the left of the formula bar. There you can cange the picture name as you wish.
Let me see if I can prepare another sample workbook for you with Richard Buttrey's fine Code.
You and Richard's contributions are indeed very welcome.
Can't thank you enough.
@ Richard Buttrey,
Hello Richard,
Could you please use the workbook in post #13, to demonstrate how you would apply your Code?
Thank you,
Kind regards.
Good day Richard and Winon
I have to report that all your efforts have paid off and my project is ready to use and working 100%.
The workbook now loaded with over 230 images that generates randomly when I click on a button.
I am still open for your last proposal Winon in uploading a detailed workbook like you have mentioned.
Just a question regarding the workbook. How on earth do I get to display the pictures on the sheet; for example in the middle of the sheet, bottom etc. It currently only displays pictures at the default "F1" cell.
We need more professionals like you out there.
Once again, thanks gentlemen.
Hello MarkV22,
That is great news, well done!
See the attached revised WorkBook, where I have moved F1 down to E8, and then Merged Cells E8 to H27.How on earth do I get to display the pictures on the sheet; for example in the middle of the sheet, bottom etc. It currently only displays pictures at the default "F1" cell.
Then I modified the Code accordingly, to show the Picture in that specific merged range.
If you like this approach, and if you would want to change it, just remember to adjust the Code accordingly.
Hi@ Richard Buttrey,
Hello Richard,
Could you please use the workbook in post #13, to demonstrate how you would apply your Code?
Thank you,
Kind regards.
Does the attached help.
I've not had time to establish why there are 10 pictures that are listed for sheet2 but you can only see 8. I tried playing around with pictures 1 & 8 which is why you see 111 & 811 listed twice but I can't see them and Sheet2.Shapes("Picture 1").Select can't select.
Regards
@ Richard Buttrey,
Hello Richard,
Sorry, I have tried for quite some time now, but Sheet3 of this Thread with your last Post will just not respond. I cannot access Page 3.
I have logged out, and exited the Net. Restarted my machine, connected to the Web, and EF, but no go.
My last post seems to have solved the issue above.
@ Richard Buttrey,
Hello Richard,
Thank you for your help.
I have sorted out the "mess" for you, and changed your code a little bit, and it seems to be running fine now.
Maybe MarkV22 can take it from here.Please Login or Register to view this content.
This is great, thank you... i have been loking for something like this for a while... let me give it a try and check if it works for my project..!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks