How to enumerate controls on a worksheet in Excel 2003

[tbone]

I am using Excel 2003. I set up control buttons on a worksheet to sort
selected areas of the sheet in certain predefined manners.

I would like to reset the captions of all the buttons when the
workbook is opened. However, it appears that there's no way to
enumerate all the controls as there is with VB forms.

I tried something like this:

dim ctl as control

for each ctl in activesheet.controls
if left(ctl.name,6) = "SortBy" then ctl.caption = "s"
next ctl

After searching help and the web, I've not found a way to enumerate
all the controls on a worksheet. It looks like there's no "Controls"
collection or something similar for a worksheet. Is there a way to do
this?

Thanks
tbone

 

[Dave Peterson]

Option Explicit
Sub testme()
Dim OLEObj As OLEObject
For Each OLEObj In Worksheets("sheet1").OLEObjects
If TypeOf OLEObj.Object Is MSForms.CommandButton Then
If LCase(Left(OLEObj.Name, 6)) = LCase("SortBy") Then
OLEObj.Object.Caption = "S"
End If
End If
Next OLEObj
End Sub

 

[Norman Jones]

Hi TBone,

In the ThisWorkbook module try:

'=============>>
Private Sub Workbook_Open()
Dim Ctrl As OLEObject
Dim SH As Worksheet
Set SH = Me.Sheets("Sheet2") '<<==== CHANGE

For Each Ctrl In SH.OLEObjects
With Ctrl
If TypeOf .Object Is MSForms.CommandButton Then
If Left(.Name, 6) = "SortBy" Then
.Object.Caption = "s"
End If
End If
End With
Next Ctrl
End Sub
'<<=============

 

[tbone]

Excellent! Thank you very much!

tbone

 

[tbone]

Works great! Thanks very much!