Is there a way to Pass a image between sheets using =INDEX

i don't believe so. i remember reading somewhere, about having a pic in a cell from a formula.. anyway, don't think it's possible. you could probably simulate it, but not the way your trying.

Re: Is there a way to Pass a image between sheets using =IND

This will copy the selected shape picture from Sheet 2 and paste it on Sheet 1. It does not have any code to order the actual image on Sheet 1, but it does get it there. I used a Select.Item variable to identify the selected image.

Sub myPicCopyTo()
Dim myItem As String
'
myItem = Selection.Index()

Sheets("Sheet2").Select
ActiveSheet.Shapes(myItem).Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Paste
Range("A1").Select
Application.CommandBars("Picture").Visible = False

End Sub

Joe that's pretty good! i just can't wait until we can do that in a formula :wink:

Re: Is there a way to Pass a image between sheets using =IND

Hi Zack I dont think you will ever be bale to do that with a function alone. Functions can only return a value and cannot affect the environment so importing an object certainly fits into that category.

With a bit of lateral thinking you could probably use a worksheet event to look for particular values then copy over the picture.

hiya Parry,

ya, it was mostly a bit of wishful thinking that MS might incorporate some functions which could recognize objects, shapes, embedded items, etc., and be able to call them direclty. boy that would be nice!

thanks for the reply parry!

Re: Is there a way to Pass a image between sheets using =IND

No. You both are missing information!
You can use Functions to access Objects!
And, you can do it now from Sheet Functions!
You must define a "User Defined Function" [UDF] to do it though!
Your UDF will work just like any Excel sheet function, only you determin how it works!

Below is a pair of "Sub's" these "Add" a standard shape and re-name that shape to avoid the index problems [This is important]. The other Sub is not used but is included here so you can see hoe to delete your re-named shape bu code!

Next you create your user defined function. Here I simply use the sheet function to pass the name of the shape object I want to work with to VBA code!

The worksheet syntax is =SShape("YourShapesNameHere")
Note: the actual sheet function will be entered as:
=SShape("4PointStar1")

Which will select the shape object you named-called from your UDF sheet function where ever it is!

Hence you can work with objects from functions!

Option Explicit
'This is the UDF's passed re-name for the shape object!
Public fShapeName As String

Sub myAdd4PointStar1()
'Run from Standard Module#.

Application.CommandBars("AutoShapes").Visible = False

With ActiveSheet.Shapes.AddShape(msoShape4pointStar, _
321.75, 201#, 102#, 86.25)
.Name = "4PointStar1"
End With

Range("A1").Select

End Sub
Sub myRemove4PointStar1()
'Run from Standard Module#.

Application.CommandBars("AutoShapes").Visible = False

ActiveSheet.Shapes("4PointStar1").Delete
'ActiveSheet.Shapes("msoShape4pointStar").Select 'this is renamed an add.
Range("A1").Select

End Sub

Function SShape(itsName)
'Custom User Defined Function, UDF.
'You can use this like any SheetFunction.
'Selects a shape.
'Store in Standard Module, like: Module1.

'WorkSheet UDF function syntax, =SShape("YourShapesNameHere")
'Note: Your shapes name in SShape, [YourShapesNameHere] must be in quotes!

SShape = itsName
fShapeName = SShape
ActiveSheet.Shapes(fShapeName).Select
End Function

Re: Is there a way to Pass a image between sheets using =IND

Hi Joe, thats very interesting - I didnt know you could do that. However once you select it can you actually do anything to it with a function alone?

Re: Is there a way to Pass a image between sheets using =IND

This uses the UDF sheet function to toggle the object color on and off.

Function SShape(itsName)
'Custom User Defined Function, UDF.
'You can use this like any SheetFunction.
'Selects a shape and toggles its fill color.
'Store in Standard Module, like: Module1.

'WorkSheet UDF function syntax, =SShape("YourShapesNameHere")
'Note: Your shapes name in SShape, [YourShapesNameHere] must be in quotes!

SShape = itsName
fShapeName = SShape
ActiveSheet.Shapes(fShapeName).Select
If Selection.ShapeRange.Fill.ForeColor.SchemeColor = 42 Then
Selection.ShapeRange.Fill.Visible = msoFalse
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 65
Else
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 42
End If

MsgBox "We will now re-set the color!"

End Function

wow, thanks for the fast response!