How to make VBA work across different sheets (Combobox)

[KasperBusk]

Hi guys, im really hoping that some inlightened minds can help me with this problem.

This is my first try at VBA so forgive my ignorance. Im allmost at the finish line - i just need this last problem fixed.

Starting with a short resume. I found this code on the internet and it does exactly what i want it to; i can create a validation list reffering to another column (regular data validation list) and the combobox jumps in and autocompletes and datavalidates simultaneously. It all works flawlessly as long as the combobox and the column that the data-validation refers to stays in the same sheet.

My problem is that the code, and of cause my combobox too, stops working when the datavalidation is referring to a column in another sheet (though still in the same workbook).

My question is: How do i tweak this code so my data-validation can refer to a list/column in another "sheet2" while my combobox continue to work in "sheet1"

The code im using now looks like this:

Option ExplicitPrivate Sub TempCombo_KeyUp(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_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
  On Error GoTo errHandler


If Target.Count > 1 Then GoTo exitHandler


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


  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 = ws.Range(str).Address
      .LinkedCell = Target.Address
    End With
    cboTemp.Activate
  End If


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


End Sub