excel vba - How to add activeX buttons programmatically in VBA, filling all rows down a column -


my first post here, have been sourcing solutions , ideas website while now. collection of solutions , ideas.

basically, have spread sheet application requiring first column, column a, filled "active x" buttons in every cell, looping through given quantity. have posted 1 such working solution below makes use of "form type buttons" , modules. exemplifies consider favored example working buttons. once operational column of buttons correspond relative data on same row, , when clicked open corresponding folders, , userforms in later developments.

the second post uses range function, doesn't incorporate buttons interactive with. however, mouse click on range activate code within worksheet_selection change procedure...sorry stating obvious!

what have been trying achieve version of code employing "activex" command buttons, after having studied great tutorials , poured on range of programing concepts, still fail miserably employ oleobjects.

how add button programmatically in vba next sheet cell data?

sheet 1 procedure: sub columna_buttons()

    dim buttons button     dim rng range     dim lineqty variant          application.screenupdating = false         activesheet.buttons.delete      lineqty = 5      = 1 lineqty         set rng = activesheet.range(cells(i, 1), cells(i, 1))         set buttons = activesheet.buttons.add(rng.left, rng.top, rng.width, rng.height)              buttons                 .onaction = "buttons"                 .caption = "line " &                 .name = "line " &                 end         next          application.screenupdating = true  end sub  public click_button variant ' make variable public userform1

'

form button module:

sub line_buttons()   click_button = application.caller      msgbox click_button & " clicked"          userform1.show 'launch custom userform  end sub

and next option considered range detection

private sub worksheet_selectionchange(byval target range)   ' e.g., range(a1:e1) clicked        if not application.intersect(target, range("b2:b12")) nothing             msgbox "you clicked " & target.address        end if end sub

here example of activex buttons being created , coded run. may take small tweaks, job done.

sub createbutton()              dim obj object             dim code string             dim cellleft single dim celltop single dim cellwidth single dim cellheight single dim lineqty integer  sheets("sheet1").select    lineqty = 5  = 1 lineqty set rng = activesheet.range(cells(i, 1), cells(i, 1))     cellleft = rng.left     celltop = rng.top     cellwidth = rng.width     cellheight = rng.height     'create button                 set obj = activesheet.oleobjects.add(classtype:="forms.commandbutton.1", link:=false, displayasicon:=false, left:=cellleft, top:=celltop, width:=cellwidth, height:=cellheight)                 obj.name = "testbutton"                 'button text                 activesheet.oleobjects(1).object.caption = "test button"                  'macro text added possibly array?                code = "private sub testbutton_click()" & vbcrlf                 code = code & "call tester" & vbcrlf                 code = code & "end sub"                 'add macro @ end of sheet module                 activeworkbook.vbproject.vbcomponents(activesheet.name).codemodule                     .insertlines          .countoflines + 1, code                 end   next  end sub              sub tester()                 msgbox "you have clicked on test button"             end sub

note in order not error on me, had go trust center , trust center settings , macro settings , check box "trust access vba project object model"


Comments

Post a Comment

Popular posts from this blog

jOOQ update returning clause with Oracle -

according http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/update-statement/ jooq doesn't support returning clause in update statements databases except firebird , postgres. does know, still correct oracle jooq? according oracle's documentation, oracle db supports returning clase delete, insert, or update statements. incorrect manual you're right - manual wrong , should fixed: https://github.com/jooq/jooq/issues/5470 single row update returning jooq 3.8 added support single row update .. returning statements. internally, uses callablestatement , pl/sql block of form: begin update "my_table" set "my_table"."column" = 'xyz' "my_table"."id" = 1 returning "my_table"."id", "my_table"."column" ?, ?; ? := sql%rowcount; end; the relevant issue is: https://github.com/jooq/jooq/issues/5190 multi row update returning a...
Read more

java - Warning equals/hashCode on @Data annotation lombok with inheritance -

i have entity inherits other. on other hand, i'm using lombok project reduce boilerplate code, put @data annotation. annotation @data inheritance produces next warning: generating equals/hashcode implementation without call superclass, though class not extend java.lang.object. if intentional, add '@equalsandhashcode(callsuper=false)' type. is advisable add annotation @equalsandhashcode (callsuper = true) or @equalsandhashcode (callsuper = false) ? if not added, 1 callsuper=false or callsuper=true ? the default value false . 1 if don't specify , ignore warning. yes, recommended add @equalsandhashcode annotation on @data annotated classes extend else object. cannot tell if need true or false , depends on class hierarchy, , need examined on case-by-case basis. however, project or package, can configure in lombok.config call super methods if not direct subclass of object. lombok.equalsandhashcode.callsuper = call for configuration system ...
Read more

java - BasicPathUsageException: Cannot join to attribute of basic type -

i using java 8 jpa (hibernate 5.2.1). have clause works perfectly, until introduce clause make use foreign key table values too. i getting following error: basicpathusageexception: cannot join attribute of basic type i think problem related fact have join table, , not sure how create join using join table. employee - employee_category - category model (employee.java): @manytomany(cascade = cascadetype.all, fetch=fetchtype.eager) @jointable ( name="employee_category", joincolumns={ @joincolumn(name="emp_id", referencedcolumnname="id") }, inversejoincolumns={ @joincolumn(name="cat_id", referencedcolumnname="id") } ) private set<category> categories; model (category.java): @id private string id; jpa when introduce following code, fails error above (i think problem new piece of code): join<t, category> category = from.join("id"); predicates.add(criteriabuilder.like(category....
Read more