VB and VBA Users Source Code: Accessing/setting properties of controls on Excel worksheets
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Accessing/setting properties of controls on Excel worksheets
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Monday, October 08, 2001
Hits:
944
Category:
Unspecified
Article:
The following routines demonstrate how to set/get properties of controls contained on Excel worksheets. Option Explicit 'Purpose : Returns an ActiveX control from a sheet. 'Inputs : oWkSheet The worksheet containing the control ' sControlName The name of the control 'Outputs : Returns the object if suceeded, else returns nothing. 'Author : Andrew Baker 'Date : 28/Sep/2001 'Notes : Provides a function to access controls on Excel sheets. ' Overcomes problems with using the Shapes collection to access the controls with ' having to access the controls through the sheet classes (i.e. early bound referencing ' eg. Sheet1.Listbox1) ' Requires Excel 2000 or newer. 'Example : Function WorksheetControlGet(oWkSheet As Worksheet, sControlName As String) As Object On Error Resume Next Set WorksheetControlGet = CallByName(oWkSheet, sControlName, VbGet) On Error GoTo 0 End Function 'Purpose : Returns an ActiveX control from a sheet. 'Inputs : oWkSheet The worksheet containing the control ' sControlName The name of the control 'Outputs : Returns the object if suceeded, else returns nothing. 'Author : Andrew Baker 'Date : 28/Sep/2001 'Notes : Provides a function to access controls on Excel sheets. ' Overcomes problems with using the Shapes collection to access the controls with ' having to access the controls through the sheet classes (i.e. early bound referencing ' eg. Sheet1.Listbox1). ' For Excel 97. 'Example : Function WorksheetControlGet97(oWkSheet As Worksheet, sControlName As String) As Object On Error Resume Next Set WorksheetControlGet97 = oWkSheet.OLEObjects(sControlName) On Error GoTo 0 End Function 'Demonstrates how to set a listbox's listfill range Sub Test() Dim oListbox As Object 'For Excel 97 use Set oListbox = WorksheetControlGet97(Workbooks("Book1.xls").Worksheets("Sheet1"), "ListBox1") 'For Excel 2000 and > use Set oListbox = WorksheetControlGet(Workbooks("Book1.xls").Worksheets("Sheet1"), "ListBox1") If (oListbox Is Nothing) = False Then oListbox.ListFillRange = "A5:A6" Set oListbox = Nothing MsgBox "Set listbox listfill range" Else MsgBox "Failed to get control from sheet" End If End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
