Creating a dynamic combo box from an indirect and dynamic source

[thaga87]

Hello,

I have a workbook that I am setting up as an IO list for a PLC. The main sheet has one combo. The combo box gets it's data indirectly depending on what row/column I have clicked on. If I double click in any cell in column J, the combo box appears in that cell and I can select the data which is dependent on the value in column G in that same row (tells me what kind of input/output that pin on the PLC is). This part works fine.

From there, I have column K which is dependent on the entry in column J. K is also linked to a table which makes the data available dynamic. This is the part not working. If I re-create the group not in a table, it works. But I would much prefer not to as the data in K can change as new tech is made.

This is the present code. I stole some of this off the internet, so I don't know exactly why some of this works or not. I am learning as I go! If this isn't possible please let me know!

Thanks in advance!
Tyler

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
Dim lSplit As Long
Dim wb As Workbook
Dim nm As Name
Dim wsNm As Worksheet
Dim rng As Range


Set ws = ActiveSheet
Set wsList = Sheets("Input Valid")
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next


With cboTemp
  .ListFillRange = ""
  .LinkedCell = ""
  .Visible = False
End With


On Error GoTo errHandler


If Target.Validation.Type = 3 Then
  Cancel = True
  Application.EnableEvents = False
  str = Target.Validation.Formula1
  str = Right(str, Len(str) - 1)
      
  'for simple INDIRECT function (English)
  ' e.g. =INDIRECT(B2)
  'will create dependent list of items
  If Left(str, 4) = "INDI" Then
    lSplit = InStr(1, str, "(")
    str = Right(str, Len(str) - lSplit)
    str = Left(str, Len(str) - 1)
    str = Range(str).Value
  End If
  
  With cboTemp
    .Visible = True
    .Left = Target.Left
    .Top = Target.Top
    .Width = Target.Width + 15
    .Height = Target.Height + 5
    .ListFillRange = str
      If .ListFillRange <> str Then
        'for dynamic named ranges
        str = Target.Validation.Formula1
        str = Right(str, Len(str) - 1)
        Set wb = ActiveWorkbook
        Set nm = wb.Names(str)
        Set wsNm = wb.Worksheets _
          (nm.RefersToRange.Parent.Name)
        Set rng = wsNm.Range _
          (nm.RefersToRange.Address)
        .ListFillRange = "'" & wsNm.Name _
              & "'!" & rng.Address
      End If
    .LinkedCell = Target.Address
  End With
  cboTemp.Activate
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