Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox1 is DELETED(!?) when Me.Pictures(1).Delete runs in my macro
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox1 is DELETED(!?) when Me.Pictures(1).Delete runs in my macro
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 "Finny388" wrote in message oups.com... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox1 is DELETED(!?) when Me.Pictures(1).Delete runs in my mac
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. "Finny388" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox1 is DELETED(!?) when Me.Pictures(1).Delete runs in my mac
On Apr 20, 12:42 am, Himani wrote:
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. "Finny388" wrote: 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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox1 is DELETED(!?) when Me.Pictures(1).Delete runs in my mac
On Apr 20, 8:58 am, Finny388 wrote:
On Apr 20, 12:42 am, Himani wrote: 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. "Finny388" wrote: 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 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 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox1 is DELETED(!?) when Me.Pictures(1).Delete runs in my mac
On Apr 20, 8:58 am, Finny388 wrote:
On Apr 20, 12:42 am, Himani wrote: 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. "Finny388" wrote: 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 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 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! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox1 is DELETED(!?) when Me.Pictures(1).Delete runs in my mac
On Apr 20, 9:17 am, Finny388 wrote:
On Apr 20, 8:58 am, Finny388 wrote: On Apr 20, 12:42 am, Himani wrote: 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. "Finny388" wrote: 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 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 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! boy posts can be slow to appear, thought the one before last was lost. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ComboBox1 is DELETED(!?) when Me.Pictures(1).Delete runs in my mac
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. In article om, Finny388 wrote: boy posts can be slow to appear, thought the one before last was lost. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro to delete any control box and pictures | Excel Worksheet Functions | |||
Why is Sheet deleted when Macro runs... | Excel Programming | |||
delete pictures with a macro | Excel Discussion (Misc queries) | |||
How do I delete checkboxes from rows I deleted in a macro? | Excel Discussion (Misc queries) | |||
Macro to delete pictures ? | Excel Worksheet Functions |