ActiveX Control Properties

[Nigel]

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

 

[Harald Staff]

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

 

[Chip Pearson]

Nigel,

Use

Sheets(1).OLEObjects("cbTotals").Enabled = True



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Nigel]

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

 

[Harald Staff]

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

 

[Nigel]

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

 

[Jon Peltier]

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]

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