Pause a macro in VBA and resume by a click of the button

[soniaalalwani]

Hi,


I have paused the macros by using Exit sub and now i want the user to click on a button in the excel which will help him resume the code. I have tried the following but it seems that Goto works only in one Sub:


Sub Next_Client()

Goto 10

End Sub


but this doesnt work. Is there a way where i can call the other macro and it starts executing from the line i left or the line want it to run from. Soemthing like:


Sub Next_Client

New_Info 'This calls the sub with the name New_Info

'I want something like

New_Info (line 10)or (Goto 10) '10 being the line number where i stopped the macro initially

End Sub


Can i use Call in the code for this?

I hope i am clear and someone out here can help me with this

 

[vijaySharma]

@Sonia,


This is not possible unless you have the mechanism (using a temporary sheet) to store the values of all related variables that may be used when calling the Sub the second time.


You can delcare a Boolean variable to check if the end user is calling the Sub first time or second time.... and then Set this to TRUE or FALSE accordingly...


Now everytime you sun the Sub, you need to check if the variable is True .... start from line 1.... if FALSE.... start from Line 10...


However keep in mind that you are NOT PAUSING this macro.... (this is not possible in the way you need and have described above).... you are only solving your issue by using a workaround...


HTH

~VijaySharma

 

[Luke M]

Can you split up your macros differently? Run a complete block of code before the user needs to pause. Or perhaps the better question, why do you think the user need to pause the code? If we can solve that question, it may resolve the entire issue.

 

[soniaalalwani]

Ok let me elaborate the problem. I want the user to insert n number of sheets based on their requirement. The program ask the user to mention the number of sheets to be added in the beginning of the program. Say if the user inserts 2 then the fist client will have three sheets and then there will be some work done by the user in those sheets. when the user has finished inputing the data for the first client then they have the option to click on the button "Next_Client". Now here i want the same procedure to be repeated (i.e) three sheets are added and the user can work on the and so forth) please look at the code below:


Sub New_Info()

z= InputBox ("No. of clients")

For i= 1 To z

UserForm1.Show

Next i

End Sub


Now when the user says that there are 3 clients the user has to enter all the data of the 3 clients at once which becomes tedios. I want to pause the code after the UserForm1 is closed and resume from Next i Line


Sub New_Info()

z= InputBox ("No. of clients")

For i= 1 To z

UserForm1.Show

Exit Sub 'this stops the code after the first client info is added but now i want to go to next i line when the user clicks a button provided on the excel sheet


Next i

End Sub


@Vijay: thans for your reply but i even tried doing it but as the macro moves to another sub the variable value becomes blank. the variable used in one sub is not being detected in the next sub

 

[Sachin]

It sounds like there are two variables to consider:


# of sheets

# of clients


If the number of sheets per client is constant (i.e. 3 sheets per client), then the macro can ask for both variables and then create all of the sheets at one go.


Otherwise, you should make the user run the macro again so they can designate a number of sheets for the next client.

 

[Luke M]

You could use some public variable to store user's initial input, and then have the "Resume" button actually call a second macro. Something like this should work. Have the program run the InitialLoad Macro first, which will setup all the variable. This then goes into the UserForm, and shows it once. When the user clicks on the Resume Button (which in reality is calling the second macro), the macro will be able to use the variable set initially and know when to stop.

[pre]

Public Z As Integer
Public i As Integer

Sub InitialLoad()
Z = InputBox("No. of clients")
i = 1
New_Info
End Sub

Sub New_Info()

If i <= Z Then
UserForm1.Show
i = i + 1
End If

End Sub

[/pre]

 

[soniaalalwani]

@ Luke


Thank you so much. Your code did the trick. It was so simple and still i couldn't figure it out .


Thank you all for the instant inputs.