programatically reference controls on spreadsheet
 

i am trying to loop through all the controls on a spreadsheet and set certain
properites. In a userform, i am able to accomplish this with the following
code
~ for each control
~userform.controls("button"&i).caption="asdf"

however, i am at a lose as to how to loop through the controls placed
directly on a worksheet as opposed to the userform.

any help would be appreciated

thks

programatically reference controls on spreadsheet
 

Dim ctl as Object
For each ctl in ActiveSheet.Shapes
MsgBox ctl.Name
Next

This seems to work for both "Forms" Toolbar created Items and "Control
Toolbox" created items.
--
Charles Chickering

"A good example is twice the value of good advice."


"k2sarah" wrote:

i am trying to loop through all the controls on a spreadsheet and set certain
properites. In a userform, i am able to accomplish this with the following
code
~ for each control
~userform.controls("button"&i).caption="asdf"

however, i am at a lose as to how to loop through the controls placed
directly on a worksheet as opposed to the userform.

any help would be appreciated

thks

programatically reference controls on spreadsheet
 

Charles,

many thanks. With this method, i am having problems modifying the properties
of the 'toggle bar' for example the code me.ctl.caption="test"? any help
would be appreciated. thanks ken

"Charles Chickering" wrote:

Dim ctl as Object
For each ctl in ActiveSheet.Shapes
MsgBox ctl.Name
Next

This seems to work for both "Forms" Toolbar created Items and "Control
Toolbox" created items.
--
Charles Chickering

"A good example is twice the value of good advice."


"k2sarah" wrote:

i am trying to loop through all the controls on a spreadsheet and set certain
properites. In a userform, i am able to accomplish this with the following
code
~ for each control
~userform.controls("button"&i).caption="asdf"

however, i am at a lose as to how to loop through the controls placed
directly on a worksheet as opposed to the userform.

any help would be appreciated

thks