Hi Culprit79,
You can achieve that using a userform and a hook into the Selection_Change event.
This code will display a userform wjhen you select a cell in column A of sheet 2, and give a drop down box of candidates from sheet 1 column A (rows 2 onwards)
To install:
1) Create a Userform, name it "FSmartTag"
2) Create the following controls in the userform
- A Text box named "txtSearch"
- A combo Box named "cboTags"
- A command button named "btnCancel" with the caption "Cancel"
- A command button named "btnOK" with the caption "OK"
3) Place this code in the userform:
Code:
Option Explicit
Public OK As Boolean
Public SmartTagCandidateList As Variant
Private Sub cboTags_Change()
btnOK.Enabled = cboTags.Value <> ""
End Sub
Private Sub txtSearch_Change()
Dim sSearch As String
Dim vCurrent As Variant
sSearch = "*" & UCase$(txtSearch.Value) & "*"
cboTags.Clear
If IsArray(SmartTagCandidateList) Then
For Each vCurrent In SmartTagCandidateList
If UCase$(CStr(vCurrent)) Like sSearch Then cboTags.AddItem vCurrent
Next vCurrent
Else
If UCase$(CStr(SmartTagCandidateList)) Like sSearch Then cboTags.AddItem vCurrent
End If
cboTags.Value = txtSearch.Value
End Sub
Private Sub UserForm_Activate()
With cboTags
.Clear
.MatchRequired = True
End With
OK = False
btnOK.Enabled = False
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If Cancel <> 1 Then
Cancel = -1
OK = False
Me.Hide
End If
End Sub
Private Sub btnCancel_Click()
OK = False
Me.Hide
End Sub
Private Sub btnOK_Click()
OK = True
Me.Hide
End Sub
4) In Sheet2 right-click the tab and select [View Code]
5) Place this in the code window:
Code:
Option Explicit
Dim mvaSmartTags As Variant
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const iSmartTagColumn As Integer = 1 'column 1 for smart Tag Column
Dim frmSmartTag As New fSmartTag
Dim lRowEnd As Long
Dim lRow As Long
'-- Exit if not correct column or row 1 or if > 1 cell selected
If Target.Column <> iSmartTagColumn Then Exit Sub
If Target.Row < 2 Then Exit Sub
If Target.Cells.Count <> 1 Then Exit Sub
If IsArray(mvaSmartTags) = False Then
'-- Get list from Sheet1 column A
With Sheets("Sheet1")
lRowEnd = .Cells(Rows.Count, "A").End(xlUp).Row
If lRowEnd > 1 Then mvaSmartTags = .Range("A2:A" & lRowEnd).Value
End With
End If
Set frmSmartTag = New fSmartTag
frmSmartTag.SmartTagCandidateList = mvaSmartTags
frmSmartTag.Show
If frmSmartTag.OK Then Target.Value = frmSmartTag.cboTags.Value
Set frmSmartTag = Nothing
End Sub
When you select a cell in column A of sheet2, the userform will be displayed. Enter a search string in the text box and the combobox will display the list of candidates. Select as appropriate & click "OK".
