VBA -- how to get code to pause in place until a condition is met

R

rubberband0

New Member
Joined
Jul 4, 2009
Messages
15
Good morning,

I was wondering does anybody know of a way to get code to pause in place until a condition is met? Specifically, I want my code to bring up a modeless userform, but I want the code to pause at that spot in the procedure until the userform is unloaded (which is what seems to happen when I bring up a modal userform).

I've tried looking at using a boolean variable in order to control whether the code after the modeless userform is displayed gets executed. But, the code keeps running and just seems to skip over the parts where it does not have permission to execute. Since I have the code within a for-next loop, I don't want the code looping a bunch of times either

The general structure of my code is as follows:

For i = 1 to 52

If a < b then
userform.show vbModeless

--> Here is where I want to pause until the userform is unloaded <--

More code/instructions

End if

Next i

------------------------

Thanks for all your help!

Bests,
Richard
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Sort by date Sort by votes
Can you post your actual code? What are the conditions you want the code to wait for? I would look at rewriting the code so the userforms run the code when you click a button (which it seems you aren't doing?)
 
Upvote 0
I found this thread, which may do what I describe below. I haven't tried it, and found it AFTER I made this post. But maybe this would also be useful assuming it works.
http://www.mrexcel.com/forum/showthread.php?t=408209&highlight=pause


I have a similar query but I'm not sure the code i have would be useful..
I am filling in a "form" (not really a form..but it looks like a form on an Excel sheet that accpets numbers..Everytime I feed the form its numbers, it produces a result, draws a graph, and does some other cranking in the background in support of this..

I would like to tell the macro that feeds my form the inputs to "pause", just long enough to let Excel finish drawing the graph and doing the the other background calculations..

I was hoping this would be some simple piece of code that just makes the macro pause itsa execution for maybe 2 seconds, and then move on..

If this is something that can be added in, I would have interest as well in this solution..
 
Last edited:
Upvote 0
I found a solution that seems to work well for me and gets the code to "pause" in place for as long as I need it to, although admittedly it feels a little artificial to me.

What I did was use 2 subs that loop together, one of them being in my main module while the other as part of the ok button click event code of my user form. When it's time for my Main sub to call the Userform, I immediately terminate the Main sub. The userform initializes, but waits until the ok_button_click event before proceding with further calculations. This is where the "pause" comes in. :cool: These further calculations are pretty much identical to the ones in the Main Sub. Once the instructions in the ok_button_click event are executed, I call back the Main sub to restart the calculations from the next startWeek and immediately unload the userform. I allow both subs access to common variables through the use of public declarations.

Code: 
Public startWeek as integer 
--------------------------------
Sub Main()

If startWeek > 1 Then
    startWeek = startWeek
Else
    startWeek = 1
End If

For j = startWeek To 52
    For i = 1 To 5
       If a(j, i) < b Then
          UserForm.Show vbModeless
          Exit Sub
       Else
       'more calculations here
       End if
    next i
next j

End Sub
------------------------------------------

Private Sub OK_Button_Click()

If startWeek > 1 Then
    startWeek = startWeek
Else
    startWeek = 1
End If

'All calculations done here for a given startWeek.

startWeek = startWeek + 1

Unload Me
Call Main

End Sub
 
Upvote 0
I created a user form with a button on it.
The user form has 'ShowModal' property set to 'False'.

The form has a button on it, with code:
b7Push = True
Unload Me

In my module, the code is:
b7Push = False
frmW7Continue.Show
Do While b7Push = False
DoEvents
Loop

Because the form is 'not modal', the displayed spreadsheet can be edited, but the code does not proceed until the button is clicked.
 
Upvote 0
You must log in or register to reply here.

Similar threads

JeffGrant
  • Solved
Pause VBA Code until userform command button is clicked
Replies
2
Views
996
JeffGrant
JeffGrant
H
  • Solved
Catching the Mouse Click Error in Modal User Form
Replies
4
Views
302
huiettcm
H
pbornemeier
  • Question
VBA Hangs While Producing Complex Graph
Replies
1
Views
128
frabulator
frabulator
A
  • Question
What is wrong with this VBA code: userForm get hand ico for pointer on hover
2
Replies
15
Views
523
RoryA
RoryA
M
  • Question
Code works however receiving Runtime error 380 - Could not set the list property. Invalid property value
Replies
2
Views
506
Megg
M

Forum statistics

Threads
1,214,978
Messages
6,122,549
Members
449,089
Latest member
davidcom

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