Control Toolbox - Combo Box not working

[sbhayes]

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

 

[Damon Longworth]

Try using a named range. This is untested, but works in other designs.

--
Damon Longworth

2006 East Coast Excel User Conference
April 19/21st, 2006
Holiday Inn, Boardwalk
Atlantic City, New Jersey
Early Bird Registration Now Open!!
www.ExcelUserConference.com

2006 UK Excel User Conference
Summer, 2006
London, England

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

 

[sbhayes]

I have created 2 differnet name ranges
NurseList "=Staff!$A$1:$A$65"
and
Nurse "=OFFSET(Staff!$A$1,0,0,COUNTA(Staff!$A:$A),1)"

I selected my cells, then gone to Define- Data Validation - List and
inputted Nurse(I was able to get to work, until I save the worksheet), and
then I tried NurseList and I did not work at all. The Data Validation works
great, but when I double click for the combo box nothing comes up.

Susan

 

[Damon Longworth]

Your problems with Nurse may be the dynamic range name. NurseList does not
work after the save?

--
Damon Longworth

2006 East Coast Excel User Conference
April 19/21st, 2006
Holiday Inn, Boardwalk
Atlantic City, New Jersey
Early Bird Registration Now Open!!
www.ExcelUserConference.com

2006 UK Excel User Conference
Summer, 2006
London, England


I have created 2 differnet name ranges
NurseList "=Staff!$A$1:$A$65"
and
Nurse "=OFFSET(Staff!$A$1,0,0,COUNTA(Staff!$A:$A),1)"

I selected my cells, then gone to Define- Data Validation - List and
inputted Nurse(I was able to get to work, until I save the worksheet), and
then I tried NurseList and I did not work at all. The Data Validation works
great, but when I double click for the combo box nothing comes up.

Susan

 

[sbhayes]

I was finally able to get it to work both ways..... I had to use the
NurseList, because for some reason the Nurse
"=OFFSET(Staff!$A$1,0,0,COUNTA(Staff!$A:$A),1)"
would show all users on the list except the last 3 or 4.

Thanks for you help!!!!