PDA

View Full Version : Execel, OLEObjects (buttons) sometimes not firing, triggering



BobTheBuilde
06-19-2008, 07:39 PM
:motz2: AHH. You would not believe how long I've looked and looked for someone else with this problem but, alas, I am here to ask away on a forum. If anyone can help I will be very very greatful. Know that you'd be making a young man somewhere in the midwest very happy.

Context: This is my first time with VBA. I've taken one collegiate class about programming, but I'm definately a newbie.
Problem: OLEObjects stop firing sometimes (by firing I mean that when they are clicked, an event is not triggered). By "sometimes", I mean I know certain procedures cause the objects to stop firing - other times it seems random.
Details: I have an Excel workbook has quite a few OLEObjects on it for navigation and all-around ease of use for the user. Each OLEObjects has its own, unique name and event (a <Name>_click() event, though many of these click events call the exact same procedure). And so, at all times, even when the OLEObjects aren't firing as they should, I always have correctly labelled OLEObjects and <Name>_click() events that should 'find each other (?)'.
Specific Case: The following code deletes all objects named "ItemButton"&<Name> (this "Name" is a number). Once that is done, it immediately re-creates the deleted OLEObjects and then creates one more (so you end up with 5 if you started with 4 - 1 new and the initial 4 should be the same as before). When this code is run, usually the 4 initial OLEObjects don't fire, whereas the new one will. This isn't always the case, but it's the most consistant example I can give.

Sub A_A_Utility_PrintBox_Populator()
Application.ScreenUpdating = False
Call A_A_Utility_UnlockAllSheets
ActiveWorkbook.Unprotect
Dim M As Long
Dim I As Long 'Hght As Long
Dim Name As String, NName As String
Dim blnMasterValue As Boolean
Range("A3").Value = "=COUNTA(F4:F1000)"
NumberItems = Range("A3").Value
Range("A3").FormulaR1C1 = ""
Sheets("HOME").Unprotect
Sheets("HOME").Select
'=====Delete PrintBoxes===========
For Each OLEObject In ActiveSheet.OLEObjects
If Left(OLEObject.Name, 8) = "PrintBox" Then
Name = Right(OLEObject.Name, Len(OLEObject.Name) - 8)
ActiveSheet.OLEObjects("PrintBox" & Name).Delete
Sheets("Home").Range("C" & Name).FormulaR1C1 = ""
End If
Next
'=================================
For I = 1 To NumberItems + 1
Call A_A_Utility_UnlockAllSheets
NName = "PrintBox" & I + 2
Range("C" & I + 2).Select
Dim PrintBoxObj As OLEObject, N%
Set PrintBoxObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CheckBox.1", _
Link:=False, DisplayAsIcon:=False, Left:=ActiveCell.Left, Top:=ActiveCell.Top, _
Width:=ActiveCell.Width, Height:=ActiveCell.Height)
PrintBoxObj.Name = NName
PrintBoxObj.Object.Caption = ""
If I > 1 Then
PrintBoxObj.LinkedCell = "C" & I + 2
PrintBoxObj.Object.Value = False
Range("C" & I + 2).FormulaR1C1 = "FALSE"
End If
Next I
Sheets("HOME").Select
End Sub
...I hope I used the VBA wrapping thing correctly...

Anyways..

Summary/Hopes/Expectations: If someone could please (!!!:hi:) tell me -
What causes this loss of function (not firing) to happen!? -
If not for this case (if you don't know), then maybe when causes this happen in general so I can research into it.
Perhaps it has to do with this garbage collector I've been hearing about, or maybe it has to do with the sloppy deleting and then recreating of the OLEObjects. I don't know. PLEASE HELP ME!

BobTheBuilde
06-20-2008, 05:35 AM
I put the wrong code up... sorry. Here is the correct code I MEANT to put up...


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

figment
06-20-2008, 07:34 AM
i don't have and example of your sheet so i cant test this, but i cleaned up your code a bit

Sub A_A_Utility_ButtonCreationForHOME_PopulateViewEditItemButton()
Dim I As Long
Dim ItemButtonObj As OLEObject
'=============Delete Buttons=================
For Each ItemButtonObj In Sheets("HOME").OLEObjects
If Left(ItemButtonObj.Name, 10) = "ItemButton" Then ' if the left letters in the OLEObject are "ItemButton" then..
ItemButtonObj.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 = 5 To 2 * Excel.WorksheetFunction.Count("A4:A1000") + 3 Step 2 'the rest of this macro replaces the "View/Edit Entries" button removed at the beginning of the delete item sequence
Set ItemButtonObj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
Link:=False, DisplayAsIcon:=False, Left:=Range("A" & I).Left + 2, Top:=ActiveCell.Top - 4, _
Width:=1.93 * ActiveCell.Width, Height:=18) 'ActiveCell.Height)
ItemButtonObj.Name = "ItemButton" & I
ItemButtonObj.Object.Caption = "View/Edit Entries"
ItemButtonObj.Object.Font.Size = 8
Next I
End Sub

at a guess i would say the problem is in this line

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)
most likely you need to Link the button to something rather then setting Link = False

i am curious as to why your are deleting functioning buttons just to replace them? seems like a waist of time.

although if you want to look for differences between the working buttons and the bad ones then i would step through your code (F8 key) and then use the debug->watch menu to look at a working button object, and compair it to a non working, and see if you can spot a diffrance.

Bob Phillips
06-20-2008, 10:35 AM
Aee you sure they are OLEObjects? If they were they would ALL have unique procedures as the event procedures include the name.

Where did you build these objects from?

BobTheBuilde
06-20-2008, 10:37 AM
I built these objects from code.. I made them by the exact same code used above..

ActiveSheet.OLEObjects(<thename>).Add ... blah blah

Bob Phillips
06-20-2008, 10:51 AM
Where are you tying it to the proceddure code?