Perform one half of module, then the second... What a day!

[RockandGrohl]

Feel like I'm spamming the boards! Sorry everyone, I just have a really ambitious project.

I'm using a userform to input criteria and then filtering a huge database with this. Along the way, I need to count the volume of clients the criteria are returning.

There's going to be a few levels to this, but for now let's think about this as having a level 1 and a level 2 criteria selection process.

LEVEL 1
We start with 300,000 clients. Right away, if I choose "DM" or "EM" radio button, a filter is applied and this list may be cut down

LEVEL 2
After these clients have been cut down a bit, we then enter needing to use advanced filter to chop down the rest of the clients.

' This code is started from the criteria selection panel
' DIM stuff here
' Worksheet setup stuff here


' Check if Level 1 is complete

' If level 1 not complete, do Level 1 code


' End level 1 and set a flag to say level 1 is complete
' End the sub here and unhide the criteria selection panel

' If Level 1 has been completed, then skip all the above and commence level 2

' Once level 2 completed, end sub.

So essentially, I'm calling a sub from the userform's "Calculate Volumes" button. After some level 1 criteria are selected (DM/EM) - it loads the database, performs some code to chop it down and count the results, and then I'd like to be returned to my userform with the count shown on screen.

If I press that same button again, I'd like it to skip over the Level 1 code which has already executed in this session, and then perform level 2 code, which is chopping the criteria field down further.

Just to be clear I don't actually need any code here for the actual meat and bones of this - the selecting criteria etc, I've got all that already, I just need to understand the format you would have to basically execute one part of code on one condition, then stop and show the same userform, and if that condition is met, kind of "resume" the code and do the second part.

I've probably explained that in the worst way possibile, apologies!

 

[StephenCrump]

Conceptually, I think you're looking for:

Public LevelOneComplete As Boolean
Sub DoStuff()

    If Not LevelOneComplete Then
        'Do stuff with level 1
        'If all OK then ...
        LevelOneComplete = True
    Else
        'Do level 2 stuff
        'If appropriate (and you don't want to iterate level 2 stuff) then ...
        LevelOneComplete = False
    End If

End Sub

 

[RockandGrohl]

Hi Stephen,

This is basically what I have, but as the level 1 process includes opening a userform and doing stuff on it, I feel like I need to write a value to the sheet which will flag whether the process has completed level 1 or not, then proceed with level 2. At the end of Level 2 I can decide whether I want to clear the flag or not. Do you agree?