Update Current VBA to allow for more Checkpoints

[johnny51981]

100% VBA Plagiarist here, and I've been using the code from this post and I cannot love it anymore every day. LOL.

That said, I would like to update a different piece of VBA. This currently prompts the user that the refresh is complete, changes to a different environment type (so the VBA isn't continually tripped), saved, and closed.

Sub worksheet_change(ByVal target As Range)
Set target = Range("RefreshStatus")
    If target.Value = "COMPLETE" Then
    MsgBox ("All Received PDF Data has now been scraped. Step 1 will now close to begin Step 2."), vbOKOnly
    
        Call ReportEnvirontment_Test
        Call CloseExcel
    End If
End Sub

This code is housed in the "Status" Sheet, which is where the table's results (from the VBA in the linked post) land.

I am working on handing this process off to someone else, and would like to add some additional prompts as there are some things that I do, that need to be done, but I remember to check since I've done it for so long.

I would it to pass through and confirm 2 questions after the RefreshStatus is set to "COMPLETE". The full order that I would find ideal would be...or something of the like.

Sub worksheet_change(ByVal target As Range)
Set target = Range("RefreshStatus")
    If target.Value = "COMPLETE" Then
    MsgBox ("All Received PDF Data has now been scraped."), vbOKOnly

        Call ReportEnvirontment_Test
        'Something in here that would be an alert [B]IF[/B] there is results in a specific query. This will be a Named Range called "Fallout1", which will be a Yes or No. 
                'If Yes, MsgBox ("There is Fallout in Table 1. Please review and refresh."), vbOKOnly and End VBA Here
                'If No, allow VBA to continue.
        'Something in here that would be an alert [B]IF[/B] there is results in a second specific query. Named Range called "Fallout2", also Yes or No. 
                'If Yes, MsgBox ("There is Fallout in Table 2. Please review and refresh."), vbOKOnly and End VBA Here
                'If No, allow VBA to continue.
        Call CloseExcel
    End If
End Sub

Any help is appreciated!

 

[myall_blues]

You should just be able to access the Count property of the range - something like below. I'm not sure what you meant by 'changes to a different environment type (so the VBA isn't continually tripped),', but when using event handlers (like Worksheet_Change) it's a good idea to switch off event handling by setting DoEvents=False while the code runs, and then switch it back on (DoEvents=True) before completing the code.

Sub worksheet_change(ByVal target As Range)
Set target = Range("RefreshStatus")
    DoEvents = False
    If target.Value = "COMPLETE" Then
        MsgBox ("All Received PDF Data has now been scraped."), vbOKOnly
        Call ReportEnvirontment_Test
        If Worksheets("Status").Range("Fallout1").Count > 0 Then
            MsgBox ("There is Fallout in Table 1. Please review and refresh.")
            DoEvents = True
            End Sub
        End If
        If Worksheets("Status").Range("Fallout2").Count > 0 Then
            MsgBox ("There is Fallout in Table 2. Please review and refresh.")
            DoEvents = True
            End Sub
        End If
        Call CloseExcel
    End If
    DoEvents = True
End Sub

 

[johnny51981]

You should just be able to access the Count property of the range - something like below. I'm not sure what you meant by 'changes to a different environment type (so the VBA isn't continually tripped),', but when using event handlers (like Worksheet_Change) it's a good idea to switch off event handling by setting DoEvents=False while the code runs, and then switch it back on (DoEvents=True) before completing the code.

Sub worksheet_change(ByVal target As Range)
Set target = Range("RefreshStatus")
    DoEvents = False
    If target.Value = "COMPLETE" Then
        MsgBox ("All Received PDF Data has now been scraped."), vbOKOnly
        Call ReportEnvirontment_Test
        If Worksheets("Status").Range("Fallout1").Count > 0 Then
            MsgBox ("There is Fallout in Table 1. Please review and refresh.")
            DoEvents = True
            End Sub
        End If
        If Worksheets("Status").Range("Fallout2").Count > 0 Then
            MsgBox ("There is Fallout in Table 2. Please review and refresh.")
            DoEvents = True
            End Sub
        End If
        Call CloseExcel
    End If
    DoEvents = True
End Sub

Thank you for this!

Addressing this part:

I'm not sure what you meant by 'changes to a different environment type (so the VBA isn't continually tripped)

I have 2 bits of code that flips between "TEST" and "PRODUCTION" in the "RefreshStatus" range, essentially setting them up as Environments (like a database, of sorts).

• When in initiating for actual use, the environment updates to PRODUCTION and after the Refresh is complete, it will flip back to TEST and CloseExcel (another code).
• When in initiating in TEST, nothing occurs after the Refresh is complete.

Now on to trying to use the code you provided. When using DoEvents = False or DoEvents = True, I receive this message.

When I comment the DoEvents pieces out, I then receive this message

This is what I have augmented my code to after using what you've provided as a jumping off point. Its clearly still wrong...but it helps display what my end goal is a little better.

Sub worksheet_change(ByVal target As Range)
'Sets Names Ranges as Ranges in VBA
    Set target1 = Range("RefreshStatus")
    Set target2 = Range("NewFind")
    Set target3 = Range("ContactInfo_Check")
    
    DoEvents = False
    If target1.Value = "COMPLETE" Then
        MsgBox ("All Received PDF Data has now been scraped."), vbOKOnly
        Call ReportEnvirontment_Test
        If target2.Value = "Yes" Then
            MsgBox ("There is Fallout in Table 1. Please review and refresh.")
            DoEvents = True
            End Sub
        End If
        If target3.Value = "Yes" Then
            MsgBox ("There is Fallout in Table 2. Please review and refresh.")
            DoEvents = True
            End Sub
        End If
        Call CloseExcel
    End If
'    DoEvents = True
End Sub

 

[myall_blues]

Apologies that’s my mistake. It should be Application.EnableEvents=TRUE/FALSE