Pause VBA Code For User Interaction

  • Hello,
    I have a situation where I have an msg box pop up that asks the user to identify whether or not the file they have chosen meets the standards needed to continue running the macro. Specifically, I need them to answer "Yes" if the file has a list of data in the first column, if there are no blanks in that list and if the first row of data begins on Row 4. If they answer yes, the macro continues ahead and does what it needs to. If the person selects "no," another input box pops up that asks the user to make the edits. This msg box asks the user to click "ok" when they have made those edits. I want the macro to pause after the person has selected "no" from the first msg box, so that the person can make those corrections. Then, I want the macro to resume when the user selects "OK" on the second msg box. How might I do this?
    I have included the piece of my code below.

  • Re: Pause Code User Corrections Then Resume Code


    I think that instead of using a message box, you will need to create a modeless userform with an OK button. After the user does the editing and clicks the OK button, it would then call the getjobtitle macro and close the userform.


    What do you want to happen if the user doesn't edit but still clicks OK?

  • Re: Pause VBA Code For User Interaction


    Just out of curiosity...


    Why not have the macro check for the conditions you mention instead of asking the user? ...and maybe have the macro automagically make those edits.

    [SIZE="1"]Sub All_Macros(Optional control As Variant)[/SIZE]

  • Re: Pause VBA Code For User Interaction


    You need to set the button argument of the MsgBox to vbYesNo rather than vbOkOnly.


    Code
    userResponse = MsgBox("Push a button", vbYesNo)
    
    
    if userResponse = vbYes then MsgBox "Yes pressed"
    
    
    if userResponse = vbNo then MsgBox "No pressed"

    The logic is not the tighest, but I wanted to show both of the constants vbYes and vbNo.
    In practice, this version would be the one I'd prefer.

    Code
    If MsgBox("Push a button", vbYesNo) = vbYes Then
        MsgBox "Yes pressed"
    Else
        MsgBox "no pressed"
    End If
  • Re: Pause VBA Code For User Interaction


    Who's that masked man? Hi Aaron long time no here from good to see bouncing around
    are you no longer a Jedi?

    Jim
    "The problem with designing vba code completely foolproof is to underestimate the ingenuity of a complete fool."

  • Re: Pause VBA Code For User Interaction


    Quote from Aaron Blood

    Just out of curiosity...


    Why not have the macro check for the conditions you mention instead of asking the user? ...and maybe have the macro automagically make those edits.

    Agree, if you rely on the user, they will let you down more often than not.


    Who's the graffiti bandid in your Avatar ? :)

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!