'Paste Picture Link' equivalent for form image? 1

[VBAjedi]

Is it possible to display a live/linked view of a range of cells with an image control on an Excel form?

When you copy a range on a sheet, then use "Paste Picture Link" to create an image control where .Formula is set to that range, you end up with a live "window" into those cell's contents on that image control. And you can change the range the image is looking at with it's .Formula property:

    ActiveSheet.Shapes("Picture1").Select
    Selection.Formula = "$A$3:$A$6"

I want the same thing, but as a control on a form where I will be setting the range it's looking at via VBA... clear as mud? I tried adding an image control to the form, but there's apparently no .formula property.

VBAjedi

 

[PHV]

Why not using a listbox ?
ListBox1.RowSource = "$A$3:$A$6"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[GlennUK]

I believe that there is a spreadsheet object that can be added to forms ( Excel 2003 ), but haven't heard of anything like it for Excel 2007. Here's a thread that might have some useful info:

http://www.officekb.com/Uwe/Forum.aspx/ms-excel/114983/Embedding-spreadsheet-in-a-user-form

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[strongm]

>haven't heard of anything like it for Excel 2007

Works just the same for Excel 2007

 

[GlennUK]

Thanks strongm, useful to know. ( I still count myself a beginner in 2007 )

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[VBAjedi]

Glenn,

Thanks! I'm a bit jumpy about using the additional controls that are available because in my experience they break more easily (some user doesn't have them installed, they trigger a security prompt, etc).

That said, I played around with a spreadsheet control and a listbox control (thanks PHV!), and I have to say the advantages to be gained by using a spreadsheet control (seeing formatting on the sheet, etc) make it worth the risk. Have a star...

Thanks again all! Hope to post a copy of this generic tool I'm building at some point for evaluation/feedback.

VBAjedi

 

[strongm]

> additional controls

It isn't an additional control; it's provided by Excel. And since you're running Excel ...

 

[VBAjedi]

strongm,

Even though I'm using it within Excel, using that spreadsheet control causes a prompt when the workbook is opened to the effect of "This workbook contains a control that may be unsafe to use" (at least on my workstation, running XL2003).

I'll use it anyway, it just means I have to educate my users to ignore that prompt, which is obviously less than ideal.

VBAjedi