dcsimg
Results 1 to 2 of 2

Thread: [excel 365] autocomplete drop down that runs runs additional code based on value

  1. #1

    Thread Starter
    New Member
    Join Date
    Nov 2018
    Posts
    9

    [excel 365] autocomplete drop down that runs runs additional code based on value

    Hello,

    So i am trying to make a part list that you can use a drop down with the part number, or the description. that will auto fill the other side with the correct matching thing. I have that part of my code working good. This is that code.

    Code:
    Option Explicit
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim prt As Range, rng As Range
    Set prt = Target.Parent.Range("a6:n40")                             'Set Target Range to watch
        Application.ScreenUpdating = False
        'wspartlist.Unprotect Password:="stlFRAC"
        On Error GoTo errHandler
        If Target.Count > 1 Then GoTo exitHandler                      'only watch single cell changes
        If Intersect(Target, prt) Is Nothing Then GoTo exitHandler     'Only look at that range
        If Target.Address = Target.MergeArea(1).Address Then            'Watch only the first column of the merged rows
            For Each rng In Target
                Select Case rng.Column
                    Case Is = 1, 8
                        If Not IsError(Application.match(rng.MergeArea(1).Value, wsPartList.Range("partno"), 0)) Then
                            Application.EnableEvents = False
                            rng.Offset(0, 1).Formula = "=index(tblPart,match(" & rng.Cells(1).Address & ",partNo,0),2)"
                            rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value
                            Application.EnableEvents = True
                        End If
                    Case Is = 3, 10
                        If Not IsError(Application.match(rng.MergeArea(1).Value, wsPartList.Range("partdesc"), 0)) Then
                            Application.EnableEvents = False
                            rng.Offset(0, -2).Formula = "=index(tblPart,match(" & rng.Cells(1).Address & ",partDesc,0),1)"
                            rng.Offset(0, -2).Formula = rng.Offset(0, -2).Value
                            Application.EnableEvents = True
                        End If
                End Select
            Next rng
        End If
        'wspartlist.Protect Password:="stlFRAC"
        Application.ScreenUpdating = True
        Application.EnableEvents = True
    Exit Sub
    
    exitHandler:
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    Exit Sub
    
    errHandler:
        MsgBox "error" & " " & Err.Number
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End Sub
    Then I wanted to add a autocomplete thing. so if someone starts typing it will help the user find the right part. I found this on another website and thought maybe I could have both sub's in the working, it works on selection change, mine works on value change. Although they have a double click version i haven't tried. Below is the code.

    https://www.contextures.com/xlDataVal14.html

    Code:
    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 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
    When I use the auto complete however, my value change code doesn't fire. Is there a better way?

    Thanks

  2. #2
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,512

    Re: [excel 365] autocomplete drop down that runs runs additional code based on value

    you could try moving the value change code to a non-event driven procedure, you could then call that procedure from either the worksheet_change or the worksheet_selectionchange procedures, pass appropriate values for the target
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

Posting Permissions

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



Featured


Click Here to Expand Forum to Full Width