Excel 2003 - VBA Code Problem

[SkippyPB]

I have the following VBA code in two different Excel Workbooks. Both
are Excel 2003. It works fine in one but dies immediately in the
other. Here's the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = False

If Application.CutCopyMode Then
'allows copying and pasting on the worksheet
GoTo errHandler
End If

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

errHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

End Sub

Debugger shows it is stopping on the Set cboTemp = statement. The
error message is:

Run Time Error '1004':
Method 'OLEObjects' of object '_Worksheet' failed

I have looked at everything I can think to look at and compared
everything between the two workbooks and cannot determine what is
missing or what is wrong. Can someone please point out what might be
wrong?

Thanks.


Steve

 

[GS]

SkippyPB explained on 9/19/2011 :

I have the following VBA code in two different Excel Workbooks. Both
are Excel 2003. It works fine in one but dies immediately in the
other. Here's the code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Application.EnableEvents = False
Application.ScreenUpdating = False

If Application.CutCopyMode Then
'allows copying and pasting on the worksheet
GoTo errHandler
End If

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With

errHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub

End Sub

Debugger shows it is stopping on the Set cboTemp = statement. The
error message is:

Run Time Error '1004':
Method 'OLEObjects' of object '_Worksheet' failed

I have looked at everything I can think to look at and compared
everything between the two workbooks and cannot determine what is
missing or what is wrong. Can someone please point out what might be
wrong?

Thanks.


Steve

Sorry to ask...

Does the OLEObject exist on ws?
Is its name speeled correctly?

 

[GS]

Sorry to ask...

Does the OLEObject exist on ws?

Is its name spelled correctly?

Speaking of typos...

 

[isabelle]

hi,

verify that this object exists on the sheet
ActiveSheet.OLEObjects("TempCombo").Select

--
isabelle



Le 2011-09-19 17:02, SkippyPB a écrit :

 

[SkippyPB]

Is its name spelled correctly?

Speaking of typos...

I hate when that happens! There are two other subs that are the same
in both workbooks. One of them should create the object. Here is
that code:

Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'Hide combo box and move to next cell on Enter and Tab
Select Case KeyCode
Case 9
ActiveCell.Offset(0, 1).Activate
Case 13
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select

End Sub

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel
As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("ValidationLists")
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

And yes, the sheet "ValidationLists" does exist in both Workbooks.

Thanks.

Steve