Control Toolbox - Combo Box not working

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

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

"sbhayes" <sbhayes@discussions.microsoft.com> wrote in message
news:F6B845F5-2FDE-45AB-BD93-C1CE56B0DE2A@microsoft.com...
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

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" wrote:

> 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
>
> "sbhayes" <sbhayes@discussions.microsoft.com> wrote in message
> news:F6B845F5-2FDE-45AB-BD93-C1CE56B0DE2A@microsoft.com...
> 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
>
>
>
>

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


"sbhayes" <sbhayes@discussions.microsoft.com> wrote in message
news:A33F0A11-5E88-4553-92D3-9E061125A499@microsoft.com...
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" wrote:

> 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
>
> "sbhayes" <sbhayes@discussions.microsoft.com> wrote in message
> news:F6B845F5-2FDE-45AB-BD93-C1CE56B0DE2A@microsoft.com...
> 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
>
>
>
>

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


"sbhayes" <sbhayes@discussions.microsoft.com> wrote in message
news:A33F0A11-5E88-4553-92D3-9E061125A499@microsoft.com...
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" wrote:

> 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
>
> "sbhayes" <sbhayes@discussions.microsoft.com> wrote in message
> news:F6B845F5-2FDE-45AB-BD93-C1CE56B0DE2A@microsoft.com...
> 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
>
>
>
>

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!!!!

"Damon Longworth" wrote:

> 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
>
>
> "sbhayes" <sbhayes@discussions.microsoft.com> wrote in message
> news:A33F0A11-5E88-4553-92D3-9E061125A499@microsoft.com...
> 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" wrote:
>
> > 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
> >
> > "sbhayes" <sbhayes@discussions.microsoft.com> wrote in message
> > news:F6B845F5-2FDE-45AB-BD93-C1CE56B0DE2A@microsoft.com...
> > 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
> >
> >
> >
> >
>
>
>