VB and VBA Users Source Code: Changing the properties of a control dynamically added to a worksheet
[
Home
|
Contents
|
Search
|
Reply
| Previous | Next ]
VB/VBA Source Code
Changing the properties of a control dynamically added to a worksheet
By:
Andrew Baker
Email (spam proof):
Email the originator of this post
Date:
Thursday, May 08, 2003
Hits:
1232
Category:
VBA (Visual Basic for Applications)
Article:
Adding a control to an Excel worksheet changes the runtime interface of the sheet. This means that you will not be able to access this control through the containers interface until the execution of you code has finished. The code below demonstrates a work around for this limitation: Option Explicit 'Purpose : Sets the properties of a worksheet controls that has been dynamically added to a sheet. 'Inputs : oWorkSheet The worksheet containing the control. ' sControlName The control name ' 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 WorksheetControlSetProperty(oWorkSheet As Excel.Worksheet, sControlName As String, sPropertyName As String, vValue As Variant) As Boolean On Error GoTo ErrFailed If IsObject(vValue) Then Call CallByName(oWorkSheet.OLEObjects(sControlName), sPropertyName, VbSet, vValue) Else Call CallByName(oWorkSheet.OLEObjects(sControlName).Object, sPropertyName, VbLet, vValue) End If Exit Function ErrFailed: Debug.Print "Error in WorksheetControlSetProperty: " & Err.Description WorksheetControlSetProperty = True End Function 'Example code Sub AddButton() 'Add a command button to a worksheet Call Sheets(1).OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=331.5, Top:=27.75, Width:=101.25, Height:=58.5) 'Change it's caption Call WorksheetControlSetProperty(Sheets(1), "CommandButton1", "Caption", "Next") 'Change it's fontsize Call WorksheetControlSetProperty(Sheets(1), "CommandButton1", "FontSize", 35) End Sub
Terms and Conditions
Support this site
Download a trial version of the Excel Workbook Rebuilder
