Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enumerate controls on a worksheet in Excel 2003
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enumerate controls on a worksheet in Excel 2003
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 tbone wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enumerate controls on a worksheet in Excel 2003
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 '<<============= --- Regards, Norman "tbone" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enumerate controls on a worksheet in Excel 2003
Excellent! Thank you very much!
tbone |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to enumerate controls on a worksheet in Excel 2003
Works great! Thanks very much!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using controls with Excel 2003 | Excel Discussion (Misc queries) | |||
No ActiveX controls when reading 2007 file formats into Excel 2003 | Excel Discussion (Misc queries) | |||
Activex controls in Excel 2003 | Excel Worksheet Functions | |||
Bug in Excel 2003 involving ActiveX Controls on worksheets | Excel Programming | |||
Controls move upon printing in Excel 2003 | Excel Discussion (Misc queries) |