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

RockandGrohl

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
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.

VBA Code:
' 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!
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

StephenCrump

MrExcel MVP
Joined
Sep 18, 2013
Messages
4,245
Office Version
  1. 365
Platform
  1. Windows
Conceptually, I think you're looking for:

VBA Code:
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

Well-known Member
Joined
Aug 1, 2018
Messages
671
Office Version
  1. 2010
Platform
  1. Windows
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?
 

Forum statistics

Threads
1,141,068
Messages
5,704,088
Members
421,327
Latest member
Msh

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
Top