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]

Where in the code do you get the error?

 

[abittolo]

Hi Norie, thanks for your attention.
When I doubleclick on the item in the listbox I get the message.
Then, if I click on “Debug”, Excel highlights me the line “UFVisualizzaGiocatore.Show”.

A

 

[Fluff]

In the VBE click on Tools > Options > General > Select "Break in class module" > OK.
The run the code again & what line gets highlighted?

 

[abittolo]

Thanks Fluff.
Done it. Now the highlighted line is:

ImgCarica = "UFImmagini." & ImgRiga

I then checked a little bit in the code and found out that Row variable at the beginning of the "If" statement is not properly valued, and becomes equal to "0" in the line above:

ImgRiga = "Img" & Row & ".Picture"

while, for example, it was supposed to be 47 in this case.

Any suggestion?

A

 

[Fluff]

Firstly change the name of the variable "Row" to something else. It's best to avoid using VBA keywords as variables, as it can cause problems.
If that does not solve the problem report back

 

[abittolo]

Hi Fluff,
I've done what you suggested and I reworked a little bit the code to get the right value of the row.
Now this is how the code is:

Me.TxtVID = IDVisualizza

Worksheets(2).Activate
rngID = 2
X = Worksheets(2).Cells(rngID, 1).Value


Do While Not X = IDVisualizza
rngID = rngID + 1
X = Worksheets(2).Cells(rngID, 1).Value
Loop
UltimaRiga = rngID


Me.TxtVID = Worksheets(2).Cells(rngID, 1).Value


ImgRiga = "Img" & rngID & ".Picture"
ImgCarica = "UFImmagini." & ImgRiga
Me.ImgVGiocatore.Picture = ImgCarica

Now, ImgRiga is properly filled with the right value ("Img47.Picture"), but ImgCarica is not valued (it is declared as Object). As a result the following line it is not executed.

Can you give an additional suggestion on how I could get it done?

Thanks,
A

 

[Fluff]

I've never worked with pictures, so am unable to help any further.

 

[Norie]

'Img47.Picture' is only a string, you can't set the Picture property of an image control, e.g. ImgVGiocatore, with that.

 

[abittolo]

Dear Norie,
first of all thanks. I don't want to be disrespectful but I see by myself that the method I'm using unfortunately is not working. And this is quite frustrating. That's why I have posted this thread to you "Gurus" of Excel/VBA to get your support.

Please consider that same pictures are reported into the spreadsheets with speaking-codes (e.g. Img47), but again, I tried but I'm not able to load them into Image box on the userform at runtime.

Can you support? If you want I can also share the workbook here.

Thanks,
Andre