I modified the following piece of code (the original combobox code I found
he http://www.contextures.com) to launch a listbox when the user double
clicks a cell which is associated with a datavalidation list. The problem is
that when one first double clicks a cell, selects the values in the listbox
and then moves down several rows and double clicks another cell, the program
always jumps back to the rows where one previously opened the listbox (i.e.
double clicked a cell). The listbox which was opened last is then not
visible at all for the user. Is there any way I could prevent excel from
doing this?

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

Cancel = True
Set cboTemp = ws.OLEObjects("ListBox1")
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 + 30
.Height = Target.Height + 250
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

I am afraid that I don't see that behaviour.

Do you have any other worksheet event code, workbook event code, or
application event code in conjunction?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"HK" wrote in message
...
I modified the following piece of code (the original combobox code I found
he http://www.contextures.com) to launch a listbox when the user double
clicks a cell which is associated with a datavalidation list. The problem
is
that when one first double clicks a cell, selects the values in the
listbox
and then moves down several rows and double clicks another cell, the
program
always jumps back to the rows where one previously opened the listbox
(i.e.
double clicked a cell). The listbox which was opened last is then not
visible at all for the user. Is there any way I could prevent excel from
doing this?

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

Cancel = True
Set cboTemp = ws.OLEObjects("ListBox1")
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 + 30
.Height = Target.Height + 250
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

"Bob Phillips" wrote in message
...
I am afraid that I don't see that behaviour.

Do you have any other worksheet event code, workbook event code, or
application event code in conjunction?

There are no other such codes in the VBA. Could this be then due to some
Excel feature?

HK

My guess would that it has something to do with your making the height of the
listbox as 250 plus the height of the cell. 250 would make it cover around
19 normal height cells. I would try (just for testing) changing the 250 back
to 5 as in the original code and see if it works. then if it works figure
out what is going on that causes the problem when it is 250.

--
Regards,
Tom Ogilvy


"HK" wrote:

"Bob Phillips" wrote in message
...
I am afraid that I don't see that behaviour.

Do you have any other worksheet event code, workbook event code, or
application event code in conjunction?

There are no other such codes in the VBA. Could this be then due to some
Excel feature?

HK

"HK" wrote in message
...
I modified the following piece of code (the original combobox code I found
he http://www.contextures.com) to launch a listbox when the user double
clicks a cell which is associated with a datavalidation list. The problem
is that when one first double clicks a cell, selects the values in the
listbox and then moves down several rows and double clicks another cell,
the program always jumps back to the rows where one previously opened the
listbox (i.e. double clicked a cell). The listbox which was opened last is
then not visible at all for the user. Is there any way I could prevent
excel from doing this?

The problem was in fact in the code. If one removes this bit
cboTemp.Activate, it works ok.

HK