looping through controls
 

Is there a way to loop through controls (e.g. vb checkBox) that are o
the worksheet. I know you can do this for the userForm as follows

For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "CheckBox" Then
MsgBox CheckBox.Text
End If
Next ctrl

Is this possible for an active worksheet

--
cmpcwil
-----------------------------------------------------------------------
cmpcwil2's Profile: http://www.excelforum.com/member.php...fo&userid=3341
View this thread: http://www.excelforum.com/showthread.php?threadid=53261

looping through controls
 

Hope this is what you are after.

Sub erm()

Dim mycntrl As OLEObject
Dim sht As Worksheet

Set sht = ActiveSheet

For Each mycntrl In sht.OLEObjects

MsgBox mycntrl.Name

Next mycntrl


End Sub


Pet

--
Peter8
-----------------------------------------------------------------------
Peter81's Profile: http://www.excelforum.com/member.php...fo&userid=2535
View this thread: http://www.excelforum.com/showthread.php?threadid=53261

looping through controls
 

I think you have to do it by specific type of control, eg TextBox

"cmpcwil2" wrote:


Is there a way to loop through controls (e.g. vb checkBox) that are on
the worksheet. I know you can do this for the userForm as follows

For Each ctrl In UserForm1.Controls
If TypeName(ctrl) = "CheckBox" Then
MsgBox CheckBox.Text
End If
Next ctrl

Is this possible for an active worksheet?


--
cmpcwil2
------------------------------------------------------------------------
cmpcwil2's Profile: http://www.excelforum.com/member.php...o&userid=33411
View this thread: http://www.excelforum.com/showthread...hreadid=532616

looping through controls
 

If they are forms toolbar checkboxes, then use

Dim ctrl As CheckBox
For Each ctrl In ActiveSheet.CheckBoxes
MsgBox ctrl.Name
Next ctrl


--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Peter81" wrote in
message ...

Hope this is what you are after.

Sub erm()

Dim mycntrl As OLEObject
Dim sht As Worksheet

Set sht = ActiveSheet

For Each mycntrl In sht.OLEObjects

MsgBox mycntrl.Name

Next mycntrl


End Sub


Pete


--
Peter81
------------------------------------------------------------------------
Peter81's Profile:
http://www.excelforum.com/member.php...o&userid=25353
View this thread: http://www.excelforum.com/showthread...hreadid=532616

looping through controls
 

Thanks for all your help, just what I needed!


--
cmpcwil2
------------------------------------------------------------------------
cmpcwil2's Profile: http://www.excelforum.com/member.php...o&userid=33411
View this thread: http://www.excelforum.com/showthread...hreadid=532616