Photo Links in Excel

Good to know for a rainy day. Thanks for the tip.

is it possiblefor the photo to automatically enlarge when I click on the photo, or do I need to expand each photo individually from the corners of the image

It'll all come in handy one day, thanks.

Can also do it by resizing the image in situ, using macros/VBA, so that clicking will toggle between thumbnail and full size view, but it gets pretty messy.

This is great, you may need to lock or anchor the picture in an area within an excel sheet.

To lock a picture within a specific area of an Excel sheet, so it stays put when you insert rows or columns, you need to adjust its properties. Select the picture, go to "Format Picture" (or "Size & Properties"), and under the "Properties" section, choose "Move and size with cells" and check the "Locked" box, according to Microsoft Community Hub. This ensures the picture stays anchored to its cell and moves/resizes with it, says HowToExcel.org. Here's a step-by-step guide:

1. Select the picture: Click on the picture you want to lock.
2. Open Format Picture: Right-click the picture and select "Format Picture" (or "Size and Properties").
3. Go to Properties: In the Format Picture pane, find the "Properties" section.
4. Choose the right option: Select "Move and size with cells" and make sure the "Locked" box is checked, according to HowToExcel.org.
5. Close the pane: Close the "Format Picture" panel.

Now, when you insert or delete rows or columns, the picture should stay locked within its cell, and resize accordingly with cell adjustments.

Thanks for great advice John D

possible yes, but will make huge files... which creates a limit to the number of records you can deal with on an 'average' computer.

JPG is an algorithm where you can decrease file size by repeatedly saving the file. (down to a few kb.. but you also degrade the quality)

I might suggest that you process all your image files to thumbnail size.. and that you then have a few dozen per sheet in your excel file. You can have separate sheets saved in a folder (with images) and have some prev and next buttons that load them as needed. (while giving the appearance of being a larger app.)

next is the enlargement feature. I suggest that you use the vba selection change (when you click a different cell) and then you can show the larger image in a userform. the userform can pick up the image name in the background from the selected cell... but all of this is not really any different than using the hyperlink to show local images.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Userform1.Show
End Sub

beyond this I think you might consider changing to a more dedicated image serving database system with more power over image and interface details.

Chris

Good idea to reduce the size of the images - if I understand correctly, the app is only for identification of the items, so "picture quality" would be a huge waste of space.

I used MS Office Picture Manager to squish this picture of the innards of Wells Cathedral Clock (built c.1392, now in the Science Museum, London - still running):

The original "off-the-camera" image was 3072 x 2304 pixels, and took 3.22MB on disk.

Options for compression and resulting image sizes were:

• Document: 1024 x 768, 137KB on disk
  
• Web Pages: 448 x 336, 36KB on disk
• E-mail Messages: 160 x 120, 8.75KB on disk

I uploaded the doc. version, but needn't have bothered, as CR4 re-compressed it to 400 x 300 anyway.

This is the 160 x 120 (thumbnail) image:

As wsjackman pointed out, you can often increase the clarity of a particular item (given the same on-screen image size) by cropping.

there is one more source for a well rendered image reduction... and that is the CR4 posting dialog.

I can load huge images in here, and they all come out the same size, and well rendered.

For thumbnails I use my windows explorer in thumbnail view, and use printscreen, paste to mspaint, and then crop to the image size.. you may not get the exact pixel size, but very close. then save as jpg.

It is also quite easy to make your own file manager in visual basic. you can then perform all the requested sizing functions, and save coding the 'table of contents' which is covered by dir and file list box... so just name your files appropriately.

I've also created a vb tool that allows users to open a directory of pdf files (using the pdf activeX control), and then to rename files based on what you see as the pdf content. (I had a bunch to rename) This tool was in preparation for a pdf document server I was creating.

As said. Click on insert, ( from the top bar ) select picture, from file, ( choose picture from the place you saved it ) enter, right click on picture, select resize, check the box "keep ratio" and change one dimension.

Repeat as necessary.

Teach the reader to right click on picture and resize as above OR double click on picture and drag the corner green square to the size required. Doing this doesn't keep the ratio.

easy eh?

INSERT/OBJECT/FROM FILE check as icon and hit browse to find your file.

Click on your file. an icon should show in the cell you selected to put your file.

That should do it.

V/R

you can also insert pictures in comments

insert a comment in a cell

click on the edge of the comment to select the comment box

right click and select format comment

select the colors and lines tab

click on the colors section and select fill options

select pictures and browse to find the picture you want for that cell

before you do this i would edit all of the pictures to the minimum size that still gives you the clarity you need at the max size you want the picture to be seen at. cropping and compressing can do wonders for the size of a document and the clarity of what you want to show.

The nomenclature is a bit unclear & confusing for spreadsheets, the different pages within a spreadsheet are worksheets

you can store the pictures on one WS [worksheet] & link to them from the WS with the inventory

I like the 1st WS to be the table of contents.

Some time ago I had made some cards up using open office. The cards had a description of a mineral specimen with a picture below. The card is about the size of 2 business cards folded over, that way when a customer was buying a particular item there was less chance of a mistake by the cashier.

I was just thinking about how to automate the process

the description is one cell, the picture another. The time consuming part is getting the already cropped picture the the right size & orientation in the space alloted. I can see something like this being handy for a parts room....

I have a copy of MS Office 03 if needs be, I'm not sure this isn't more of an access project than excel

with access I can have a bit more control, by using a form to input the data, so the format of a WS doesn't get broken accidentally

You're right about the worksheet/spreadsheet confusion - I've been using the Excel nomenclature so long now that I'd forgotten my initial problems.

Think the Access/Excel argument only really comes in if you have more that "a certain number" of records to fret about (or if you need a "proper" relational database). I'd say that the Excel method would be fine up to a few hundred records, but could get unwieldy after that.

You can use cunning VBA stuff in Excel to keep eveything hidden, and only have access (small 'a') to the lists & images via a form. Sometimes it's not obvious whether an app is Excel or Access based.

Using Access a bit made me learn a tiny bit about databases, which in turn made it easier to use Excel.

the main things are to have only one data point per cell & making sure you have a unique identifier

so where would I find info about how to use a form with excel?

the hiding stuff is not too hard

nor is using one WS for a report, I'm just try to make the inputing of the data as braindead easy as possible.

later I'll share a basic project manager spread I cooked up on Google docs. It's nice as the customer can see everything, without being able to make any changes

the first stop to learn about that is in Excel.

press Alt-F11 to get to the macro editor...

in the project tree, right click, and select 'insert user form'

you can add controls from the toolbox. If you don't see what you like, you can add more controls by adding them to the toolbox (right click) If I want the filelistbox, etc, I can get them this way.

if you double click on teh control, you then get switched over to the 'event handler' for that control. the list of events is shown on the left side of the API.

once you get into writing the event code, you can put your cursor on any keyword, and press f1 to get help. there is also 'in context' help when using built in functions.

if you want more info.. let me know..

if I understood your question correctly.

Chris

Not so much then, access is far easier to use than that

I don't need totally custom forms... I'm sure there must be templates somewhere :D

Have a look at the cloud version which has even less of a learning curve

Link

you can't do all the things VBA will do, but I also won't spend the next few weeks learning how to get this basic thing going

feel free to enter data on the form or change things, this is just a copy, I've wiped the data off of

I can set permissions from completely private to completely open...

click form>go to live form to enter data

All the data goes to the data WS

the other WS's are really just queries of the data WS

interesting... and is there any scripting available for that?

actually what I need is some more complex (and probably customized) scheduling software for jobs at my small factory. (I've been slowing writing and building an automated spreadsheet system... but it probably needs a backend database too.)

chris

I'm not sure what you mean by that

there are most of the excel kind of formulas available

I just added sheet21 do with it what you may...

or describe in a bit more detail what you would like to do?

this is part of what I've built so far. this is a combined scheduling and tracking sheet. there are two other sheets that go with this.. a job list and another tracker that gets used to print work center specific schedule dates.

in this sheet, the green cells are filled green when complete (center section) or are grey when not-applicable. overall job completion is the 2 green columns on the left.

Oh ya, each job has 3 rows assiged to it.. production complete date, delivery date, and installation date. red is production complete date, orange is delivery date, and blue is installation days. light yellow is days in production. olive green is the bottleneck of the spraybooth (finishing) for which we have little data to estimate with.

(ps, we are a kitchen manufacturing and installation shop)

so it's an everything tracking sheet, which is a graph at the same time

the other 2 sheets being derived from this one

no real computation, besides some simple math & pulling some data around

the changing colors is a nice touch

I just fired up Access on my XP virtual box

I don't have any of my old projects so I'll to see what my learning curve is.

I'm remembering my biggest issue was getting reports in the very specific format I needed to fax to the county, I'll see what happens when I get to the thumbnails & have to get through the VBA part :D

Thanks all. I didn't realise so many options were available. Will try all out and see which one works for me best.

Cheers

One thing I have done in the past is to insert a comment in the cell , then paste the photo in to the comment window. Then when you hover over the cell, it opens the picture sort of automatically. This is how its done:

1. Insert comment in the cell you want

2. Open the comment for editing

3. Click on the border of the comment

4. Open the Format comment title

5. Open the colors and lines tab

6. Where it shows a color in a rectangular window, click the arrow to drop it down like you are going to select a color

7. At the bottom, you will see 'fill effects", open that.

8. Go to the picture tab and click on the select picture button

9. Then select okay.

So now the cell will show the little comment triangle in the cell, and when you hover over the cell, the picture will appear, and disapear when you hover away from the cell.

You can also do as you say, store you pictures on another sheet and note the approximate location. Then in the cell, creat a hyperlink. When it opens the hyperlink window, tell it you want to link in another page in this document. Then select the page with the photos from the list, and type the cell location in the window. This work sgood, but you may have to adjust the cell location to get the picture in the page the way you want it to look. Then you can also put somewhere nearby, "go back to main page" and do a hyperlink back to where you came from.