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!
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!