ActiveX Control _Click doesn't work in second window

[owenf]

Excel 2007

Hello, I have a very large worksheet with many images that each perform an action when clicked. However, I need to be able to see the top right and the bottom left at the same time, with a lot of horizontal view of each area. Split, Freeze, etc., are not sufficient. But opening a second window let's me see what I need to (View ribbon-->New Window).

However, none of the click events work in the new window. In other words, clicking on an image runs its code as it should in the first window, but does nothing in the second. Here is how to recreate the problem in a simple worksheet.

1) In a new workbook, in the Developer ribbon, click Insert-->Image (ActiveX Control)
2) Drag to create a small image.
3) right-click the new image and select View Code.
4) Add a simple message box to the code. Here is an example:

Private Sub Image1_Click()
MsgBox ("You just clicked Image1")
End Sub
5) Return to the workbook. Turn off Design Mode by clicking Design Mode.
6) Click on the image: a message box appears. Click OK.
7) Click View to open the View ribbon. Click New Window. A new window opens.
8) Now click on the image in the new window. Nothing happens. I need both windows to run the activeX control's code when it is clicked in either window.

Thanks!
Owen

[snb]

Please don't ask too much: post a sample workbook !

[owenf]

Thanks for the instructions.

I Did not mean to ask too much- I thought the creation process would shed some light on the question, and it takes less than a minute. But I just posted a sample workbook. To reveal the problem, you'll still need to complete steps 7 and 8:

7) Click View to open the View ribbon. Click New Window. A new window opens.
8) Now click on the image in the new window. Nothing happens. I need both windows to run the activeX control's code when it is clicked in either window.

Thanks much,
Owen

[Jan Karel Pieterse]

That is really odd, never seen that one before and certainly looks liek a bug to me. I suggest to use the form controls, don't think they have this issue.

[owenf]

Thanks for the suggestion, but I don't think form controls will suffice. Browsing the menu, I don't see an image form control, and I dynamically load images into the controls; that is one of the essential functionalities of the workbook.

[owenf]

Is there anybody viewing that wouldn't mind testing this on a newer version than Excel 2007? I would be willing to upgrade if the problem has been fixed in newer versions.
Thanks!

[snb]

I did (so did JKP I suppose), it isn't.

[Jan Karel Pieterse]

You can use the picture object from the INsert tab, Insert Picture button. Then after insertion, change the name of the object on the sheet so you can address it by macro. To update the picture it needs to be replaced with a new one, like so:

Sub UpdatePicture()
Dim oP As Object
Dim oP1 As Object
Dim sName As String
'
sName = "Picture 1"
Set oP = ActiveSheet.Pictures(sName)
Set oP1 = ActiveSheet.Pictures.Insert("C:\Users\Administrator\Pictures\JKP 20120527.jpg")
oP1.Left = oP.Left
oP1.Top = oP.Top
oP.Delete
oP1.OnAction = "MyMacro"
oP1.Name = sName
End Sub

[snb]

I found no problems using:


Sub tst()
Sheet1.Shapes.AddPicture("G:\Mijn afbeeldingen\coffee.gif", True, True, 20, 30, 120, 40).OnAction = "sheet1.M_snb"
End Sub

Sub M_snb()
MsgBox "OK"
End Sub

[Jan Karel Pieterse]

snb: doesn't that add a new picture over and over again, stacking them all on top of each other? The OP needs to be able to update the picture with another one...

[Jacob Hilderbrand]

Same issue in 2013.

[snb]

@JkP

The OP might remove the existing picture if necessary.

[owenf]

Thanks Jan for the code! I'll give it a go. I only looked on the developer tab for objects to use- did not think to check the insert menu. I have over 800 images on the single spreadsheet, so I'll have to insert the images programatically, and I used Excel string functions to create code for each image before, and then pasted it into VBA, so I'll try that again.

Also thanks to those who checked to see if the bug existed in newer versions of Excel.

[Jan Karel Pieterse]

Wow, 800. I don't think you need separate lines of code for each of them though. Have you got a list of their filenames and locations somwhere?

[owenf]

Yes. Below is a brief description if you are interested, but to answer your question quickly first, there is a list of all possible image file names on another sheet. But which of those files each image displays is dynamic.

The spreadsheet keeps track of fabric used in the production of gymnastics-wear. Each image represents fabric used in a garment. Each garment can have up to 6 different fabrics in it, and there are slots for 150 different garments. = 6 * 150 = 900 images, so I was off. Each garment can come in different quantities of 10 different sizes.

Then, the part that I need to be able to see off to the left, is a Palette of available fabrics, which has 250 fabrics (so, there are 1050 images total). You can move fabrics around by clicking on their images. When you click a fabric, it stores the file name of the image file, and when you next click on a different image, that image loads the stored file name. So this is how you assign fabrics to garments. The spreadsheet also keeps track of how many of each size you are making of each garment, and how much fabric is being used. It all updates when you move fabrics around by clicking.

Believe it or not, it actually all works, and we have used it for a couple of years. I am working on improving it now. Each image is only 64x64 pixels, and my decent desktop computer it doesn't bog down too much.

-Owen

[Jan Karel Pieterse]

OK, thanks. Interesting application indeed!
We'll hear from you should you have follow up questions I'm sure :-)

[owenf]

Thanks for the help.

Before I received your suggestion of using pictures, I created a second workbook and put the Palette in that. I then referenced the other workbook, and used a few global variables.

It works OK, but when you switch workbooks, you have to click once to activate the workbook you just moved the cursor to.

Anyway, I have that as a working solution while I experiment with using the pictures instead of the activeX image controls.

[SamT]

Excel 2007


1) In a new workbook, in the Developer ribbon, click Insert-->Image (ActiveX Control)
2) Drag to create a small image.
7) Click View to open the View ribbon. Click New Window. A new window opens.


Run this from the macros menu and see how many shapes are in the workbook with two windows open.
[vba]Sub SamT()
Dim Shp As Shape
Dim WkBk As Workbook
For each WkBk in Workbooks
For Each Shp in WkBk.Shapes
MsgBox WkBk.Name & "," & Shp.Name
Next
Next
End Sub[/vba

I suspect that the second window's book and/or image have temporary name(s). If true, you should be able to write a permanent sub to call the "real" sub, or maybe move the "real" sub code into another and call it from both image_Click subs