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:

enter image description here

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

Popular posts from this blog

serialization - Convert Any type in scala to Array[Byte] and back -

i have following question: i have variable value in program declared value. i want convert value byte array.. how can serialize byte array , back? found examples related other types such double or int, not any. this should need. it's pretty similar how 1 in java. import java.io.{bytearrayinputstream, bytearrayoutputstream, objectinputstream, objectoutputstream} object serialization extends app { def serialise(value: any): array[byte] = { val stream: bytearrayoutputstream = new bytearrayoutputstream() val oos = new objectoutputstream(stream) oos.writeobject(value) oos.close stream.tobytearray } def deserialise(bytes: array[byte]): = { val ois = new objectinputstream(new bytearrayinputstream(bytes)) val value = ois.readobject ois.close value } println(deserialise(serialise("my test"))) println(deserialise(serialise(list(1)))) println(deserialise(serialise(map(1 -> 2)))) println(deserialise(seri
Read more

matplotlib support failed in PyCharm on OSX -

any idea how fix ? how can debug might problem ? not seem : https://youtrack.jetbrains.com/issue/py-15520 i in console: python 2.6.9 (unknown, jul 14 2015, 19:46:31) [gcc 4.2.1 compatible apple llvm 6.0 (clang-600.0.39)] on darwin >>> import matplotlib.pyplot plt matplotlib support failed the backend : >>> import matplotlib >>> print(matplotlib.rcparams['backend']) macosx
Read more

python - Matplotlib: TypeError: 'AxesSubplot' object is not callable -

i new programming please pardon me if rookie mistake. i writing python script have defined functions create subplots of distributions. i have slider widget end user change value of variables(mu , sigma) , see effect on distribution. here's code import numpy np print np.__version__ import scipy print scipy.__version__ scipy.stats import norm, lognorm, uniform import matplotlib print matplotlib.__version__ import matplotlib.pyplot plt matplotlib.widgets import slider, button, radiobuttons, checkbuttons #####calculating mean , standard deviation##### global mu_a1 mu_a1 = 1 global mu_b1 mu_b1 = 10 global mu_c1 mu_c1 = -13 global sigma_a1 sigma_a1 = 0.14 global sigma_b1 sigma_b1 = 1.16 global sigma_c1 sigma_c1 = 2.87 mu_x01 = -11 sigma_x01 = 1.9 #####_____##### #####generating random data##### a1 = 0.75*mu_a1 + (1.25 - 0.75)*sigma_a1*np.random.sample(10000) b1 = 8*mu_b1 + (12 - 8)*sigma_b1*np.random.sample(10000) c1 = -12*mu_c1 + 2*sigma_c1*np.random.sample(10000)
Read more