Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel controls vs vba controls
Sorry posted this in Excel General before by mistake! I am new to VBA so just finding my feet, can anyone help me with the differences between an excel control (i.e. one from the form toolbox) lets say a checkBox and a vba checkBox? From trying the two controls the I have noticed is that you cannot capture the change event of the VBA checkbox in the code module, it has to be done via the sheet (i.e. checkbox on sheet1, code goes in change event on sheet1) the control is placed on, is this correct? What would you do if you have three sheets with the same controls just different data, do you really have to code the control 3 times? The excel control however you can code a module to capture the change event but cannot reference the checkbox directly as in activesheet.checkbox1.value, is this correct? I am trying to decide which is the best 'toolbox' to use for a vba app, trying to ensure the code is not tied to a specific sheet. Not sure if this is possible using these controls? -- cmpcwil2 ------------------------------------------------------------------------ cmpcwil2's Profile: http://www.excelforum.com/member.php...o&userid=33411 View this thread: http://www.excelforum.com/showthread...hreadid=534110 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel controls vs vba controls
Control toolbox controls are event driven, and can respond to a number of
events, and have more programmable properties. These events are coded in the sheet code module that the control exists within. Forms controls just have a single macro assigned to them which fires when the control is clicked, and have fewer programmable properties. The macro would be in a standard code module. Control toolbox controls are more flexible, forms controls are usually easier for the less experienced user, and are part of Excel, so maybe work better in Excel -- HTH Bob Phillips (remove nothere from email address if mailing direct) "cmpcwil2" wrote in message ... Sorry posted this in Excel General before by mistake! I am new to VBA so just finding my feet, can anyone help me with the differences between an excel control (i.e. one from the form toolbox) lets say a checkBox and a vba checkBox? From trying the two controls the I have noticed is that you cannot capture the change event of the VBA checkbox in the code module, it has to be done via the sheet (i.e. checkbox on sheet1, code goes in change event on sheet1) the control is placed on, is this correct? What would you do if you have three sheets with the same controls just different data, do you really have to code the control 3 times? The excel control however you can code a module to capture the change event but cannot reference the checkbox directly as in activesheet.checkbox1.value, is this correct? I am trying to decide which is the best 'toolbox' to use for a vba app, trying to ensure the code is not tied to a specific sheet. Not sure if this is possible using these controls? -- cmpcwil2 ------------------------------------------------------------------------ cmpcwil2's Profile: http://www.excelforum.com/member.php...o&userid=33411 View this thread: http://www.excelforum.com/showthread...hreadid=534110 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel controls vs vba controls
Your assessment are generally correct except the last one about referencing.
You can reference it direclty if you enclose it's name in brackets. activesheet.[checkbox1].value of course the default names usually contain spaces activesheet.[check box 1].value You can use techniques like those shown by John Walkenbach for attaching a single event to multiple ActiveX (control toolbox) controls: http://www.j-walk.com/ss/excel/tips/tip44.htm Generally, the activeX controls have more events, are more configurable and are the only controls (of the two) that work on userforms Forms controls are integral to Excel, so they are highly compatible, but only have the click event and have few properties which can be manipulated. (however, the textbox control from the drawing toolbar supports rich text formatting). the work on the old dialog sheets as well. I don't see it as an either/or decision, but using the right control at the time. (and of course there are many times when either control could get the job done) -- Regards, Tom Ogilvy "cmpcwil2" wrote: Sorry posted this in Excel General before by mistake! I am new to VBA so just finding my feet, can anyone help me with the differences between an excel control (i.e. one from the form toolbox) lets say a checkBox and a vba checkBox? From trying the two controls the I have noticed is that you cannot capture the change event of the VBA checkbox in the code module, it has to be done via the sheet (i.e. checkbox on sheet1, code goes in change event on sheet1) the control is placed on, is this correct? What would you do if you have three sheets with the same controls just different data, do you really have to code the control 3 times? The excel control however you can code a module to capture the change event but cannot reference the checkbox directly as in activesheet.checkbox1.value, is this correct? I am trying to decide which is the best 'toolbox' to use for a vba app, trying to ensure the code is not tied to a specific sheet. Not sure if this is possible using these controls? -- cmpcwil2 ------------------------------------------------------------------------ cmpcwil2's Profile: http://www.excelforum.com/member.php...o&userid=33411 View this thread: http://www.excelforum.com/showthread...hreadid=534110 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel controls vs vba controls
Thanks for the info, very helpful! -- cmpcwil2 ------------------------------------------------------------------------ cmpcwil2's Profile: http://www.excelforum.com/member.php...o&userid=33411 View this thread: http://www.excelforum.com/showthread...hreadid=534110 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Controls not available in Excel. | Excel Discussion (Misc queries) | |||
ActiveX Controls vs Form Controls | Excel Discussion (Misc queries) | |||
Event procedures for controls added with Controls.Add | Excel Programming | |||
Help on Excel controls | Excel Discussion (Misc queries) | |||
Controls in Excel | Excel Programming |