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.
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.
www-contextures-com-xlDataVal14-html (website that i can't link because im new here. but i think it is important for my reference)
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
Bookmarks