Form: Load Picture from Worksheet instead of a folder structure

Hello All,

I have done extensive searching all over the internet for what I thought would be a simple answer, but this one is proving to elude me.

This is to load a picture onto an image in a form.

How do I change this line here...(which works)

Please Login or Register  to view this content.

to instead, say this here...

Please Login or Register  to view this content.

I need to load pictures from my macros startup workbook rather than a folder structure because otherwise, this code won't work on another computer.


UPDATE: I tried this and it didn't work..

Please Login or Register  to view this content.

Thanks much

Hello duugg,

Things are not always what they seem as is the case here. The Image control expects the picture to be in a format it understands. The LoadPicture method converts picture files stored on disk into the picture format the Image control understands.

The problem with loading a picture from a workbook is the picture is stored within the workbook, not as a separate file on the disk. To load a picture from a workbook into a Image control requires copying the picture to clipboard and then converting the picture on the clipboard into the picture format that the Image control understands.

Your profile says your are using Excel 2003. If you plan to use this on other platform like 2007 or 2010 then let me know. I have code to do this but made need some modification if it is used on later versions of Windows.

A real 'workaround':

Please Login or Register  to view this content.

Use F8 in the VBEditor to specify exactly in which line the error occurs..
The file should be saved once otherwise thisworkbook.path errors out.

Please Login or Register  to view this content.

If you add the picture to the sheet using an ActiveX Image control, you can simply assign the picture property of that control to the picture property of the image control on the form.

PS Alternatively, with a standard picture, you can use Stephen Bullen's PastePicture code:

Please Login or Register  to view this content.

and then just use in the userform:

Please Login or Register  to view this content.

I don't see what PowerPoint has to do with anything. The code does not care what format picture you inserted on the sheet.

Where exactly are you running the code from? A userform in the personal macro workbook? If so, and assuming 2 image controls, the code in the form will be:

Please Login or Register  to view this content.

romperstomper,

Ok, there's what I did so far...

I tried this code here...

Please Login or Register  to view this content.

and I got this error here...

Compile error:

Sub or Function not defined

My form name is "UF_Status". Would that affect this error maybe?


UPDATE:

Am I still supposed to paste in Stephen Bullen's Paste Picture code as well? If so, I tried that and got an error saying this...

Compile error:
"Only comments may appear after End Sub, End Function of End Property"

Any thoughts?



Also, am I supposed to put some code in the UF_Status form? It currently looks like this...

Please Login or Register  to view this content.

Thanks

Did you add the first bit of code I posted to a new module in the same workbook as the code you just posted?

Then I guess you did not add it to a new module?

To illustrate my code suggestion, cfr. the attachment

Not sure what you mean by "each code section"? In my original 'PS' post there are two code blocks. The first one is a complete module. The second was an example of how to call the first, but that was replaced by the specific code for your workbook/sheet/picture combinations.

I also implemented RS's suggestion to use Active X controls in the sheet. (by far the simplest method)

Wow guys, I totally appreciate you sticking with me on this as long as you have been able to.

It's hard for you guys to understand my confusion because you both obviously are very well versed with VBA and I am totally jealous! lol .

I'm going to explain in laymens terms here my thoughts and dilemnas and maybe after that, you will be able to give this another attempt if you so wish to.

Okay, so, I am working with 2 workbooks here.

Workbook 1 - Named "Macros", located in my startup folder and is where all my macros are stored in about 80 modules or so.

Workbook 2 - Named "DB Only", saved as an XLSM file so that I can run macros on it.



In my Macro workbook...

I have a form named "UF_Status", and within that form, I have "Image1" and "Image2" which are blank and have no pictures/images loaded into them.

I have a worksheet named "sheet1" (decided it should be in my "Macros" workbook because I want to save with images with my macro workbook and NOT the "DB Only" workbook.

On the worksheet named "sheet1", I have 2 images named "picture 1" and "picture 2".


In my DB Only workbook...

The only worksheet I have in this workbook is called "DB"


Here's what I want...
(keep in mind that I can already do this perfectly by loading the images from a folder location, which is really not what I want)...


While on the DB Only workbook...

1. I want to press a button on my toolbar assigned to run a macro

2. I then want 2 things to take place
a) I want to run this code below...

(note that my code is obviously much much longer than what is below, I simply chose this code because it's short and simple...)

Please Login or Register  to view this content.

B) WHILE the above code is running..I would like "Picture 1" to show up and then go away, then have "Picture 2" show up and then go away.

And that's it!


Now, from what I gathering here, there seems to be 6 areas that can modified with either code, names or properties..

Area 1. The "sheet1" worksheet of the "Macros" workbook
Area 2. The "sheet1" code of the "Macros" workbook
Area 3. The code area of the form named "UF_Status" in the "Macros" workbook which looks like this...

Please Login or Register  to view this content.

Area 4. The properties area of the form named "UF_Status" in the "Macros" workbook.
Area 5. Module "X" - That "long" code that romperstomer provided" that begins with this..

Please Login or Register  to view this content.

Area 6. Module "Y" - That "short" code which is here..

Please Login or Register  to view this content.

So, having said that here are my questions...

Question 1 - AREA 2 - Do I need to put anything at all in AREA 2, the code of the sheet1 WB?

Question 2- AREA 3 - Code area of the form - Do I need to put anything in this area?

Question 3 - AREA 4 - Properties area of the form - Do I need to change any values in this area, name, pre-load the images, etc?

Question 4 - AREA 5 - Module "X" - Other than copying that long code, is there anything at all i need to modify? For example, what should the module be named? I assume that there's no "Sub" and "End Sub" anywhere in this module?

Question 5 - AREA 6 - The "Short" code - Again, other than copying the short code to a module, is there anything I need to modify? What should this module be named?


Having stated the above, I just realized that there should be NO code at all in the "DB Only" workbook. I want all the pictures and code to reside in the "Macros" workbook so that this awesome macro will work on any workbook, using any person's computer (providing I put the "Macros" workbook in their startup of course.)


I'm basically looking for this at this point...

1. Put THIS code in "AREA 1"
2. Put THIS code in "AREA 2"
3. Name THIS module "X"
etc, etc, etc.


Wow, I am sorry to be so technical with my questions here guys. FYI - I have given you both as many reputation points as possible I have learned sooo much from this forum in the past year or so. Forms and even functions to a degree are brand new to me and this is proving to be a real challenge. Do whatever you can and even if you can't do anymore, thanks so much for getting me to this point!

You say you already have this working from folders so:
Area5 No change.
Area6 - this code basically replaces the code you are using to load the pictures from a folder. It is not a complete sub - it is just a couple of code lines that you put in your existing sub.
That's it.

Here is a demo file in case it makes things clearer.

I'm still not seeing what the disconnect is. You say (but still haven't shown) that you have code to load the pictures from a file. We have given you samples showing how to load the picture from a picture on a sheet. All you need to do is replace one with the other. It does not have to run from clicking anything - that is simply a demonstration.

Please Login or Register  to view this content.

HEY NOW!

HOW DO YOU SPELL SUCCESS!!!

WWwwwooowwwWW

Unbelievable!

My only regret is not providing the whole code earlier!


Thank you thank you thank you!

Puuurrrfection!!!!!

P.S. I tried to mark the thread "solved" but was unable to.

In the meanwhile I was working on a 'switching' variant.
cfr. the attachment

That code only works if you also have Stephen's code too.