You would need to create a custom userform to provide a dropdown of names.


here are some references:
http://www.visualbasicforum.com/show...409#post594409


http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

Microsoft(R) Visual Basic(R) for Applications Examples for Controlling
UserForms in Microsoft Excel 97

This Application Note is an introduction to manipulating UserForms in
Microsoft Excel 97. It includes examples and Microsoft Visual Basic for
Applications macros that show you how to take advantage of the capabilities
of UserForms and use each of the ActiveX controls that are available for
UserForms

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.



http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data


John Walkenbach's site:
http://j-walk.com/ss/excel/tips/userformtips.htm
Userform Tips

Peter Aiken Articles:

watch word wrap. the URL should all be one line.
Part I
http://msdn.microsoft.com/library/en...uctiontoUserFo
rmsPartI.asp
Part II
http://msdn.microsoft.com/library/en...uctiontoUserFo
rmsPartII.asp

--
Regards,
Tom Ogilvy



"cdb" wrote:

I currently have a macro that copies a sheet to a different workbook
dependant on who the adviser is. It currently picks the adviser name up from
a cell in a sheet, but what I would like it to do is then when the macro is
set off, some kind of input box comes up and gives the option of which
adviser name to choose (be it a dropdown menu, a tick box etc)

Anyone know how to do this?

Cheers,

cdb