Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - VBA Code Problem
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - VBA Code Problem
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? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - VBA Code Problem
Sorry to ask...
Does the OLEObject exist on ws? Is its name spelled correctly? Speaking of typos... -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - VBA Code Problem
hi,
verify that this object exists on the sheet ActiveSheet.OLEObjects("TempCombo").Select -- isabelle Le 2011-09-19 17:02, SkippyPB a écrit : 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel 2003 - VBA Code Problem
On Mon, 19 Sep 2011 17:30:14 -0400, GS wrote:
Sorry to ask... Does the OLEObject exist on ws? 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem with VBA code not running in Excel 2003 | Excel Programming | |||
Problem running VBA 2000 code in Excel 2003 | Excel Programming | |||
Excel 2003 code, problem in Excel 97 | Excel Discussion (Misc queries) | |||
Problem with VBA code written in Excel 2002 working in Office 2003 | Excel Programming | |||
Problem Running Code in Excel 2003 | Excel Programming |