Need a macro to show multiple images on a sheet based on the name of the image file

Hello friends, have created a macro that fetches images based on the value of a cell.
If you go to sheet "Search Designs" and click on "Search Design" button which is on cell B2, it displays the image based on the name that appears in cell D2.
Note i enter my image name in cell D3. The image gets displayed in the big box ie cell B4.Now when i enter another name in cell D3, it deletes my older image and replaces it with the new image. So far i am happy with the result. I don't want the older images to be overlapped by new images.

Now if we click the Search Design button in cell H2, it displays the relevant image in Box2 which refers to cell H4, but what happens is that the image which was displayed in cell B4, gets deleted.

Similarly the same thing happens when i click on Search Design button in cell N2, where my previous image in cell H4 gets deleted.

Now ideally i would want all the 8 big boxes should display the respective images referring to the names connected to their respective cell, for eg B4 will display image based on value in D2, H4 will be based on value of J2, and so on and so forth.

I have written the macro for 1,2,3 boxes.

Any solution on the above problem would be of great help

Regards

Nirmall

Hi Nirmall,

Very interesting question. The key to deleting the proper file is to have a naming convention and assign names to the shapes (pictures) as they are created.

For example (working code excerpt taken out of context):

Please Login or Register  to view this content.

See the attached file which uses one routine for all 8 searches. I also added the following capabilities which can be disabled:
a. 'Left Click' a picture to Enlarge the picture or restore an Enlarged picture to Normal size.
b. 'Added a caption at the bottom of each picture containing descriptive information.
c. Added a small YELLOW rectangle in the caption that will remove the picture wen 'Left Clicked'.

I also added a couple of utility routines in module ModUtilities that will:
a. Get the color of the active cell - useful for obtaining 'Color numbers'.
b. Display information about each Shape on the Sheet.

I tested the software using Excel 2003 and 32 bit Excel 2010.
In Excel 2003, I had problems with the Excel Window expanding beyond it's usual maximum size.
Reset the window size with command keys:
a. Press Alt+Spacebar. This displays the Control menu (even if you cannot see it).
b. Press the letter R; the Excel window is restored to its “in between” size.
c. Press Alt+Spacebar to again display the Control menu.
d. Press the letter X; the Excel window is maximized.

In Excel 2010, I had problems with VBA putting the pictures in the proper place. I corrected the problem by repeating the code that assigned picture Left and Top coordinates.

The 'Merge Areas' for Items 1, 5, 8 seemed to be one column too wide, and I reduced their size.

The following excerpt from the top of ordinary code module ModSearchDesign contains items that can be changed to modify the look and feel of the software:

Please Login or Register  to view this content.

How the software works:
a. I renamed your CommandButtons to ButtonSearch1' thru 'ButtonSearch8' and all were assigned to Macro SearchDesign().
b. Macro SearchDesign() identifies which CommandButton was pressed using Application.Caller. From the CommandButton name obtained, the Window Number 1 thru 8 is identified.
c. Appropriate data associated with the specific Window is obtained.
d. The picture (if available) is inserted in the appropriate Window. A caption containing data associated with the picture, and a small yellow rectangle shape are placed on the picture and all are grouped together.

It is a best practice to declare all variables. If you misspell a variable in your code, VBA will silently assume it is a Variant variable and go on executing with no clue to you that you have a bug. Go to the VBA development window, click Tools, Options, and check "Require Variable Declaration." This will insert the following line at the top of all new modules:

Please Login or Register  to view this content.

This option requires all variables to be declared and will give a compiler error for undeclared variables.

Lewis

Hi Lewis,

first of all a million thanks for taking time out to answer my thread.

As i can see you have put in a lot of efforts in the coding.

Will test it at my end and revert back

Regards

Nirmall

One more thing Lewis, can u tell me where are you in the picture???

Your welcome. It was a lot of fun to work on.

I'm the 2nd youngest in the picture, and 2nd from the left on the bottom row.

Top row ages: 76, 81
Bottom row ages: 76, 66, 76, 57, 82

Pretty cool, you are a stunner, also i admit that i have jus taken your 2 sheets and 2 modules and copy pasted in my worksheet, but every time i search a design, i get the desired result, but in the backend your excel opens. I deleted your excel and did the search from my sheet it then said file could not be found.

Can u give a solution for this??

You have come across the problem of unintended links in Workbook1 to Workbook2. This is caused by the 'Search Design' CommandButtons being linked to my Workbook.

Try the following to fix the problem:
a. Open your file.
b. Right Click on the 'Search Design' CommandButton associated with Window 1.
c. Assign Macro 'SearchDesign' to the CommandButton. This will remove the association with my file.
d. Repeat steps b and c for Windows 2 thru 8.
e. Save your file.

Lewis