Hi All
I am rather new to Excel so excuse me if this is a dumb/old question.
I am trying to use the code developed by Contextures.com to link a Combobox to a Dropdown box.
I can get the Combobox to show the data from the table that I have created but as soon as I have copied and pasted the code into the VBA, I double click on a Dropdown box and it is blank??
The sheet with the Dropdown Lists & Combox is named "Test Results" and the sheet with the Table is called "Max Zs values".
I have posted the code and the file attachment below. Let me know if you require any more info.
I have tried looking at the example workbook on the Contextures website but I can't see where I am going wrong.
Can anyone help?
Thanks in advance.
Ben
Option Explicit
' Developed by Contextures Inc.
' www.contextures.com
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
'Hide combo box and move to next cell on Enter and Tab
Select Case KeyCode
Case 9
ActiveCell.Offset(0, 1).Activate
Case 13
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim wsList As Worksheet
Set ws = ActiveSheet
Set wsList = Sheets("Max Zs values")
Application.EnableEvents = False
Application.ScreenUpdating = False
If Application.CutCopyMode Then
'allows copying and pasting on the worksheet
GoTo errHandler
End If
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
.Top = 10
.Left = 10
.Width = 0
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
Application.EnableEvents = False
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 15
.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.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
End Sub
Bookmarks