VB and VBA Users Source Code: Looping through objects on an worksheet and changing their properties
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Looping through objects on an worksheet and changing their properties
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Friday, May 24, 2002
Hits:
1038
Category:
Unspecified
Article:
To loop through the objects on an Excel worksheet use the following example: Option Explicit 'Purpose : Sets the value of a range of worksheet controls with similiar names. 'Inputs : oWorkSheet The worksheet containing the controls. ' sControlBaseName The control base name without the index to set the values of ' eg. To set the values of the controls Checkbox1, Checkbox2 and ' Checkbox3 - send in a basename of "Checkbox" ' lStartIndex The first control to set the value of (in the case above use, 1). ' lEndIndex The last control to set the value of (in the case above use, 3). ' sPropertyName The name of the property to set the value of. ' vValue The value to set the property to. 'Outputs : Returns True on failure 'Author : Andrew Baker 'Date : 03/09/2000 13:58 'Notes : 'Revisions : Function SheetSetControls(oWorkSheet As Excel.Worksheet, sControlBaseName As String, lStartIndex As Long, lEndIndex As Long, sPropertyName As String, vValue As Variant) As Boolean Dim lThisControl As Long On Error GoTo ErrFailed For lThisControl = lStartIndex To lEndIndex If IsObject(vValue) Then Call CallByName(oWorkSheet.OLEObjects(sControlBaseName & lThisControl), sPropertyName, VbSet, vValue) Else Call CallByName(oWorkSheet.OLEObjects(sControlBaseName & lThisControl).Object, sPropertyName, VbLet, vValue) End If Next SheetSetControls = False Exit Function ErrFailed: Debug.Print "Error in SheetSetControls: " & Err.Description SheetSetControls = True End Function 'Add four CheckBoxes to an Excel worksheet then run the following code to tick and untick each of the boxes Sub Test() If SheetSetControls(ActiveSheet, "CheckBox", 1, 4, "Value", True) = True Then MsgBox "Failed to set checkbox values" Else MsgBox "Ticked checkboxes" End If If SheetSetControls(ActiveSheet, "CheckBox", 1, 4, "Value", False) = True Then MsgBox "Failed to set checkbox values" Else MsgBox "Ticked checkboxes" End If End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
