pausing macro

Is it possible to pause a macro, enabling the user to perform actions and then recommence the macro by performing an action?

For example:
The macro pauses so the user can select a specific cell and then the user can restart the macro by pressing 'f5'.

Using buttons would not be sufficient in this case

Cheers,
Alex

DangleBerry

The only thing close to that I can think of quickly would be to create a form that is modeless, then when the person clicks an OK button the remaining code is performed. This I think would take a bit of playing to get it right but could be done.

Mike

I've done something along those lines before, but the workbook that will be selected will change so I don't think it will work. I have another way I can get it to do what I want but it's a lot more long winded so I hoped for a lazy option!!!

the only way to truly pause a program would be to put a stop instruction in. then they could select where ever and then go back to the edditor and depress F5. This could be very risky though for those who are wondering how and what you code does.

Trying to keep things as simple as possible for the user. Don't want them to have to access editor. I might have a play around with stop and try a few other avenues. if I find anything useful I will post it here.

Thanks for the help Mike.

A thought for you Alex:

I don't know if this is possible but there are people out there with far more experience than me who should be able to answer.

When you enter an =If into a cell on Excel, at the right of the prompt there is an icon which, when clicked, allows you to select a cell and then return to the "if" prompt. If this control was accessible via vba it might give you the funtionality you require.

Whether it is accessible and how you access it would need more knowledge than I have but someone out there will know I'm sure

I would suggest having 2 macros. The first finishes when the pause is required and uses:

application.onkey

to assign a key to run the second macro. The second macro could then use it again to remove the functionality of pressing this key to run the second part.

If you declare your variables global they should remain in memory for the second macro to use or you could store them using hidden names if there is likely to be a long pause in between to ensure that they are still available.

:)

I'd run the first procedure from a form button. When the process reaches the bit where the user selects a cell, unhide a 'RefEdit' control and stop, waiting until a cell is selected and the user has clicked another button before proceeding...

You might also want to check out Application.InputBox("Select a cell", "Range", , , , , , 8), as suggested here:
http://www.visualbasicforum.com/t108542.html

I hear what you guys are saying, but that doesn't mean I understand it!!! :confused:

I'm not really up with a lot of the VB jargon. I'm new at the game, and what I know I've mostly learnt from playing around and making mistakes. Sorry if these question sound silly, but they say theres no stupid questions, just stupid people, what does that make me? :chuckle:

1) what is a form button exactly? Is it a button on the toolbar or on the spreadsheet, or somthing else entirely? A button on the toolbar sounds like it will do the job quite nicely thanks Timbo.....

2)Mark, what is the best way to make a variable global. I've tried to 'dim' them as 'global' in a sub but that doesn't seem to work. The method I've found that seems to do the trick is


Public variable as string


i put this outside of the subs and it seems to work, but I don't really know what that means and if its the best method. Any tips would be great!

3) I like your solution Insomniac, but when I press 'cancel' an error comes up, how do I fix this?

Now I just gotta work out which works better. I need values from a number of cells so your style is good for that, but using a toolbar allows the required workbook to be opened after the prompt. maybe I can use a combination of the two.

Curse you guys, I've got some old code I've written that I wanna use this stuff to neaten up, even though they work fine already! I'm studying Engineering so I live by the age old Engineering philosophy:

Work out how big it needs to be, then build it a whole lot bigger!

Ah.... over Engineering. It's all good! thanks guys!

3) I like your solution Insomniac, but when I press 'cancel' an error comes up, how do I fix this?

Sub GetRange()
Dim rng As Range
On Error Resume Next
Set rng = Application.InputBox("Select a cell", "Range", , , , , , 8)

If rng Is Nothing Then
MsgBox "You havn't selected a range"
Else
MsgBox "You selecteted range " & rng.Address
End If
On Error GoTo 0
End Sub

Thanks mate, you've done well. My old friend On Error Resume Next.......

Had to zip of to work after last post but I knew I'd seen this somewhere before, see here for a full description of how to use the Application.InputBox.

Full credit to:http://j-walk.com/ss/excel/tips/tip81.htm

Neat!

Thnaks again Insomniac. Had a little look around at the site you linked, do you know what the other values for 'type' do? I tried a couple and couldn't get them to work.

What I would like to do if possible is find one where you just type in an alphanumeric answer. The situation is that I need certain information that is stored on another sheet. It has been placed as an image (who knows why?!?!?) so they can't select the data, but i need the functionality of the user being able to change sheets and move around, which I don't think can be done with a standard input box.

Any ideas?

From the help files:

Type Optional Variant. Specifies the return data type. If this argument is omitted, the dialog box returns text. Can be one or a sum of the following values.

Value Meaning
0 A formula
1 A number
2 Text (a string)
4 A logical value (True or False)
8 A cell reference, as a Range object
16 An error value, such as #N/A
64 An array of values
You can use the sum of the allowable values for Type. For example, for an input box that can accept both text and numbers, set Type to 1 + 2.


I have not used it this way but from the help it looks like you can do it by specifying 2 Types for input. Otherwise you may need to look at other options.

looks like just what i need! Trouble is I can't get it to work properly! Here is the code I'm using:


Sub test()
Set myval = Application.InputBox(prompt:="Please enter text", Type:=2)
MsgBox (myval)
End Sub


when i try to type in a value it brings up the debug dialog box. If I put an equals sign first the debug dialog box doesn't come up, but instead a text is not valid prompt comes up.

when I replace type=2 with 8 it works fine. Any ideas?

Looking at insomniac's answer: You could try type:=10. Hopefully that might give you what you want.

It's only the string that I want. I was just saying I used type=8 to show that the code works for that.

Tried 10 though, still no good :(

Dangleberry,

Try this:


Sub test()
myval = Application.InputBox(prompt:="Please enter text", Type:=2) ' SET removed from statement.
MsgBox (myval)
End Sub


This worked fine for me

Closed the [vb] tags - Mill

Yes, using SET is only a good idea for Type 8 in this case. (A Range is an Object, but a String would not be.) Although, even here, an On Error Goto... statement prior might be a good idea.

-- Mike

thanks Crittar, does what I want now!!

Ta Mike, was a little unsure what SET did exactly. Yeh, will chuck an on error statement in when i put it in my code. Thanks for your help guys, especially Insomniac!!

You're welcome, always happy to help