ComboBox1 is DELETED(!?) when Me.Pictures(1).Delete runs in my macro

  • Thread starter Thread starter Finny388
  • Start date Start date
F

Finny388

My combobox DISAPPEARS when the event handler is triggered!
If I comment out Me.Pictures(1).Delete, it works fine.
If I step through the code - it works fine!!!

I am sure it isn't hidden b/c when I go and create another one, it is
auto-named ComboBox1.

I use that delete line to clear out the existing pic before inserting
another. Why would it delete the combobox too? Is there a better way?

entire code:
Private Sub ComboBox1_Change()
On Error Resume Next
Dim MyPic As IPictureDisp
Me.Pictures(1).Delete
Set MyPic = LoadPicture("I:\Customers\Quadro Catalogue &
Pictures\SAP Pictures\" & Range("D5").Value & ".jpg")
PicPath = "I:\Customers\Quadro Catalogue & Pictures\SAP
Pictures\" & Range("D5").Value & ".jpg"
H = MyPic.Height
W = MyPic.Width
R1 = W / H
Cells(2, 4).Select
Set pic = ActiveSheet.Pictures.Insert(PicPath)
pic.Height = Cells(2, 4).Height
pic.Width = Cells(2, 4).Height * R1
End Sub
 
Put a combo box and a command button on a new worksheet.
Then in the cmdButton_Click event enter:
Debug.Print Me.Picture.count

Fire the code. What do you see in the Immediate window ?

See if that helps.

NickHK
 
Really interesting issue.

Add following code, it's working fine at my end now.
If Me.Pictures(1).Name <> "ComboBox1" Then
Me.Pictures(1).Delete
End If

Hope this helps.
 
Really interesting issue.

Add following code, it's working fine at my end now.
If Me.Pictures(1).Name <> "ComboBox1" Then
Me.Pictures(1).Delete
End If

Hope this helps.
Click to expand...

With Debug.Print Me.Picture(s).count it showed 2 in a brand new
workbook with just a button and a combobox
Back to my orig with button, cbox, and 1 picture and the result is 3!

wtf?

I'll try your method Himani, in a moment
 
With Debug.Print Me.Picture(s).count it showed 2 in a brand new
workbook with just a button and a combobox
Back to my orig with button, cbox, and 1 picture and the result is 3!

wtf?

I'll try your method Himani, in a moment
Click to expand...

So get this:
Private Sub CommandButton1_Click()
Debug.Print Me.Pictures.Count
Debug.Print Me.Pictures(1).Name
Debug.Print Me.Pictures(2).Name
Debug.Print Me.Pictures(3).Name
End Sub

Immediate Window:
3
Picture 3
CommandButton1
ComboBox1

As bizarre as this is, I'm also noting that how things are numbered is
mystery to me. The CommandButton was the most recent entry!
Also, if this is true, why would my delete statement delete both the
pic1 AND the Combobox(pic3) ?

Ran the combobox_change again, with the button there, and now the
button and the picture delete!

I guess I'll just have to loop through Me.Pictures only deleting
Pictures with the word "Picture" in its name. Sheesh!
 
With Debug.Print Me.Picture(s).count it showed 2 in a brand new
workbook with just a button and a combobox
Back to my orig with button, cbox, and 1 picture and the result is 3!

wtf?

I'll try your method Himani, in a moment
Click to expand...

added names to the debugging routine giving
3
ComboBox1
Picture 12
CommandButton1

Why it regards these as Pictures is beyond me.

I have checked the name now and it is noticeably slower:
For i = 1 To Me.Pictures.Count
If Left(Pictures(i).Name, 7) = "Picture" Then
Me.Pictures(i).Delete
End If
Next i

Sheesh!
 
So get this:
Private Sub CommandButton1_Click()
Debug.Print Me.Pictures.Count
Debug.Print Me.Pictures(1).Name
Debug.Print Me.Pictures(2).Name
Debug.Print Me.Pictures(3).Name
End Sub

Immediate Window:
3
Picture 3
CommandButton1
ComboBox1

As bizarre as this is, I'm also noting that how things are numbered is
mystery to me. The CommandButton was the most recent entry!
Also, if this is true, why would my delete statement delete both the
pic1 AND the Combobox(pic3) ?

Ran the combobox_change again, with the button there, and now the
button and the picture delete!

I guess I'll just have to loop through Me.Pictures only deleting
Pictures with the word "Picture" in its name. Sheesh!
Click to expand...

boy posts can be slow to appear, thought the one before last was lost.
 
If you use a newsreader instead of Google, you can connect directly to
the msnews servers. Posts generally show up within a minute or so.
 
Back
Top