Delete embedded listbox

MODERATOR NOTICE: This topic has also been posted on other sites and may already have an answer elsewhere. Please take this into consideration when answering this question

1. Quote

   This is one of the ways I tried to delete the listbox
   

   Code

   1. 'Remove current listbox object
   2. With objOLE .Delete
   3. End With

   I can't select the listbox in Developer mode and it doesn't delete using the shapes delete method.Further it doesn't show up in the objects pane or with the object select.
   
   Also, I really need vba code to delete it as this will be deleted and remade any time the code runs.
   
   Here is the complete code:
   
   

   Code

   1. Sub GetComponents(wsName As Worksheet, Analysis As Variant)
   2. Dim Conn1 As New ADODB.Connection
   3. Dim Cmd1 As New ADODB.Command
   4. Dim Errs1 As Errors
   5. Dim Rs1 As New ADODB.Recordset
   6. Dim i As Integer
   7. Dim AccessConnect As String
   8. Dim x As String
   9. Dim ORACLE_USER_NAME As String
   10. Dim ORACLE_PASSWORD As String
   11. Dim sql As String
   12. Dim myPort As String
   13. Dim myHost As String
   14. Dim OracleSID As String
   15. Dim SERVICE_NAME As String
   16. Dim rcnt As Integer
   17. Dim lstHeight As Integer
   18. Dim objOLE As OLEObject, objListBox As msforms.ListBox
   19. Dim obj As OLEObject
   20. Dim Records As Variant
   22. ' Error Handling Variables
   23. Dim errLoop As Error
   24. Dim strTmp As String
   26. With Conn1 .CursorLocation = adUseClient
   27. End With
   29. 'Set Sheet1 RowHeight
   30. wsName.Rows("1:1").RowHeight = 24
   32. x = wsName.Range("C2")
   34. ORACLE_USER_NAME = "xxxxxxxxxxxxxx"
   35. ORACLE_PASSWORD = "xxxxxxxxxxxx"
   36. myPort = "1521"
   37. myHost = "xxxxxxxx"
   38. OracleSID = "10.6.4.142"
   39. SERVICE_NAME = "xxxxxxxxxxx"
   41. 'Connection String
   42. AccessConnect = "Provider=OraOLEDB.Oracle;" & _
   43. "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(Host=" & myHost & ")(Port=1521))(CONNECT_DATA=" & _
   44. " (SERVICE_NAME=" & SERVICE_NAME & ")));User ID=" & ORACLE_USER_NAME & ";Password=" & ORACLE_PASSWORD
   46. On Error GoTo AdoError ' Full Error Handling which traverses Connection object
   48. Conn1.ConnectionString = AccessConnect
   49. Conn1.Open
   50. Cmd1.ActiveConnection = Conn1
   52. sql = ""
   53. sql = sql & "SELECT DISTINCT LWPROD.COMPONENT.NAME"
   54. sql = sql & " FROM LWPROD.COMPONENT"
   55. sql = sql & " INNER JOIN lwprod.ANALYSIS ON COMPONENT.ANALYSIS = ANALYSIS.NAME"
   56. sql = sql & " WHERE ANALYSIS.NAME = '" & Analysis & "'"
   57. sql = sql & " ORDER BY 1"
   59. Cmd1.CommandText = sql
   61. Set Rs1 = Cmd1.Execute
   62. 'Rs1.CursorLocation = adUseServer
   63. Rs1.MoveFirst
   64. Do Until Rs1.EOF rcnt = rcnt + 1 Rs1.MoveNext
   65. Loop
   67. rcnt = rcnt 'Rs1.RecordCount
   68. lstHeight = rcnt * 17
   69. Rs1.MoveFirst
   70. wsName.Range("A1:A" & rcnt).CopyFromRecordset Rs1
   72. 'Remove current listbox object
   73. With objOLE .Delete
   74. End With
   76. Set objOLE = wsName.OLEObjects.Add(ClassType:="Forms.ListBox.1", Link:=False, _
   77. DisplayAsIcon:=False, Left:=0, Top:=0, Width:=150, Height:=lstHeight) ' set some properties
   78. With objOLE ' these are properties of the container OLEObject, not the Listbox itself .Name = "lstComponents" .ListFillRange = "A1:A9" Set objListBox = .Object ' toggle visibility to ensure the control is clickable .Visible = False .Visible = True
   79. End With
   80. ' now we can set the listbox-specific properties
   81. With objListBox .MultiSelect = fmMultiSelectMulti .MatchEntry = fmMatchEntryComplete
   82. End With
   84. 'Close Connections
   85. Rs1.Close
   86. Conn1.Close
   87. Conn1.ConnectionString = ""
   89. Done:
   90. Set Rs1 = Nothing
   91. Set Cmd1 = Nothing
   92. Set Conn1 = Nothing
   94. 'Prompt
   95. MsgBox " Data Returned "
   96. Exit Sub
   98. AdoError:
   99. 'i = 1
   100. On Error Resume Next
   102. ' Enumerate Errors collection and display properties of
   103. ' each Error object (if Errors Collection is filled out)
   104. Set Errs1 = Conn1.Errors
   105. For Each errLoop In Errs1 With errLoop strTmp = strTmp & vbCrLf & "ADO Error # " & i & ":" strTmp = strTmp & vbCrLf & " ADO Error # " & .Number strTmp = strTmp & vbCrLf & " Description " & .Description strTmp = strTmp & vbCrLf & " Source " & .Source i = i + 1 End With
   106. Next
   107. End Sub

   Display More

   If there's a better way to add, fill and delete a list box I would gladly change my code to get this working.
   
   Thanks,
   Jeff

   Display More

   [Blocked Image: http://www.vbaexpress.com/forum/clear.gif] Edit Post   [Blocked Image: http://www.vbaexpress.com/forum/clear.gif] Reply   [Blocked Image: http://www.vbaexpress.com/forum/clear.gif] Reply With Quote   [Blocked Image: http://www.vbaexpress.com/forum/images/buttons/multiquote_40b.png]