excel - Creating a list of ComboBoxes using VBA that update the values of cells specified in the code -
what trying create program adds combo boxes options. these options should then, depending on option selected, change values in cells specify in code.
this how make combo lists:
private sub workbook_open() worksheets("sheet1").columns("e") .columnwidth = 25 end = 1 6 set curcombo = sheet1.shapes.addformcontrol(xldropdown, left:=cells(i, 5).left, top:=cells(i, 5).top, width:=100, height:=15) curcombo .controlformat.dropdownlines = 3 .controlformat.additem "completed", 1 .controlformat.additem "in progress", 2 .controlformat.additem "to done", 3 .name = "mycombo" & cstr(i) .onaction = "mycombo_change" end next end sub i want each of dropdown values trigger event mycombo_change , change cell "d" example, combo box 3 located @ e3 , want "to done" clear cell d3 , completed store date (and time) cell d3. should done combo boxes in e column.
private sub mycombo_change(index integer) me.range("d" & cstr(index)) = me.mycombo.value end sub this code started thinking about, have no idea how call event integer index parameter nor how access cell using said index.
the effect want along lines of this:
use application.caller name of control called mycombo_change event.
sub mycombo_change() dim curcombo shape set curcombo = activesheet.shapes(application.caller) curcombo.topleftcell.offset(0, -1) = end sub assign mycombo_change existing dropdown:
sub assignmacrotoalllistboxes() dim sh shape dim ws worksheet each ws in thisworkbook.worksheets each sh in ws.shapes if typename(sh.oleformat.object) = "dropdown" sh.oleformat.object.onaction = "mycombo_change" end if next next end sub delete dropdowns on sheet1
sub deletealldropdownsonsheet() each sh in sheet1.shapes if typename(sh.oleformat.object) = "dropdown" sh.delete end if next end sub 
Comments
Post a Comment