H
HK
I modified the following piece of code (the original combobox code I found
here: 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
here: 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