Need to create a staffing sheet with combo boxes, I would like to have the
employees names on another sheet so they can be updated easily. I have tried
to input a control toolbox- combo box into a Excel worksheet.

I have tried to get the sample at
http://www.contextures.com/xlDataVal11.html to work, I've followed the
instructions, but I must be missing something. I am able to get the combo box
to
work if the list is on the same page, but not on the separate worksheet.

My combo box is called "NurseCombo" and the employess are listed on a
worksheet called "Staff". I can get the combo box to work 1 time, I save the
worksheet and it will not work again. (Code listed below)

Any idea what I might be missing?

thanks in advance for your help,
Sbhayes....


'==========================
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("Staff")

Cancel = True
Set cboTemp = ws.OLEObjects("NurseCombo")
On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.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 = True

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

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub