Adapt macro to select from list - Excel 2010

I am trying to adapt these two macros listed here:

http://www.excelforum.com/excel-gene...r-of-word.html

to my workbook. The cell that needs to populate is A7 in sheet 1 and my data list is in sheet 2 cells A1-A325 (it's also a named range: EmpList). I get a runtime error 9 when I run it.

Thanks!

Can you upload the workbook that you are trying to adapt and the code you used?

Originally Posted by MrsRobinson

I am trying to adapt these two macros listed here:

http://www.excelforum.com/excel-gene...r-of-word.html

to my workbook. The cell that needs to populate is A7 in sheet 1 and my data list is in sheet 2 cells A1-A325 (it's also a named range: EmpList). I get a runtime error 9 when I run it.

Thanks!

Consider using an ActiveX Combobox control from the Control Toolbox toolbar. You can position it to fill cell A7 and set these three properties (right-click and select Properties)
LinkedCell: A7
ListFillRange: EmpList
MatchEntry: 1 - frmMatchEntryComplete

The user can type in an entry and it will match it from the list as they type, or they can select an entry from the drop down list.

I can't upload the workbook - confidential employee info. However, here are the macros:

This one in a module:

Sub ShiftList(sLetter As String)
Dim MyRng As Range
Dim SearchFor As String
Set MyRng = Worksheets("Sheet2").Range("A1:A300")
SearchFor = sLetter & "*"
On Error Resume Next
MyRng.Find(What:=SearchFor, After:=MyRng(MyRng.Count), _
LookAt:=xlWhole).Activate
If Err <> 0 Then
MsgBox "The letter '" & sLetter & "' cannot be found."
Err.Clear
Exit Sub
End If
On Error GoTo 0
With ActiveWindow
.ScrollRow = ActiveCell.Row
.ScrollColumn = 1
End With
End Sub

this one in sheet 1:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
If Target.Address(0, 0) = "A7" Then Call ShiftList(Range("A7").Value)
End Sub

I will try the combobox, meanwhile...thanks!

AlphaFrog, I've tried every way I can think of to enter my range (i.e. with quotes, without quotes, named range, actual range, etc.) but ListFillRange goes blank after every entry...

Okay, figured it out - I'm going with the combobox. Thanks for the input!