Error Message - Run-time error ‘91’: Object Variable or With Block variable not set

[abittolo]

Hello everybody,
I'm building a quite complex Excel model - complex at least for me.

Objects involved are basically 3:
- Worksheet containing data;
- Userform1 (UFSchedaNavigazione) where I have the list of data contained in Worksheet;
- Userform2 (UFImmagini) where I have a set of pictures whose names contain the row of the related data in Worksheet;
- Userform 3 (UFVisualizzaGiocatore) where I want to display after selection in UFSchedaNavigazione data contained in Worksheet plus image from Userform (UFImmagini).

But, when I doubleclick on the item in listbox of UFSchedaNavigazione, I receive the message "Run-time error ‘91’: Object Variable or With Block variable not set".

Here below the code contained in Userform UFScheda Navigazione:

Private Sub LstBoxRicercaGiocatore_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

For i = 0 To LstBoxRicercaGiocatore.ListCount - 1
If LstBoxRicercaGiocatore.Selected(i) = True Then
'set the listbox column
ID = LstBoxRicercaGiocatore.List(i, 0)
IDVisualizza = ID
End If
Next i
UFVisualizzaGiocatore.Show
End Sub

While, here below the code contained in UFVisualizzaGiocatore:

Private Sub UserForm_Initialize()
Dim rngID As Range
Dim X As String
Dim objPic As IPictureDisp
Dim shp As Shape
Dim pic As Shape
Dim Row As Long
Dim ImgRiga As String
Dim ImgCarica As Image

Me.Image1.BackColor = RGB(4, 34, 46)
Me.LblTitolo.BackColor = RGB(4, 34, 46)

Me.TxtVID = IDVisualizza

Worksheets(2).Activate
LastRow = Worksheets(2).Range("A" & Rows.Count).End(xlUp).Row

Set rngNome = Worksheets(2).Range("A2:A" & LastRow)
For Each rngID In rngNome
If rngID.Value = IDVisualizza Then
Row = rngID.Row
Me.TxtVNome.Value = rngID.Offset(, 1).Value
Me.TxtVCognome.Value = rngID.Offset(, 2).Value
Me.TxtVAnnoNascita.Value = rngID.Offset(, 3).Value
End If
ImgRiga = "Img" & Row & ".Picture"
ImgCarica = "UFImmagini." & ImgRiga
UFImmagini.Hide
Me.ImgVGiocatore.Picture = ImgCarica
Next rngID

End Sub

As said, when I doubleclick on item of the Listbox contained in UFSchedaNavigazione, I get the error mesage. Any clue how I can sort this out?

Thanks,
A

 

[Norie]

Andre

What exactly are you trying to do?

Are you trying to load images that are stored on a worksheet into image controls on a userform?

 

[abittolo]

Dear Norie,
thanks for your patience.
Basically I'm associating picture to a single data through speaking code and when data are retrieved in the userform I would like to recall the associated picture as well.
Now, pictures reside both on excel spreadsheet (but I cannot load them simply calling "Shapes" objects) and on a dedicated userform where everytime I'm storing the pictures that the user is inserting.

Hope to have been clear, in case feel free to ask. If you want I can even attach the whole workbook.

Thanks,
Andre

 

[Norie]

Andre

The only easy way I can think of to load images that are stored on a worksheet into an image control on a userform would be to store the images in image controls on the userform.

If you did that you could use code like this.

Dim pic As Object

    Set pic = Sheets("Sheet1").OLEObjects("Image1")
    Me.Image1.Picture = pic.Object.Picture

 

[abittolo]

Dear Norie,
thanks a lot. I've tried your suggestion. Unfortunately the system is giving me again an error: "Object doesn't support this property or method" at the line:

[COLOR=#333333]Set pic = Sheets("Sheet1").OLEObjects("Image1")[/COLOR]

Can it be related to type of "Image1" (in my case "Picture 2")?

Thanks,
Andre

 

[Norie]

Andre

The method I suggested above will only work if you first insert ActiveX image controls and then load the images you want into them.

If you've inserted a picture simply using Insert>Picture then the only way I know of doing what you want would involve using the Windows API.

See PastePicture at this link.

 

[abittolo]

Thanks Norie.
How can I insert ActiveX Image? Is there any setting to be marked in "Tools" menu?
In fact when I open the excel I get this message: "This application is about to initialize ActiveX controls that might be unsafe....", but when I open VBE the Toolbox has only generic "Controls"...

Thanks,
Andre

 

[Norie]

Andre

To insert ActiveX controls you would need to make the Developer tab visible.

When you do that there's a section called Controls with a dropdown menu named Insert where you'll find both ActiveX and Forms controls.

PS You could also add the Insert menu to the QAT without displaying the Developer tab.

 

[abittolo]

Hi Norie,
I was coming to the same conclusion while you were typing the reply. Thanks.
Now my issue is that the picture are inserted in the cell by VBA code. I'm reporting it here:

aleft = Worksheets(2).Cells(iRR, 31).Leftatop = Worksheets(2).Cells(iRR, 31).Top
h = Worksheets(2).Cells(iRR, 31).RowHeight
w = Worksheets(2).Cells(iRR, 31).Width
Address = Me.TxtPath
Worksheets(2).Shapes.AddPicture Address, False, True, aleft, atop, w, h

How should I change the code to make sure the picture is being saved as an "ActiveX Image" so that I could apply your code?

Thanks again for your patience.

Andre

 

[abittolo]

Hi Norie,
sorry to bother you.
Now, I have create a Picture on worksheets - as OLEObject.
I've then created an Userform, where I inserted "Image 1" control. I've added your piece of code, but it retrieves me always the message "Object doesn't support this property or method".

Andre