We have a macro-enabled template which has been working fine on all machines in the office for ages. Recently, on certain machines only, it has produced "Run-time error 1004: Unable to get the Object property of the OLEObject class."
It works on one machine with Windows 7 Professional installed running the Home & Business 2013 version of Excel. It does not work on another Windows 7 Pro machine running the Professional Plus 2013 version of Excel.
The code which seems to be producing the error:
Option Explicit
Dim ComboBoxes() As New ITEM_COMBOBOXES
Sub InitializeBoxClass()
Dim Obj As OLEObject
Dim ComboBox_Count As Integer
For Each Obj In PO_CREATION_WS.OLEObjects
If TypeName(Obj.Object) = "ComboBox" Then 'DEBUGGER HIGHLIGHTS THIS LINE
ComboBox_Count = ComboBox_Count + 1
ReDim Preserve ComboBoxes(1 To ComboBox_Count)
Set ComboBoxes(ComboBox_Count).ITEM_COMBOBOXES = Obj.Object
Obj.ListFillRange = "ITEM_LIST"
End If
Next Obj
End Sub
I have tried referencing the OLEObjects by their index numbers instead of as a collection. This produces another error (not the same error as before). I have tried activating each OLEObject prior to retrieving its properties; another error code. I have tried adjusting the security settings in many different ways with no luck.
Does anyone have any idea what went wrong?
(Just in case, here is the ITEM_COMBOBOXES class)
Option Explicit
Public WithEvents ITEM_COMBOBOXES As MSForms.ComboBox
Private Sub ITEM_COMBOBOXES_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
With ActiveSheet
Select Case KeyCode
Case vbKeyTab
If Shift = 1 Then 'SHIFT PRESSED, SHIFT TAB MOVES BACKWARD
If Me.ITEM_COMBOBOXES.Index = 3 Then 'FIRST BOX SELECTED
.OLEObjects(.OLEObjects.Count).Activate 'GO TO END
Else 'NOT FIRST BOX, MOVE BACK ONE
.OLEObjects(Me.ITEM_COMBOBOXES.Index - 1).Activate
End If
Else 'NO SHIFT KEY, TAB MOVES FORWARD
If Me.ITEM_COMBOBOXES.Index = .OLEObjects.Count Then 'LAST BOX SELECTED
.OLEObjects(3).Activate 'GO TO TOP
Else 'NOT LAST BOX, MOVE FORWARD ONE
.OLEObjects(Me.ITEM_COMBOBOXES.Index + 1).Activate
End If
End If
Case vbKeyLeft
Range("A" & Me.ITEM_COMBOBOXES.Index - 1).Activate
Case vbKeyRight
Range("C" & Me.ITEM_COMBOBOXES.Index - 1).Activate
Case vbKeyDelete
Me.ITEM_COMBOBOXES.Value = ""
End Select
End With
End Sub
Bookmarks