Combo Box Data Validation Macro

[samuelimtech]

Hi all,
Ive pinched the follwing macro to. it generates a combo box over a DV cell and essentially pinches its data so the input is a little bit user firndlier.
the macro works fine when both the dropdown and its source are on the same page but when theyre not it doesnt work.
can anyone spot the the issue?
FYI the macro is in the sheet with the drop down not the source.

Private Sub Worksheet_beforerightclick(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 = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown

errHandler:
Application.EnableEvents = True
Exit Sub
End If
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
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If
errHandler:
Application.EnableEvents = True
Exit Sub
End Sub
'====================================
'Optional code to move to next cell if Tab or Enter are pressed
'from code by Ted Lanham
'***NOTE: if KeyDown causes problems, change to KeyUp
Private Sub TempCombo_KeyDown(ByVal _
KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
Select Case KeyCode
Case 9 'Tab
ActiveCell.Offset(0, 1).Activate
Case 13 'Enter
ActiveCell.Offset(1, 0).Activate
Case Else
'do nothing
End Select
End Sub


thanks for any help

[p45cal]

try changing:
.ListFillRange = ws.Range(str).Addressto:
.ListFillRange = str

[samuelimtech]

Thank YOu that has certainly gotten me a little closer, this works when the list is a straight list except its not, its a formula (=OFFSET(Projects!$D$3,0,0,COUNTA(Projects!D:D)-1)) it works out how long the list is which defines how large the range for the list in the DV should be. no idea why but its not working

[p45cal]

Thank YOu that has certainly gotten me a little closer, this works when the list is a straight list except its not, its a formula (=OFFSET(Projects!$D$3,0,0,COUNTA(Projects!D:D)-1)) it works out how long the list is which defines how large the range for the list in the DV should be. no idea why but its not working

Try:
Private Sub Worksheet_beforerightclick(ByVal Target As Range, Cancel As Boolean)
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 Resume Next
ActiveWorkbook.Names("DVList").Delete
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 into a Name:
ActiveWorkbook.Names.Add Name:="DVList", RefersTo:=Target.Validation.Formula1
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 = "=DVList"
.LinkedCell = Target.Address
End With
cboTemp.Activate
'open the drop down list automatically
Me.TempCombo.DropDown

errHandler:
Application.EnableEvents = True
Exit Sub
End If
End Sub

[samuelimtech]

Thank You very much, it works a treat, now Im gunna sit and work out why what you did works :)

[bglumac]

Did your Combo filled the drop down list from cell below?
I can't manage it to work with this exact code :(

Second, dependent dropdown list is only showing what is currently in the dropdown, not the entire list...