How to pause macro then select object?

D

didj

Board Regular
Joined
Feb 7, 2008
Messages
50
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?
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
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.
 
Upvote 0
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).

Code: 
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?
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Solved
Adding a Log from user entered comments
Replies
3
Views
217
Tupe77
T
A
  • Question
Help Debugging Custom Data Label Macro
Replies
0
Views
130
arndtea
A
M
  • Solved
How do I prevent users from selecting an Internet Shortcut on a File Dialog?
Replies
6
Views
379
Magic Polygon
M
H
  • Question
How do I avoid a 1004 Runtime Error when trying to run this script?
Replies
3
Views
600
helpneeded2
H
rjplante
  • Solved
Code following macro doesn't run
Replies
1
Views
254
rjplante
rjplante

Forum statistics

Threads
1,214,911
Messages
6,122,198
Members
449,072
Latest member
DW Draft

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top