activex-combobox for name column in excel -

i have excel sheet use record casual worker data.

out of 6 columns, options can chosen through data validation not change , therefore not problem.(range of days worked/overtime, etc).

the 1 thing need name column activex combobox can make use of autofill , have ability add new names time.

unlike data validation can dragged , copied down range of cells (i have no more 100 casual workers), unsure how combobox every new row of name column contains combobox. please advise.

if don't mind using form control combobox instead of activex, here example worked up. used months instead of names, because don't know names want use. concept same.

it adds months list items in code, although array of values, , loop instead. sake of simplicity of demonstrating programmatically adding comboboxes cells, hard coded 12 months.

also, populated rows 2 20 sake of example.

tested:

sub addcomboboxes()  dim sheet string dim newname string dim lrow long 'dim lastrow long   'not using example.  worth keeping in mind option.      sheet = "sheet1"   'set sheet name     'lastrow = sheets(sheet).range("a" & rows.count).end(xlup).row          lrow = 2 20          'choose row limitations, perhaps 2 lastrow               newname = "cmbauto" & lrow             set cmbmonthrow = sheets(sheet).shapes.addformcontrol _             (xldropdown, left:=cells(1, 1).left, top:=cells(lrow, 1).top, width:=60, height:=15)          cmbmonthrow              .controlformat.linkedcell = "a" & lrow             .controlformat.additem "january", 1             .controlformat.additem "february", 2             .controlformat.additem "march", 3             .controlformat.additem "april", 4             .controlformat.additem "may", 5             .controlformat.additem "june", 6             .controlformat.additem "july", 7             .controlformat.additem "august", 8             .controlformat.additem "september", 9             .controlformat.additem "october", 10             .controlformat.additem "november", 11             .controlformat.additem "december", 12              .controlformat.dropdownlines = 12             .name = newname         end     next lrow end sub 

note: linked cell returns index number of selection. can have event when value changes, in example provided, linkedcell property fine.

in example, have used months, , since returns index, putting value behind combobox. included screenshot demonstrate this, , left column width wide enough see value of cell behind object. of course have column width end @ end of combobox.

in cell in column e, have formula using value of linked cell:

=if(a2="","",text(a2*29,"mmmm")) 

this returns month name of dropdown. without it, there wouldn't on sheet representing choice made dropdown.

links:

office support: add listbox or combobox control worksheet.

here example of question answered using userform pretty same thing, instead of object in sheet, or in case many objects in sheet. why prefer use userforms. have 1 dropdown when changed, code finds appropriate cells manipulate, instead of 1 every row.