Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Properties
Hi Guys
I have not used ActiveX controls on a sheet before, so any help gratefully received. I have a worksheet with some activeX command buttons, each are set manually using the control properties to enabled = false. In my VBA code module I am trying to use the following to enable the controls, in this case the command name is cbTotals, however this does not work :-( Sheets(1).OLEObjects("cbTotals").Object.Enabled = True Something simple I'm sure but I've tried various permutations without success Cheers Nigel |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Properties
Hi Nigel
It's far simpler: Sheet1.cbTotals.Enabled = True where Sheet1 is not the real sheet name, but what it displays as in the project explorer window of the VB editor. If you code directly in this sheet's module, simply no sheet address needed: cbTotals.Enabled = True HTH. Best wishes Harald "Nigel" skrev i melding ... Hi Guys I have not used ActiveX controls on a sheet before, so any help gratefully received. I have a worksheet with some activeX command buttons, each are set manually using the control properties to enabled = false. In my VBA code module I am trying to use the following to enable the controls, in this case the command name is cbTotals, however this does not work :-( Sheets(1).OLEObjects("cbTotals").Object.Enabled = True Something simple I'm sure but I've tried various permutations without success Cheers Nigel |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Properties
Nigel,
Use Sheets(1).OLEObjects("cbTotals").Enabled = True -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Nigel" wrote in message ... Hi Guys I have not used ActiveX controls on a sheet before, so any help gratefully received. I have a worksheet with some activeX command buttons, each are set manually using the control properties to enabled = false. In my VBA code module I am trying to use the following to enable the controls, in this case the command name is cbTotals, however this does not work :-( Sheets(1).OLEObjects("cbTotals").Object.Enabled = True Something simple I'm sure but I've tried various permutations without success Cheers Nigel |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Properties
Hi Guys
Tried this as Harald Staff and Chip Pearson suggested but no joy! So there must be something else at fault? Sheets(1).OLEObjects("cbTotals").Enabled = True I get the Error 1004 - Unable to get the OLEObjects Property of the worksheet class The worksheet referred to is listed in the VBA projects as Sheet1 (Report) upon which there is a the command button named cbTotals -- I know this works as I have some code behind this worksheet that acts on this controls click event. I am stumped! The code to change the control is in a module within the workbook - does this matter? Cheers Nigel "Chip Pearson" wrote in message ... Nigel, Use Sheets(1).OLEObjects("cbTotals").Enabled = True -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Nigel" wrote in message ... Hi Guys I have not used ActiveX controls on a sheet before, so any help gratefully received. I have a worksheet with some activeX command buttons, each are set manually using the control properties to enabled = false. In my VBA code module I am trying to use the following to enable the controls, in this case the command name is cbTotals, however this does not work :-( Sheets(1).OLEObjects("cbTotals").Object.Enabled = True Something simple I'm sure but I've tried various permutations without success Cheers Nigel |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Properties
"Nigel" skrev i melding
... Hi Guys Tried this as Harald Staff and Chip Pearson suggested but no joy! So there must be something else at fault? Hi Nigel I did set this scenario up, and my provided code is tested and copy-pasted as is. So yes, something else is wrong, and I have no idea what. Nothing you write indicate that there anything could cause problems. Can you first try it in a brand new workbook with that single control, properly named, in Sheet1, and see if it works ? Best wishes Harald |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Properties
Thanks, I have figured out the problem but I'm not happy!
The worksheet with the controls, I had set up by adding one control, renamed it and then copied and pasted it five times. This block of five controls was then copied to create ten controls in total. Each was then renamed appropriately. I used my office PC running Excel 97 to do this. Click events were added to the sheet and the click event names were as per the control names. I then used my home machine (Excel 2002) to refine the modules by trying to add enable / disable events. In this environment the controls still appeared to be named as I had set them up with the attached events working OK. But VBA did not recognise the control names, as I discovered, following your advice on how to add the enable event. On further investigation I discover that the actual control names are not the same as those that I set up. These are CommandlButton1 to CommandButton5 and these are repeated for the second set - two sets of controls with the same names!! Even though the names, as they appear in the properties dialog, are as I set them up and recognised by the events. I only discovered they are different by printing all the control names from the Shapes enumeration for those objects that are controls. How the names in the properties list and the attached event work - I have no idea, but they do, and of course the VBA enable code failed as we discovered. I have since deleted all the control and added them one at a time, have not renamed them and they appear as CommandButton1 to CommandButton10 all events have been renamed and the references in my modules to enable / disable works great. I have not been able test the Excel 97 to Excel 2002 transfer yet, but I have tried the same in Excel 2002 and have not been able, so far, to recreate the problem. Another reason not to use embedded control methinks! Thanks for your help Cheers Nigel "Harald Staff" wrote in message ... "Nigel" skrev i melding ... Hi Guys Tried this as Harald Staff and Chip Pearson suggested but no joy! So there must be something else at fault? Hi Nigel I did set this scenario up, and my provided code is tested and copy-pasted as is. So yes, something else is wrong, and I have no idea what. Nothing you write indicate that there anything could cause problems. Can you first try it in a brand new workbook with that single control, properly named, in Sheet1, and see if it works ? Best wishes Harald |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Properties
Nigel -
Excel 97 was not very smart about copying these controls. I remember the names being mixed around, and the code behind the sheet no longer referring to the button I'd intended. I don't manipulate these controls very much (probably because of being burned in the past), but I have heard that Excel 2000 and later are smarter than Excel 97 in this regard. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Nigel wrote: Thanks, I have figured out the problem but I'm not happy! The worksheet with the controls, I had set up by adding one control, renamed it and then copied and pasted it five times. This block of five controls was then copied to create ten controls in total. Each was then renamed appropriately. I used my office PC running Excel 97 to do this. Click events were added to the sheet and the click event names were as per the control names. I then used my home machine (Excel 2002) to refine the modules by trying to add enable / disable events. In this environment the controls still appeared to be named as I had set them up with the attached events working OK. But VBA did not recognise the control names, as I discovered, following your advice on how to add the enable event. On further investigation I discover that the actual control names are not the same as those that I set up. These are CommandlButton1 to CommandButton5 and these are repeated for the second set - two sets of controls with the same names!! Even though the names, as they appear in the properties dialog, are as I set them up and recognised by the events. I only discovered they are different by printing all the control names from the Shapes enumeration for those objects that are controls. How the names in the properties list and the attached event work - I have no idea, but they do, and of course the VBA enable code failed as we discovered. I have since deleted all the control and added them one at a time, have not renamed them and they appear as CommandButton1 to CommandButton10 all events have been renamed and the references in my modules to enable / disable works great. I have not been able test the Excel 97 to Excel 2002 transfer yet, but I have tried the same in Excel 2002 and have not been able, so far, to recreate the problem. Another reason not to use embedded control methinks! Thanks for your help Cheers Nigel "Harald Staff" wrote in message ... "Nigel" skrev i melding .. . Hi Guys Tried this as Harald Staff and Chip Pearson suggested but no joy! So there must be something else at fault? Hi Nigel I did set this scenario up, and my provided code is tested and copy-pasted as is. So yes, something else is wrong, and I have no idea what. Nothing you write indicate that there anything could cause problems. Can you first try it in a brand new workbook with that single control, properly named, in Sheet1, and see if it works ? Best wishes Harald |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
ActiveX Control Properties
Thanks Jon, As I discovered!
I have since reloaded the sheet with the new code in Excel 97 and everything is working OK. I tried to recreate the problem without success, so even that is not consistent!! Cheers Nigel "Jon Peltier" wrote in message ... Nigel - Excel 97 was not very smart about copying these controls. I remember the names being mixed around, and the code behind the sheet no longer referring to the button I'd intended. I don't manipulate these controls very much (probably because of being burned in the past), but I have heard that Excel 2000 and later are smarter than Excel 97 in this regard. - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ Nigel wrote: Thanks, I have figured out the problem but I'm not happy! The worksheet with the controls, I had set up by adding one control, renamed it and then copied and pasted it five times. This block of five controls was then copied to create ten controls in total. Each was then renamed appropriately. I used my office PC running Excel 97 to do this. Click events were added to the sheet and the click event names were as per the control names. I then used my home machine (Excel 2002) to refine the modules by trying to add enable / disable events. In this environment the controls still appeared to be named as I had set them up with the attached events working OK. But VBA did not recognise the control names, as I discovered, following your advice on how to add the enable event. On further investigation I discover that the actual control names are not the same as those that I set up. These are CommandlButton1 to CommandButton5 and these are repeated for the second set - two sets of controls with the same names!! Even though the names, as they appear in the properties dialog, are as I set them up and recognised by the events. I only discovered they are different by printing all the control names from the Shapes enumeration for those objects that are controls. How the names in the properties list and the attached event work - I have no idea, but they do, and of course the VBA enable code failed as we discovered. I have since deleted all the control and added them one at a time, have not renamed them and they appear as CommandButton1 to CommandButton10 all events have been renamed and the references in my modules to enable / disable works great. I have not been able test the Excel 97 to Excel 2002 transfer yet, but I have tried the same in Excel 2002 and have not been able, so far, to recreate the problem. Another reason not to use embedded control methinks! Thanks for your help Cheers Nigel "Harald Staff" wrote in message ... "Nigel" skrev i melding .. . Hi Guys Tried this as Harald Staff and Chip Pearson suggested but no joy! So there must be something else at fault? Hi Nigel I did set this scenario up, and my provided code is tested and copy-pasted as is. So yes, something else is wrong, and I have no idea what. Nothing you write indicate that there anything could cause problems. Can you first try it in a brand new workbook with that single control, properly named, in Sheet1, and see if it works ? Best wishes Harald |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
2007 Form Control/ActiveX Control font difference | Excel Discussion (Misc queries) | |||
Definitions for Activex control properties? | Excel Worksheet Functions | |||
How do I change activex control properties from a macro | Excel Discussion (Misc queries) | |||
Return ActiveX properties by means of a variable??? | Excel Programming | |||
ActiveX Chart Properties in Excel 2000 | Excel Programming |