I have OLEObjects on an excel workbook that will not "fire" (Trigger an event sometimes). Just to be clear, each button is labelled (for example) as [Name][Number] and I've put a [Name][Number]_Click() event in the appropriate sheet code. The buttons work, but then randomly (it seems) stop working.
One section of code, in particular, does causes this problem.
Sub A_A_Utility_ButtonCreationForHOME_PopulateViewEditItemButton() Dim M As Long Dim I As Long Dim Name As String, NName As String Dim NumItems As Long Range("A3").Value = "=COUNTA(A4:A1000)" 'Count... NumItems = Range("A3").Value Range("A3").Value = "" '=============Delete Buttons================= For Each OLEObject In Sheets("HOME").OLEObjects If Left(OLEObject.Name, 10) = "ItemButton" Then ' if the left letters in the OLEObject are "ItemButton" then.. Name = Right(OLEObject.Name, Len(OLEObject.Name) - 10) ' Take the letter to the right of those 10 letters as call them the variable (string) "Name" Sheets("HOME").OLEObjects("ItemButton" & Name).Delete 'and delete any OLEObject called "ItemButton&Name" (say.. ItemButton5) End If Next '=============Then Add the Buttons Back========= (prevents double creation of buttons) For I = 1 To NumItems 'the rest of this macro replaces the "View/Edit Entries" button removed at the beginning of the delete item sequence Range("A" & 2 * I + 3).Select Dim ItemButtonObj As OLEObject, N% Set ItemButtonObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _ Link:=False, DisplayAsIcon:=False, Left:=ActiveCell.Left + 2, Top:=ActiveCell.Top - 4, _ Width:=1.93 * ActiveCell.Width, Height:=18) 'ActiveCell.Height) ItemButtonObj.Name = "ItemButton" & 2 * I + 3 ItemButtonObj.Object.Caption = "View/Edit Entries" ItemButtonObj.Object.Font.Size = 8 Next I End Sub
When this code is executed, it removes all buttons whose names start with "ItemButton", then it recreates all the just-deleted "ItemButton"s and creates one more. The deleted-and-then-recreated buttons don't work, the new button does (as in clicking them does or does not trigger the _Click() event associated to each button).
I think it has to do with the fact that a deleted button isn't replaced by a newly created button of the same name - that the new buttons don't absorb the same 'link' (?) to the click event.
I've literally been trying to figure this ONE problem out for about 20 hours (because I have a lot of code and I don't want to change something fundamental like moving to a different non-OLEObject), so any help would be appreciated