Excel: opening workbook

Question

Private Sub CommandButton1_Click()
        ...
        Set wb = Workbooks.Open(Filename:="C:\book1.xls")
        If MsgBox("Please Review the Transmittal sheet, if satisfied click Yes to send,  No to cancel." & vbCrLf & "Transmittal Number: " & Transm, vbYesNo) = vbNo Then
        ...
End Sub

The problem is this, I trying to open a workbook from within another workbook. The second workbook opens but only to a certain point – the screen is white as if it’s waiting for something to complete.

My code moves to the next line with the msgbox prompt, once I select either yes or no and the sub routine is completed the second workbook then opens completely showing its data. It doesn’t open properly when called within the sub routine, not under the sub routine is executed completely

Any ideas why?

Over all I’m trying to figure out a solution for the following scenario. User opens the excel workbook, clicks on a button on the sheet that opens a user form. The user selects some information in combo box and based on those selections the excel sheet is filled out with relative data. Once filled the user clicks on a button to generate a report based on the information in the excel workbook. Before it’s sent, the user must check and confirm that the data is correct on the excel workbook, so they are prompted with a msgbox asking them to do this – click yes if data is correct, no if not. But with the msgbox and user form, they can’t access the excel workbook. My thoughts on this was to open a new workbook and copy the data from the current workbook allowing the user to view it that way. Maybe you have a better way of doing this.

Please note that the original code is not mine so I’m only fixing bugs, this particular bug is that the user can’t focus on the excel worksheet because of the user form and msgbox.

Jim

Answer 1

I couldn't find a suitable solution to this, the msgbox seem to lock excel completely and I wasn't able to view the new worksheet either.

So the work around I came up with was to export the current worksheet as PDF and display that.

   Dim pdfFile As String
    pdfFile = Get_Temp_File_Name(sExtensao:="pdf")

    ThisWorkbook.ExportAsFixedFormat _
    Type:=xlTypePDF, _
    Filename:=pdfFile, _
    Quality:=xlQualityStandard, _
    IncludeDocProperties:=True, _
    IgnorePrintAreas:=False, _
    OpenAfterPublish:=True
    ' Allow time for PDF to be created and loaded
    Application.Wait (Now + TimeValue("0:00:10"))
GoTo PreviewSkip

TrapPreview:
    MsgBox Err.Description

PreviewSkip:

If MsgBox(...

I also use the code from Excel Tilte Set to generate the temp file name with pdf extension.

Thanks for everyone's input. Jim

Answer 2

Not sure what is your exact issue but if you want to prompt for Yes/No AFTER a sheet is being "read":

macro #1 (located in "Thisworkbook" on VB editor):

Private Sub Workbook_Open()
Sheets("Transmittal sheet").Activate 'or whatever the sheet's name
End Sub

This will force user to have this worksheets on screen when the workbook is opened. Next, macro #2 (located in "Transmittal sheet"(Sheet X)) 'again whatever sheet name / number:

Private Sub Worksheet_Deactivate()
    Set wb = Workbooks.Open(Filename:="C:\book1.xls")
    If MsgBox("Please Review the Transmittal sheet, if satisfied click Yes to send,  No to cancel." & vbCrLf & "Transmittal Number: " & Transm, vbYesNo) = vbNo Then
End Sub

This will prompt when the user trying to move away from the worksheet. An alternative is to place the 2nd macro under workbook_beforeClose depends on how the situation goes. Again it is better if you could clarify what exactly you would like to achieve.