Creating a dynamic combo box from an indirect and dynamic source

thaga87

New Member
Joined
Feb 11, 2019
Messages
1
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

Code:
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
 

Some videos you may like

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Watch MrExcel Video

Forum statistics

Threads
1,123,087
Messages
5,599,654
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top