VB- Automatically Insert Picture

The following User defined function may help you meet your needs. It's a function I have modified from Damon Ostrander.
http://www.mrexcel.com/board2/viewtopic.php?t=104322&postdays=0&postorder=asc&start=0



To add this function, you can hit alt + F11 to bring up the VB editor
Insert Module
Paste the above code.
Close the VBA editor

Then put this function in the cell(s) you want the picture's upper left corner to align with.

The first argument is the complete file name and address of the picture.
The 2nd and third arguments are optional height and width dimensions in pixels. If you do not put anything in it will default to 200 pixels high by 300 pixels wide.

Example:
Entered In Cell D4
=ShowPicD("C://Picture Folder/"rose.jpg",300,100)

will put a 300 tall, by 100 pixel wide picture of a rose on your spreadsheet with the corner of the picture starting in Cell D4.


The UDF is really useful because you can use other functions within it. For example, instead of typing the file name and location directly into the function, you might use a reference to another cell that contains the name, which can be chosen from a drop down.

Damon wrote this function awhile ago, and I've found it imensley helpful for my seating charts, I hope you find it as helpful.

Thank you!

Hi

Thank you for the link. I had found it after posting my request but had some problems understanding it but finally worked it out.

I've added a few other things:
all references are now entered into the worksheet instead of opening VB all the time (I have a few storyboards and they are in different folders). VB finds them using Range("NAME").

Next step to perfecting my storyboard template: how to automatically set the column and row sizes to fit the images (every 2nd column in a range and every 4th row in the same range). I'm hoping this won't be to difficult and i can work it out without having to ask someone for help.

Thanks again.

I'm not really a VBA whiz, but I"m pretty sure there is a row.height property and a column.width property, or something along those lines.

Quickest way to figure out the syntax would be to use the macro recoder, then step into the code and look at it.

You could then use your picture dimensions to force the desired rows and columns to their respective heights and widths.

I don't fully understand it, but beware, I believe the row height and column width use different scales. (You can tell this when you look at the numbers as you manually adjust the width and height of a cell.)

You may need to figure out (or look up) a conversion factor from pixel size to height, and a conversion factor from pixel size to width.

Just some thoughts from an amateur.

Pictures, Pixels, Column Width and Row Height

Hi.

Thanks again. Yes, i see the pixel to column width & row height issues. I've been reading a lot and playing with it in the last 24hrs. I managed to work out:

a ColumnWidth set to "1" = one letter "o" character in the "Default Font"

Thus, in my computer and this file/template i've managed to work it out:

Default Font: 8pt Arial

ColumnWidth = Picture Width in pixels x 0.217741935483871
Row Height = Picture Height in pixels x 0.0648967551622419

Just in case, this is based on:
Screen resolution: 1400 x 1050
32bit colour


** NOTE: Excel's "normal" Row measure is correct to my picture's pixel height (this applies to setting default font to 8pt, 9pt and 10pt Arial). However, the row height in Row's pixel measure (in brackets when sliding the row height on the Row Headings) is not the same as my picture's pixel height. - i haven't worked out why Excel's pixel measure is different yet and what it really means - does anyone know?

Here are the main issues:
a) If you change your default font and/or defualt font size, the column size will shift accordingly. There is no set rule except to somehow "lock" the default font (in VB?)

b) If the file is handed to someone else to use in their computer it may still shift depending on their screen/monitor resolution setting (theory only from what i have read - i have not yet tested this)

c) Cross platform distribution of the file. Most people i work with are on Mac and i'm on PC. Given the usual default font on PC is Arial and on Macintosh is Helvetica, there could be yet another shift. Additionally, Ii haven't yet checked the size difference between the two fonts. If they are identical, perhaps VB code could run a check and defualt the font to the appropriate font..... to-date, this VB code concept is way beyond me.

My storyboards will primarily be printed as PDF and handed out so i don't think i need to worry about file sharing just yet (though it would be great to share the workload). But from my reading, it seems various computer screen resolutions plays a bit of havoc with resizing in online forms (?).

I digress and jumping way ahead of myself, I just need to layout the storyboards in realtively efficient time, make it simple and ideally, a continuing pleasant excercise.

Thanks also to Damon Ostrander and his lovely code.

Cheers.