"Error '424' Object required" when trying to display "Temp.jpg" in an-CodePudding

The Application I am working on Runs in front of the Excel Workbook so the User has NO direct access to the individual Worksheets. The primary area of concern is with the Reports Worksheets where I want to Display the equivalent of a Print Preview of the Worksheet with all the data populated on the Worksheet. I have 4 ComboBoxes configured as DDLs and a Submit Button implementing the functionality of selecting the desired Worksheet, the Named Ranges associated with the respective Worksheets, and populating the appropriate cells on those Worksheets(See Image Below). ALL Variables for Worksheets and Ranges are Declared as Global Variables in the WB_Initialization Module and Set in UserForm_Initialize()!

The Choose Report Type ComboBox Change Event sets the ActiveWorksheet and the remaining 3 ComboBoxes control the values that the Worksheet uses to implement proper selection and display of data, and the Submit populates the Cells on the Worksheet and calls the Function DisplayRange rngToDisplay! I confirmed proper operation of ALL Worksheets before implementing DisplayRange rngToDisplay! The Code for Choose Report Type is as follows:

   Private Sub cmbPropWksts_Change() 'Located on MultiPage1.Page4
   Select Case cmbPropWksts.ListIndex
    Case 0
        wkstSum.Select
        rngRptSum.Select
        Set rngToDisplay = rngRptSum
    Case 1
        wkstDetail.Select
        rngRptDetail.Select
         Set rngToDisplay = rngRptDetail
    Case 2
        wkstCmpYrsElec.Select
        rngRptCmpYrsElect.Select
        Set rngToDisplay = rngRptCmpYrsElect
    Case 3
        wkstCmpYrsGas.Select
        rngRptCmpYrsGas.Select
        Set rngToDisplay = rngRptCmpYrsGas
   End Select

   cmdSubmit.Caption = "Display " & cmbPropWksts.Value

   Call MeasureSelection_Pixels

    If cmbPropWksts.ListIndex > 1 Then
        cmbwkstYrs2.Visible = True
    End If
    
   End Sub

The Submit Button Code is Here:

    
    pID = cmbRptPrpID.Text
    pIndex = cmbRptPrpID.ListIndex   2
    wsYr1 = cmbWkstYrs1.Text
    wsYr2 = cmbwkstYrs2.Text
    
    Select Case cmbPropWksts.ListIndex
        Case 0
            ActiveSheet.Cells(3, "B") = cmbRptPrpID.Text              'Cell B3 of the Summary Worksheet
            ActiveSheet.Cells(3, "Q") = cmbWkstYrs1.Text              'Cell Q3 of the Summary Worksheet
            ActiveSheet.Cells(1, "A") = wsCntrl.Cells(pIndex, "N")    'Cell for the Property's address
        Case 1
            ActiveSheet.Cells(3, "B") = cmbRptPrpID.Text              'Cell B3 of the Summary Worksheet
            ActiveSheet.Cells(3, "Q") = cmbWkstYrs1.Text              'Cell Q3 of the Summary Worksheet
            ActiveSheet.Cells(1, "A") = wsCntrl.Cells(pIndex, "N")    'Cell for the Property's address
        Case 2, 3
            ActiveSheet.Cells(2, "B") = cmbRptPrpID.Text              'Cell B3 of the Summary Worksheet
            ActiveSheet.Cells(2, "C") = cmbWkstYrs1.Text              'Cell Q3 of the Summary Worksheet
            ActiveSheet.Cells(17, "C") = cmbwkstYrs2.Text             'Cell Q17 of the Summary Worksheet
            ActiveSheet.Cells(1, "A") = wsCntrl.Cells(pIndex, "N")    'Cell for the Property's address
    End Select
    
    
    DisplayRange rngToDisplay
    
    End Sub

And finally, DisplayRange Code is here:

        Function DisplayRange(r As Range)
        Dim wsChart As Worksheet
        Dim fname As String
        
        Set wsChart = ThisWorkbook.Worksheets("tmpChart")
        
        fname = ThisWorkbook.Path & "\TempImages\temp.jpg"
        
        r.CopyPicture xlScreen, xlBitmap
        
        With wsChart
        Dim chtObj As ChartObject
        Set chtObj = .ChartObjects.Add(100, 30, 400, 250)
        
        With chtObj
            .Width = r.Width: .Height = r.Height
            .Chart.Paste
            .Chart.Export Filename:=fname, FilterName:="jpg"
            .Delete
        End With
        DoEvents
        End With
        
        imgRpts1.Picture = LoadPicture(fname)
        
        End Function

The Call to the above Function properly generates the Chart on the proper Worksheet as confirmed here: And creates the temp.jpg image here: in the proper Folder here: the Error 424 happens here!

My research indicates that the most common cause of this Error is due to Undeclared/Improperly Declared Varables! I have checked the Varable Scopes, Image Box Name, and the File Paths so apparently I am missing something.

Thanks in advance for the help!

CodePudding user response：

Somewhat as a result of a question from @Ambie, I revisited the Module Code for Function DisplayRange. I remembered a similar problem I had when trying to to move Code from the UserForm to a Module and kept getting a Variable Not Defined Error. I learned two valuable lessons today. First, Make sure to use Option Explicit in EVERY Module. Second I learned how to set up Inter-Module and Module to UserForm communication.

I resolved the issue by add these 2 lines of Code:

   Dim imgRpts1 As Object
   Set imgRpts1 = UserForm1.imgRpts1

Here is the final Function Code:

     Option Explicit
            
    Function DisplayRange(r As Range)
        Dim wsChart As Worksheet
        Dim fname As String
        Dim imgRpts1 As Object
        Set imgRpts1 = UserForm1.imgRpts1
        Set wsChart = ThisWorkbook.Worksheets("tmpChart")
        fname = ThisWorkbook.Path & "\TempImages\temp.jpg"
        
        r.CopyPicture xlScreen, xlBitmap
        
        With wsChart
        Dim chtObj As ChartObject
        Set chtObj = .ChartObjects.Add(100, 30, 400, 250)
        
        With chtObj
            .Width = r.Width: .Height = r.Height
            .Chart.Paste
            .Chart.Export Filename:=fname, FilterName:="jpg"
            .Delete
        End With
        DoEvents
        End With
        
        imgRpts1.Picture = LoadPicture(fname)
    
    End Function

AND IT WORKS! [![AND IT WORKS!][1]][1] [1]:https://i.stack.imgur.com/3DfCD.jpg