I'm dashboarding with Excel and have everything finished except for programmatic placement of camera objects. Here's the gist:

My dashboard selects an image to copy with the camera tool based on the results of various lookup and choose functions applied to data pulled from an ODBC query embedded in a worksheet. The Camera object is placed on a display worksheet so that the dashboard is nothing more than a worksheet with a collection of camera objects referencing various data and images from other hidden worksheets.

My question is: how do I automagically determine the display position of the camera object based on data in another worksheet For instance, if Sheet3!A1 equals C14, then I want a particular camera image to show up at Sheet1!C14. The idea is kind of like an autogenerated map - if the data says a "red truck" is in "texas", then I want a camera object pointing at "red truck" to pop up in the location "texas".

I have everything working except that the resultant image has to be manually placed - how do I do this programmatically Please note that everything I know about programming came from the back of a Wheaties box.


Re: Automate the Excel Camera Tool

Jon Peltier

If you know the name of the camera object, you can use this syntax:

Worksheets("Sheet1").Shapes("Picture 1").Left = Worksheets("Sheet1").Range("C14").Left
Worksheets("Sheet1").Shapes("Picture 1").Top = Worksheets("Sheet1").Range("C14").Top

To use the value from Sheet3!A1:

Worksheets("Sheet1").Shapes("Picture 1").Left = Worksheets("Sheet1").Range(Worksheets("Sheet3").Range("A1").Value).Left

Of course, you'll use a more descriptive name than "Picture 1" for the camera object.

- Jon
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions

Re: Automate the Excel Camera Tool


Thanks, Jon - that was the piece I was missing. Now all of my "red trucks" are where the database says they should be.

Thanks again!