VBA Resume A Stopped Code (Interrupt code to do something else in another workbook)

[Ark68]

Hello,
I have this code that acts as a form of proactive error control. A critical piece of information (pn) has to be available in a second workbook to ensure the rest of application proceeds without error if it were to encounter missing information. So, this code references the critical data in the second workbook to see if that value exists. If it does, the application can proceed without fearing errors encountered from not finding required information in the second reference workbook. If the code reveals that the value doesn't exist in the reference box, the code stops, and opens up an application for the user the enter the missing information to the second workbook at that time. Worksheet 2 that holds the reference data, also has a worksheet based form in which the user can manipulate the data in the data worksheet.

In this instance, if the initial application reveals missing data, the user is forwarded to to the worksheet form to submit the missing data to the reference data worksheet. At that point, that missing value no longer poses a threat to error moving forward in the primary application (workbook) since the value is no longer missing from the database.

My question is, how do I resume my application's code once the user has successfully entered the data into the second workbook? I know that I could create a userform in the primary workbook to mimic what the worksheet based form in the second workbook does, but I'm hoping to avoid having to reinvent the wheel. I would be nice to use a process already established.

I hope I make sense, it's complicated to try and explain.

Sub permitintegrity()
'checks to ensure each booking has an assigned classification (D,F,C,T,P)
    cntsh = 0 'count of visible sheets
    With ws_front
        .Unprotect
        .Range("BT1") = "Permit integrity:  "
        .Range("BT1").HorizontalAlignment = xlRight
    End With
    For Each ws9 In wb_data.Worksheets
        Debug.Print "Assessing:  " & ws9.Name
        If ws9.Visible = xlSheetVisible Then
            ws_front.Range("BU1") = ws9.Name
            ws_front.Range("BU1:BV1").HorizontalAlignment = xlCenterAcrossSelection
            lrpda = Application.WorksheetFunction.Match("Facility Maintenance Activities", ws9.Columns(1), 0) - 3
            For po = 13 To lrpda
                pn1 = ws9.Cells(po, 3)
                If pn1 <> "" Then
                    If Application.WorksheetFunction.CountIf(ws_permit.Columns(1), pn1) = 0 Then
                        ws_front.Range("BU1").Font.Color = RGB(192, 0, 0)
                        MsgBox "Worksheet:  " & ws9.Name & Chr(13) & "Row:  " & po & Chr(13) & "RID:  " & ws9.Cells(po, 1), , "Invalid Permit Number:  " & pn1
                        wb_permit.Windows(1).Visible = True
                        wb_permit.Worksheets("lists2").Activate
                        With wb_permit.Worksheets("Form")
                            .Activate 'reveals the worksheet based form in data workbook (wb_permit)
                            'user enters and submits entered data to wb_permit data worksheet for future reference
                            'when completed, submitting the data will hide wb_permit and I would like to see the initial code resume at this point? 
                            'some action opposite 'stop'?
                        End With
                    End If
                End If
            Next po
        End If
    Next ws9
    With ws_front
        .Range("BU1") = "OK"
        .Protect
    End With
End Sub

 

[portews]

Where and how is the second worksheet opened? I think you can open a new instance of Excel with that file and interrogate it all from the code on the current workbook so that code will maintain control. If you do need to change the data on that sheet, put in a message box with vbApplicationModal in the Buttons section and code will be suspended until OK is hit. Using the .Activate that you have sends control to the other workbook and you don't want to do that.

Here's a section of code that opens a new instance of Excel, copies information from that sheet to the current workbook. I stuck the messagebox in there to stop the code. You can modify that sheet and when you're done, hit the OK button and code will resume.

            'open a hidden instance of Excel
            Dim app As New Excel.Application
            app.Visible = True 'Visible is False by default
            'open the workbook to check
            Dim book As Excel.Workbook
            Set book = app.Workbooks.Add(<workbookname>)

'look for your criteria here, if you can't find what you want then
            'Messagebox with vbApplicationModal stops code execution until OK is pressed.
            Z = MsgBox("Update data. Click OK when complete.", vbApplicationModal)
            'do whatever else you need to do, like save the other file.
'endif

            'with the latest workbook, Overall Results worksheet
            With book.Worksheets("Overall Results")
                'copy A1:D60
                .Range("A1:D60").SpecialCells(xlCellTypeVisible).Copy
                'paste it into this workbook in the latest worksheet, cell A1
                Thisworkbook.Worksheets(SourceName).Range("A1").PasteSpecial xlPasteValues
                'this next copy/paste is to reduce the clipboard down to a smaller size
                'so Excel won't ask if you want to save it on exit.
                .Range("A1").SpecialCells(xlCellTypeVisible).Copy
                wb.Worksheets(SourceName).Range("A1").PasteSpecial xlPasteValues
            End With
            Application.CutCopyMode = False
            book.Close SaveChanges:=False
            app.Quit
            Set app = Nothing

 

[Ark68]

Wow. Thank you Bill!
I will have to digest this a bit and see where/how I can adapt it to fit my needs.
The second workbook has already been opened, and is hidden, in code from earlier in my application.

In the primary workbook, where the code is, data is gathered from secondary workbooks, analysed and prepared for user output. However, in this case, if critical data needed for analysis and further processing is missing, that primary code has to pause to allow the user to correct the problem by providing them an opportunity to provide the missing information to the appropriate reference workbook(worksheet). In this case, that reference database has a GUI for the user to enter information into that specific database. So when the code pauses, it's to allow the user to use that GUI to enter the information. Once entered, the primary code can resume.

So ... I guess where I might be stumped without experimenting is ...
The first part, where you reveal the hidden workbook ... is that necessary in my case where the workbook is already opened, declared, set and hidden. Will the workbook need to be visible for this to work?
I would set the vbApplicationModal property at the point the missing data has been identified and the application sends the user to the reference workbook's GUI to enter data?
I have no need to copy any data, so safe to assume you included that just for illustration purposes?
I assume I would have to put code in the GUI procedure of the reference data workbook to save and rehide itself again in order to expose the ok button in order to resume the code?
Would I have to reverse the vbApplicationModal property from where the code resumes?

Just looking for clarification before I go messing around with things. I know trial and error with the intent to move one step forward can often times send one back 2 or 3 steps.

 

[portews]

The first part, where you reveal the hidden workbook ... is that necessary in my case where the workbook is already opened, declared, set and hidden. Will the workbook need to be visible for this to work?

Is your GUI for the reference workbook visible at that point? Can you do what you need to do to add your missing data? If yes, you don't need to do anything with it. I was suggesting making it visible to allow you to fix what you needed it to.

I have no need to copy any data, so safe to assume you included that just for illustration purposes?

True. My big idea was the use of the message box as a way to stop the code and wait for you to do fix what you need to fix. This was just something I had handy as an example.

I assume I would have to put code in the GUI procedure of the reference data workbook to save and rehide itself again in order to expose the ok button in order to resume the code?

No, you hitting the OK on the message box would resume the code. When the current code says "I can't find what I need", it will pop up this message box. Leave that message box up while you fix what's missing on the other sheet. When it's ready to go, hit OK on that message box and the code will resume, checking the now fixed sheet.

If the code in the reference sheet handles saving automatically after you've made changes, you may not have to do anything. If you have both workbooks open, you can also run any code on the reference workbook from the first, i.e. if you have a Sub SaveRefFile to save the reference book, you can call that from the current workbook to save the reference file.

I would set the vbApplicationModal property at the point the missing data has been identified and the application sends the user to the reference workbook's GUI to enter data?

The vbApplicationModal property is part of the message box properties that tells Excel, "don't run any more of this code until the OK is pressed." Once you press OK, everything continues on, there's nothing to reset after that.

 

[Ark68]

Hi Bill, I really appreciate the support you're providing to see this work. I've been tinkering around with it, but the logic is eluding me a bit so I've hadn't much success.

Perhaps you can follow along with me in my logic, I'll share some code, and we can figure out where the components should go.

I have my workbook (SignatureSheets.xlsm) that is the primary workbook with all th code. It's purpose is to reference data from support data workbooks, analyze that data, and process same data for presentation to the user in the form of a report. The data in the support datasets have to be present to refer to, or else the primary application will copme to a blazing halt. One supporting workbook is called permit_info.xlsm. It is a workbook designed to work independently to process permit information and holds not only data, but also has a worksheet designed as a GUI for the user to enter data into the database. The primary worksheet relies on data in this dataset, and when if fails to refeence data encountered, the wish is to allow the user to access this GUI to enter missing data, and then on completion resume the primary application code.

Here is the code that loads and hides permit_info.xlsm

Sub open_permit()
    Dim strpath As String
    Dim lrow As Integer
    
    'Set ws_front = Workbooks("SignatureSheets.xlsm").Worksheets("Front")
    Application.ScreenUpdating = False
    strpath = "D:/WSOP 2020/permit_info.xlsm"
    Workbooks.Open strpath
    Set wb_permit = Workbooks("permit_info.xlsm")
    Set ws_permit = wb_permit.Worksheets("Permit_Data")
    Set ws_cust = wb_permit.Worksheets("Customer_Default")
    ws_permit.Unprotect
    If ws_permit.AutoFilterMode Then ws_permit.AutoFilterMode = False
    lrow = ws_permit.Cells(ws_permit.Rows.Count, "A").End(xlUp).Row
    Set rngPermit = ws_fac.Range("A1:BO" & lrow)
    lrow = ws_cust.Cells(ws_cust.Rows.Count, "A").End(xlUp).Row
    Set rngCust = ws_cust.Range("A1:AG" & lrow)
    wb_permit.Windows(1).Visible = False
    Application.ScreenUpdating = True
    ws_front.Unprotect
    With ws_front.Cells(5, 1)
        .Value = "Permit Data"
        .Font.Color = RGB(24, 160, 35) 'green
    End With
    ws_front.Pictures("hidden3").Visible = True
    ws_front.Protect
End Sub

As the primary application is executed, if it encounters a permit number for example not in the permit database, continuing would cause an error. To avoid the error and allow the application to function, the user is required to access the GUI (worksheet: FORM) in permit_info.xlsm.

Sub permitintegrity()
'checks to ensure each booking has an assigned classification (D,F,C,T,P)
    cntsh = 0 'count of visible sheets
    With ws_front
        .Unprotect
        .Range("BX1") = "Permit integrity:  "
        .Range("BX1").HorizontalAlignment = xlRight
    End With
    For Each ws9 In wb_data.Worksheets
        Debug.Print "Assessing:  " & ws9.Name
        If ws9.Visible = xlSheetVisible Then
            ws_front.Range("BY1") = ws9.Name
            ws_front.Range("BY1:CB1").HorizontalAlignment = xlLeft
            lrpda = Application.WorksheetFunction.Match("Facility Maintenance Activities", ws9.Columns(1), 0) - 3
            For po = 13 To lrpda
                pn1 = ws9.Cells(po, 3)
                If pn1 <> "" Then
                    If Application.WorksheetFunction.CountIf(ws_permit.Columns(1), pn1) = 0 Then 'missing information identified
                        Stop
                        ws_front.Range("BY1").Font.Color = RGB(192, 0, 0) 'red
                        '1
                        MsgBox "Worksheet:  " & ws9.Name & Chr(13) & "Row:  " & po & Chr(13) & "RID:  " & ws9.Cells(po, 1), , "Missing Permit information:  " & pn1
                        wb_permit.Windows(1).Visible = True 'reveal hidden permit workbook
                        wb_permit.Worksheets("lists2").Activate 'this is done to ensure that the worksheet: FORM activation code is executed. Opening direct to FORM would miss critical worksheet activation code
                        wb_permit.Worksheets("Form").Activate 'activates GUI and executes worksheet activation code
                        '2
                  End If
                End If
            Next po
        End If
    Next ws9
    With ws_front
        .Range("BY1").Font.Color = RGB(3, 172, 19) 'green
        .Range("BY1") = "OK"
        .Protect
    End With
End Sub

The code above identifies an inconsistencey and then prepares the GUI for the user for permit data entry. I can only guess that the message box with the xlApplicationModal would go in here somehwere.
If in here, not sure where. If I put it in at position 1, the GUI won't open automatically. If I put it at position 2, it will never be reached becuase the permit_info.xlsm code takes over.

Here is the the worksheet FORM activation code:

Private Sub Worksheet_Activate()
   Stop
   Call ReadingView
   Application.EnableEvents = True
   mbevents = True
End Sub

The GUI is now setup to accept data. The user then clicks a Submit button which sends the entered data to the database. At that point, the application code can resume without error as the missing data is now available. The submit button should then hide the workbook again to reveal the primary application. I assume that is where the OK button would be clicked?

 

[Ark68]

The closest I got in testing this was to put

Z = MsgBox("Update data. Click OK when complete.", vbApplicationModal)

at position 2 (see code in post 5).
The form in ther second workbook opens opens up and does it thing to prepare, and the message box (Z=) pops up over top. However, the form is not accessible. The Windows waiting disk is spinning, so I assume it's waiting for OK to be clicked.

I hope I'm doping something wrong because this seems like exactly what I need to do!

 

[RoryA]

Personally, I don't think I'd adopt this approach - I'd just flag all the offending items in some way (highlighting or whatever), and tell the user to fix them and then run the code again.

If you really want to do it this way, you're probably going to need an event listener of some sort, or a continuous timer checking for some flag to be set ready to proceed.

 

[Ark68]

Thanks Rory! Your input is always valued. It's unfortunate this isn't an easy approach. I was hoping to avoid having the user have to pop in and out of different workbooks over the course of the process. But it appears it may be inevitable.