none
Print preview and userform

    Question

  •  

    I have a project in Excel that has a sheet with command buttons that activate a userform.  Depending upon which button is clicked, a userform then displays a list of available charts in a multioption listbox.  Upon selecting a chart or charts, a command button on the form runs code that determines which chart(s) selected then calls a function that selects the chart and invokes the Print Preview window.

     

    When the preview window is dismissed either by 'Close' or by 'Print', VB displays "Code execution has been interrupted".  If I select Debug, the line immediately following the Application.ActiveChart.PrintPreview is highlighted.  I can not figure out how to correct the error.  When I step through the code using F8, no error is ever encountered.

     

    Thoughts or suggestions?

    Thanks,

    JP

     

    Sample code:

     

    ' Worksheet command button OnClick

    Private Sub btnSelectList1_Click()
        
        fieldlist = "List1"
        frmFieldSelect.Show

    End Sub

     

    ' Form initialize event

    Private Sub UserForm_Initialize()

        ListBox1.RowSource = ""
        
        If fieldlist = "List1" Then
            ListBox1.RowSource = "'Variables'!L3:'Variables'!L4"
        End If
        If fieldlist = "List2" Then
            ListBox1.RowSource = "'Variables'!M3:'Variables'!M12"
        End If

    End Sub

     

    ' Form command button OnClick event

    Private Sub btnViewSelection_Click()

        Dim numFields As Integer
        Dim fieldSelected() As Boolean
           
        numFields = ListBox1.ListCount - 1
        ReDim fieldSelected(numFields) As Boolean
       
        For counter = 0 To numFields
            fieldSelected(counter) = ListBox1.Selected(counter)
        Next
        
        Unload Me  ' used so that the user form does not display on top of the preview window
            
        If fieldlist = "List1" Then
       
            For counter = 0 To numFields
           
                If fieldSelected(counter) Then
                    Call prtChart(fieldChartxRef_List1(counter + 1))
                End If
            Next
           
            Application.Goto Range("A3"), True
           
        End If
        
        If fieldlist = "List2" Then
       
            For counter = 0 To numFields
           
                If fieldSelected(counter) Then
                    Call prtChart(fieldChartxRef_List2(counter + 1))
                End If
            Next
           
            Application.Goto Range("A75"), True
           
        End If
       
    End Sub

    ' Funtion for previewing the desired chart(s)

    Sub prtChart(ChrtName As String)

        Sheets("Net Graphs").Select
        ActiveSheet.ChartObjects(ChrtName).Activate
        ActiveChart.ChartArea.Select
        Application.ActiveChart.PrintPreview
        ActiveWindow.Visible = False    ' <-- This is where the debugger stops execution
        Sheets("Net Graphs").Select

    End Sub

    Tuesday, May 08, 2007 3:51 PM

All replies

  • Posts from the past 5 years suggest re-booting, but noone seems to be sure as to the cause. ln my case it did solve the problem. Still do not know why it happened. -JP
    Tuesday, May 08, 2007 11:59 PM
  • I know this is a very late response to your post, but I think I have the same problem.  It seems to happen to me when I ctrl-brk when a message box is open.  I am not sure if it helps anyting, but I suppose it's good to post it anyway.

     luc

     

    Thursday, December 04, 2008 3:50 PM