Results 1 to 1 of 1

ActiveX Combobox VBA not working on Cascading Data Validation

Threaded View

  1. #1
    Registered User
    Join Date
    08-25-2016
    Location
    Calgary, Canada
    MS-Off Ver
    2010
    Posts
    4

    ActiveX Combobox VBA not working on Cascading Data Validation

    Hello,

    I've posted a similar question recently but now I'm facing one last specific problem which now doesn't match my original post title... I hope someone can help me with this.

    On my attached file "License Details" tab, cells B32 and down have cascading data validation with below formula.
    =OFFSET(Brands!$B$1,MATCH(A32,Brands!$B:$B,0)-1,1,COUNTIF(Brands!$B:$B,A32),1)

    Although I have a separate list on tab "Brands," the following code that I'm using for dropdown list formatting isn't working for this column...
    I'm assuming this is because it's not a simple range but a dependent, but I don't know how to make the VBA code to apply to this as well.

    Any input is much appreciated.

    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 Tgt As Range
    Dim TgtMrg As Range
    Dim c As Range
    Dim TgtW As Double
    Dim AddW As Long
    Dim AddH As Long
    
    Set ws = ActiveSheet
    On Error Resume Next
    'extra width to cover drop down arrow
    AddW = 15
    'extra height to cover cell
    AddH = 5
    
    If Target.Rows.Count > 1 Then GoTo exitHandler
    
    Set Tgt = Target.Cells(1, 1)
    Set TgtMrg = Tgt.MergeArea
    On Error GoTo errHandler
    
      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
    
      On Error GoTo errHandler
      If Tgt.Validation.Type = 3 Then
        Application.EnableEvents = False
        If Not TgtMrg Is Nothing Then
          'get total width of merged cells
          TgtW = 0
          For Each c In TgtMrg.Cells
            TgtW = TgtW + c.Width
          Next c
        End If
        
        str = Tgt.Validation.Formula1
        str = Right(str, Len(str) - 1)
        With cboTemp
          .Visible = True
          .Left = Tgt.Left
          .Top = Tgt.Top
          If TgtW <> 0 Then
            'use total width for merged cells
            .Width = TgtW + AddW
          Else
            .Width = Tgt.Width + AddW
          End If
          .Height = Tgt.Height + AddH
          .ListFillRange = str
          .LinkedCell = Tgt.Address
        End With
        cboTemp.Activate
        Me.TempCombo.DropDown
      End If
    
    exitHandler:
      Application.EnableEvents = True
      Application.ScreenUpdating = True
      Exit Sub
    errHandler:
      Resume exitHandler
    
    End Sub
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. ActiveX Combo Box and VBA code not working on multiple Data Validation Dropdown Lists
    By ringonohitorigoto in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-29-2016, 04:05 PM
  2. Replies: 5
    Last Post: 07-28-2014, 11:53 AM
  3. Validation or ComboBox ActiveX/Form Control...
    By brucemc777 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-20-2014, 01:10 PM
  4. [SOLVED] How do I change properties of activex combobox that is linked data validation list?
    By Chris* in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 01-13-2014, 07:03 AM
  5. How to fill an activex combobox with a data validation list?
    By Sape in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 10-03-2013, 04:45 PM
  6. ActiveX ComboBox Completely Stops Working
    By daedelous00 in forum Excel General
    Replies: 1
    Last Post: 01-25-2013, 11:00 AM
  7. Cascading Data Validation -eliminate duplicates
    By coachcr in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 10-15-2008, 11:39 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1