User Controls Excel Add-in

Controls

In Excel user can insert user controls which can help creating various useful tasks like employee survey, daily task trackers, dashboards etc. Microsoft Excel, ActiveX controls are represented by OLEObject objects in the OLEObjects collection. Let’s write some code add an ActiveX control in a sheet, use the Add method of the OLEObjects collection. The following example adds a Command button to worksheet.

Code example

Public Sub AddControl()
	Worksheets(1).OLEObjects.Add "Forms.CommandButton.1", Left:=10, Top:=10, Height:=20, Width:=10
End Sub

Control Properties

Caption: refers to display text on the control. The following example changes the caption on the control named “CommandButton1.”

Code example

Public Sub SetCaption()
	Sheet1.CommandButton1.Caption = "Run"
End Sub

Note when you use a control name outside the class module for the sheet containing the control, you must qualify the control name with the sheet name.

Positions (Left, Right, Top)

Refers to left position on the screen for the given control

Code example

Public Sub SetPosition()
	Worksheets(1).OLEObjects("CommandButton1").Left = 50
	Worksheets(1).OLEObjects("CommandButton1").Right = 70	
	Worksheets(1).OLEObjects("CommandButton1").Top = 10
End Sub

Object Property

Public Sub Setcaption()
	Worksheets(1).OLEObjects("CommandButton1").Object.Caption = "run me"	
End Sub

Controls with Shape

Public Sub AllignAllShapes()
	For Each s In Worksheets(1).Shapes
		If s.Type = msoOLEControlObject Then 
			s.Left = 10
		End If
	Next s
End Sub

Next >> Excel Replace Function Vs. VBA replace Function example

Share on Facebook

Tweet