Pause a sub while a selection is made

[julhs]

I have a sub that does as intended WHEN I step through it (F8)
BIG but though; when RUN it in full, I simply don’t get the opportunity to make a selection from “ComboBox1” (surprise surprise!!)
I know I need a way to “Pause” the this sub while a selection is made in “ComboBox1” and then continue when it has been made, but I can’t figure out how to do it!!
I have looked at timed pauses but it is not the way I want to go.

Option Explicit
Public Sub SelectFilterToRun ()
Dim sht As Worksheet
Dim rng As Range
Dim FrwD As Long

   Set sht = ThisWorkbook.ActiveSheet
      Application.ScreenUpdating = False 'This disables ScreenUpdating
      Application.EnableEvents = False 'This disables Events
   Set rng = Range("AK:AK").Find(what:="Paid to", LookIn:=xlValues, LookAt:=xlWhole)
     If Not rng Is Nothing Then
       FrwD = rng.Row
   With ActiveSheet
      .ComboBox1.Visible = True 'This Un-Hides "ActiveX ComboBox1" as is set to "Hidden" in its properties by default, this Sub "Unhides it"
   End With
  End If
      MsgBox "Select from the DropDown which 'Get' Sub you want to run then"

[COLOR=rgb(226, 80, 65)][B]‘### This is where I need to create the pause[/B][/COLOR]

     If Not Range("$BA$58") Is Nothing Then
       Select Case Range("$BA$58")
        Case "GetRePaid"
       Call GetRePaid
        Case "GetCommentCell"
       Call GetCommentCell
     End Select

     MsgBox "You didn't Select from the DropDown which 'Get' Sub you want to run, go do it again"
      Application.ScreenUpdating = True 'This re-enables ScreenUpdating
      Application.EnableEvents = True 'This re-enables Events
   If Range("$BA$58") = "" Then
    With ActiveSheet
     .ComboBox1.Visible = False 'Hides "ActiveX ComboBox1" for the next run of the Sub
   End With
  End If
 End If
End Sub

The sub that will be called has an un-orthodox “End” statement that ends the above sub
ComboBox1 is an ActiveX

 

[DanteAmor]

I know I need a way to “Pause” the this sub while a selection is made in “ComboBox1” and then continue when it has been made, but I can’t figure out how to do it!!
I have looked at timed pauses but it is not the way I want to go.

It is not possible to pause that way and then return to code execution.

I recommend showing the combobox with the following:

Public Sub SelectFilterToRun()
  Dim sht As Worksheet
  Dim rng As Range
  Dim FrwD As Long
  
  Set sht = ThisWorkbook.ActiveSheet
  Set rng = sht.Range("AK:AK").Find(what:="Paid to", LookIn:=xlValues, LookAt:=xlWhole)
  If Not rng Is Nothing Then
    FrwD = rng.Row
    ActiveSheet.ComboBox1.Visible = True 'This Un-Hides "ActiveX ComboBox1" as is set to "Hidden" in its properties by default, this Sub "Unhides it"
  End If
  MsgBox "Select from the DropDown which 'Get' Sub you want to run then"
End Sub

Then they must select an item from the combobox and when they do, a code will be executed.

Put the following code in the sheet events where you have the combobox:

Private Sub ComboBox1_Change()
  Select Case ComboBox1.Value
    Case "GetRePaid"
      Call GetRePaid
    Case "GetCommentCell"
      Call GetCommentCell
    Case ""
      MsgBox "You didn't Select from the DropDown which 'Get' Sub you want to run, go do it again"
      ActiveSheet.ComboBox1.Visible = False 'Hides "ActiveX ComboBox1" for the next run of the Sub
  End Select
End Sub

Note Sheet Event:
Right click the tab of the sheet you want this to work, select view code and paste the code into the window that opens up.

--------------
Let me know the result and I'll get back to you as soon as I can.
Cordially
Dante Amor
--------------​

 

[julhs]

Hello again Danta.
I had pretty much come to the conclusion that what you suggest was going to be the main option.
I have been playing around with an alternative approach to yours that involves 2 separate subs (not using a Change Event), it is more convoluted but with the testing and modifications to date it does appear to work.
However, I believe your suggestion of using a Change Event is by far the better/simplest option.
I found a VERY old post on “Pausing” and then “Resuming” a Sub here;
Pause macro then resume macro
I spent ages trying to diagnose why I couldn’t get it to run for me, so gave up and went down the 2 Sub route.
Your expert eye will find the flaws in the suggested solution
Many thanks
Julhs