Taking object (picture) from excel and bringing it to a form

[Palamedesheights]

I have a form where I am letting the user choose the picture. The picture is put on the form and then placed in the appropriate spot in excel.

Later if it is edited I would like the picture in excel to be brought back into the form.

I have learned how to rename the objects (below) so I know what it is but I have not learned how to bring this up into a form like I do a picture from a folder [Insert_Picture.Picture = LoadPicture(Picture1)].

I would greatly appreciate anyones help in this.


Sub Object_To_Form

Dim sShapes As Shape, lLoop As Long
Dim wsStart As Worksheet, WsNew As Worksheet
Set wsStart = ActiveSheet

ActiveSheet.Pictures.Insert("C:\Documents and Settings\user\desktop\folder\Default Picture.jpg").Select

For Each sShapes In wsStart.Shapes
lLoop = lLoop + 1
With sShapes
If InStr(.Name, "Picture") > 0 Then
.Name = "Part 1"
End If
End With
Next sShapes
End Sub

 

[Jerry Sullivan]

This thread might help you...
http://www.mrexcel.com/forum/showthread.php?t=516887

 

[Palamedesheights]

Thank you Jerry for the response. But I don't seem to be able to get it to work.

It doesn't like this line
.Image1.Visible = True
It is giving me "Method or data member not found.

I used:

For Each sShapes In wsStart.Shapes
lLoop = lLoop + 1
With sShapes
temp_count = temp_count + 1
temp_string = "Image" & temp_count
If InStr(.Name, "Picture") > 0 Then
.Name = temp_string
End If
End With
Next sShapes

To rename the objects.

I might sound stupid to ask what you mean by option button, I used a command button.

I am not sure how:

With UserForm1
If .OptionButton1.Value = True Then
.Image1.Visible = True
.Image2.Visible = False
.Image3.Visible = False

works. Is image1 the name of the object in excel or is image a property with this option button. For the command button I see picture as a property. I don't know how to tie the picture property to the objects on the excel spreadsheet.

Thanks,

Duane

 

[Jerry Sullivan]

Hi Duane,

I think I misread your Original Post. When you described letting the user choose a picture for a form, I thought you meant a UserForm that would allow the user to select an image from a menu of images.

On re-reading this thread, I believe you might be describing a single ActiveX Image object that is placed directly in the worksheet (with no UserForm).

It will probably be simpler to clarify and help with you with what you are trying to do than to try to explain how the instructions in the link might apply.

I have a form where I am letting the user choose the picture. The picture is put on the form and then placed in the appropriate spot in excel.

Please clarify as best you can how you are currently doing this.

Later if it is edited I would like the picture in excel to be brought back into the form.

Please clarify "if it is edited" ...do you mean the original source file is changed, or do you mean if the worksheet is edited?

 

[Palamedesheights]

I appreciate your help in this, I will try to simplify the code I have written so far so that I can post something that makes sense here.

Thought I would give a short outline of what I am trying to do.

The reason for this macro is to help with the formatting of the original document.

start macro
1) Type in instructions, if picture is needed import picture to form to preview

2) Create form with instructions and pictures provided.

If the form is to be modified:
1) Bring up instructions from excel and pictures that were provided during last session. Right now I have them linked to the original file, I would like to pull from excel and exchange with new picture if one is provided. I need to do this because the user will change.

2) Recreate form with new information provided.

I do not have all the formatting scripts created, I need to figure out how to refer to the original picture on excel first.

Thanks,

Duane

 

[Palamedesheights]

If the worksheet is modified.

 

[Jerry Sullivan]

...I will try to simplify the code I have written so far so that I can post something that makes sense here.

Duane, Are you still planning on posting your code? Sorry but I'm just not understanding what you're trying to do.