Validation in Combo From other WorkBook

[skaswani]

Dear All,
i am using Ms office 2003

i was reading site

http://www.contextures.com/xlDataVal11.html


=== modified code ========

Option Explicit

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
Dim oXl As Application


Set oXl = CreateObject("Excel.Application")
oXl.Visible = False
oXl.Workbooks.Open ("C:\import_data.xls")
Set wsList = Sheets("DataList")
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

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

what i want to is, i want to us Another WorkBook and use the values to Combo box


can anyone help me pls

error : Subscript out of range

Set wsList = Sheets("DataList")


import_data.xls has this worksheet

Thanks

[Hack]

If "DataList" is in a different workbook then preface the whole thing with the workbook name.

[skaswani]

changed
====

Set wsList = oXl.Sheets("DataList")

===

now error on

Set cboTemp = ws.OLEObjects("TempCombo")


error:
Object variable or with block variable not set

[zaza]

You haven't set ws to be anything. Notice how in the SelectionChange sub it has been set to ActiveSheet...?