cflmarketing
New Member
- Joined
- Jun 6, 2014
- Messages
- 11
I have a spreadsheet which is quite simple but has a combo box that auto-fills/selects on the basis of data validation lists. When you double-click on the cell, the DV lists come up, and you can select from them.
I found the code on the internet and it has worked fine until we upgraded to 2016. Now, it auto-fills, but the DV list doesn't come up, so it only auto-fills with the first item on the list which matches the first letter you put in. If you type a second letter it will change to the first item on the list starting with those 2 letters etc.
I have gone back to the original website from where I got the code and have re-done the sample given on the website, but it seems to suffer from the same issue. I am presuming that the code is therefore missing some element to make it work properly in 2016 (I have run my spreadsheet on a computer with 2010 and it does work fine there.) I was wondering if anyone familiar with VBA might be able to look through the code and see if anything jumps out them?
The website is http://www.contextures.com/xlDataVal11.html. But for ease, I am also pasting the code from my spreadsheet below:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Set cboTemp = ws.OLEObjects("TempCombo")
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
Cancel = True
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
'open the drop down list automatically
Me.TempCombo.DropDown
End If
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
Private Sub TempCombo_LostFocus()
With Me.TempCombo
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End Sub
I would be grateful for any insight!!
Thank you very much
I found the code on the internet and it has worked fine until we upgraded to 2016. Now, it auto-fills, but the DV list doesn't come up, so it only auto-fills with the first item on the list which matches the first letter you put in. If you type a second letter it will change to the first item on the list starting with those 2 letters etc.
I have gone back to the original website from where I got the code and have re-done the sample given on the website, but it seems to suffer from the same issue. I am presuming that the code is therefore missing some element to make it work properly in 2016 (I have run my spreadsheet on a computer with 2010 and it does work fine there.) I was wondering if anyone familiar with VBA might be able to look through the code and see if anything jumps out them?
The website is http://www.contextures.com/xlDataVal11.html. But for ease, I am also pasting the code from my spreadsheet below:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Set cboTemp = ws.OLEObjects("TempCombo")
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
Cancel = True
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
'open the drop down list automatically
Me.TempCombo.DropDown
End If
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
Private Sub TempCombo_LostFocus()
With Me.TempCombo
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End Sub
I would be grateful for any insight!!
Thank you very much