How to pause macro then select object?

[didj]

Hi Everyone,

I have found code to pause a macro and select a range of cells using an inputbox. This is NOT what I want to do. What I wish to do is pause the macro, allow the user to select an object (it will be a picture as an embedded object), then restart the macro (ie: the macro will tell it where to copy and paste the picture).

If it is possible to restart the macro automatically upon selecting the object that is great, but even better would be an OK button on the message box.

Is there a method whereby an vbokonly messagebox appears (the text will prompt the user to select a picture), then once the ok button is clicked it will restart the macro?

 

[texasaswang]

Could you have the macro script start when an image is clicked on?

If not, please provide more data on what is going on prior to the pause.

 

[didj]

I want to set the worksheet name as a variable before selecting the picture ie: the starting worksheet is variable, then it selects another sheet where the pictures are located (sheet 10 in the below code example), then I want it to pause, then once the picture is selected go back to the starting sheet (Day2 in the below example).

Range("C16").Select
    Day2 = ActiveSheet.Name
    UserForm1.Show
------
Private Sub CommandButton1_Click()
    
Sheets("Sheet 10").Select
    UserForm1.Hide

End Sub

If I start the script after the object is selected it won't carry the variable "Day2" over.

Make sense?