Hi,
Since you appear to want to limit the user's choice to values that already exist in column A and then presumably want to do something with that choice, don't bother with an Input Box, give the user a ListBox instead so that they can't do anything but select a valid choice
If the unique values in column A are static, create a list of allowable values somewhere else and name this say, 'Input_Vals"
Now use the .RowSource property of the Input Box and specify the name 'Input_Vals'. The user will only be able to select from the allowable values.
If the unique values are changing over time, then just enhance your macro so that the first thing you do each time is use an Advanced Filter to extract a list of unique names from column A. So make sure you have a column label at the top of your column A data, put this same label in a cell named say 'Data_Out" and then
Range(Range("A1"),Range("A" & Rows.Count).End(xlUp)).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("Data_Out"), Unique:= True
Assuming the DataOut range is in column Z, define the dynamic range Name 'Input_Vals' as
=OFFSET(DataOut,1,0,COUNTA(Sheet2!$Z:$Z)-1,1)
HTH
Bookmarks