Add Controls, Form Control and ActiveX Control on the worksheet dynamically
This page discusses the use of the Form control and ActiveX control on the worksheet.
- Add the Form controls and ActiveX controls on the worksheet dynamically
- Assign the event handling Macros to the corresponding Form and ActiveX controls
- Encapsulate the Macros in the Class module rather than in the worksheet code module
1. Main Procedure
The procedures are modularized to aid in the compartmentaton of the large program code and a main procedure Demo_Add_Control contains and runs the compartment Macros in sequence.
Option Explicit
Option Base 1
Option Compare Text
Sub Demo_Add_Control()
Call Form_Template
Call Add_ActiveX_Control
Call Add_Form_Control
ActiveSheet.Columns.AutoFit
End Sub
2. Add ActiveX Controls on the worksheet dynamically.
Add a ComboBox of ActiveX control on the worksheet using OLEObjects.Add (ClassType:="Forms.ComboBox.1"...) method
The added ActiveX control is named and the elements are added using OLEObjects("TypeofCalculation").Object.list property.
The procedure is designed to add two ComboBoxes of the ActiveX controls on the worksheet at the defined location with defined width and height.
Sub Add_ActiveX_Control()
Dim ole As OLEObject
Dim Cbx1 As OLEObject
Dim Cbx2 As OLEObject
'*******************************************************************
' ComboBox for Type of calculation (cbx1)
'*******************************************************************
With ActiveSheet
With .Cells(1, 3)
Set Cbx1 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=.Left, Top:=.Top, Width:=.Resize(1, 1).Width, Height:= _
.Resize(1, 1).Height)
End With
With Cbx1
.Name = "TypeofCalculation"
.Border.LineStyle = fmBorderStyleSingle
.Object.SpecialEffect = 0
End With
With .OLEObjects("TypeofCalculation").Object
.Clear
.List = Array("Gas", "Liquid", "Steam")
.Text = .List(0)
End With
End With
'*******************************************************************
' ComboBox for Unit system (cbx2)
'*******************************************************************
With ActiveSheet
With .Cells(1, 4)
Set Cbx2 = ActiveSheet.OLEObjects.Add(ClassType:="Forms.ComboBox.1", Link:=False, _
DisplayAsIcon:=False, Left:=.Left, Top:=.Top, Width:=.Resize(1, 1).Width, Height:= _
.Resize(1, 1).Height)
End With
With Cbx2
.Name = "UnitSystem"
.Border.LineStyle = fmBorderStyleSingle
.Object.SpecialEffect = 0
End With
With .OLEObjects("UnitSystem").Object
.Clear
.AddItem "SI"
.AddItem "FPS"
.Text = .List(0)
End With
End With
End Sub
3. Add Form Control on the worksheet
Add a Form control, a Command button, is added using Buttons.Add method dynamically and assigned to an event procedure "ActiveXControl_Set" to be run using OnAction attribute. The event procedure ActiveXControl_Set for a Form control can be and is placed in a standard module. You can write the code for the application before the form control is created or dynamically by writing the code by VBA.
Sub Add_Form_Control()
Dim CmdBtn As Variant
Set CmdBtn = ActiveSheet.Buttons.Add(20, 20, 20, 20)
With CmdBtn
.Left = ActiveSheet.Cells(1, 6).Left
.Top = ActiveSheet.Cells(1, 6).Top
.Width = ActiveSheet.Cells(1, 6).Resize(1, 1).Width * 1#
.Height = ActiveSheet.Cells(1, 6).Resize(1, 1).Height
.Caption = "Event"
.OnAction = "ActiveXControl_Set"
End With
End Sub
The event procedure is written in a standard module Sub ActiveXControl_set()
Option Explicit
Option Base 1
Option Compare Text
Dim cls As New cl_ActiveX
Sub ActiveXControl_Set()
'**************************************************
'Point sheet and OleObjects to class module object.
'**************************************************
Set cls.Sheet = ActiveSheet
Set cls.Combo1 = cls.Sheet.OLEObjects("TypeofCalculation").Object
Set cls.Combo2 = cls.Sheet.OLEObjects("UnitSystem").Object
End Sub
Sub ActiveXControl_Reset()
Set cls.Sheet = Nothing
Set cls.Combo1 = Nothing
Set cls.Combo2 = Nothing
End Sub
4. Encapsulate the ActiveXcontrols event procedure in a class module
It is usual and straightforward that the ActiveX control event procedure is created in the code module of the worksheet where the ActiveX control lies. To do so, click the activeX control at the design mode and select View Code on the shortcut menu. Create your own event procedure in the VBA editor.
Alternatively, use a class module to encapsulate the event procedures (method) and properties in the class module.
To do so, take the following steps:
The event procedure and propertis are defined inside the class module.
The class module is not an object by itself but a template of the object.
It is necessary to make an object based on the class module and to be instantiated to become an object.
- Insert a new class module and name the class module, for example,cl_ActiveX
- In the class module, declare public Objects for worksheet and ActiveX controls by using the WithEvents keyword.
Public WithEvents sht As Worksheet
Public WithEvents Cbx1 As ComboBox
Public WithEvents Cbx2 As ComboBox - Define the objects in the class module using Property Get and Property Set procedure. The objects defined in the class module are to be addressed by the procedure in the standard modules.
- The class module is not an object by itself but a template of the object.
It is necessary to make an object based on the class module and to be instantiated to become an object.
Declare the class module and instantiate the class into an object in the general declaration section of the standard module
Dim cls As New cl_ActiveX - The objects defined in the class module are pointed from the procedure in the standard module using Set keyword.
Set cls.Sheet = ActiveSheet
Set cls.Combo1 = cls.Sheet.OLEObjects("TypeofCalculation").Object
Set cls.Combo2 = cls.Sheet.OLEObjects("UnitSystem").ObjectAddress methods defined in the class module
4.1 Class module
It declares public Objects for worksheet and ActiveX controls by using the WithEvents keyword in the general declaration section,
and define the objects and procedutes which are pointed by the standard module procedure.
Option Explicit
Option Compare Text
Option Base 1
Private ws As Worksheet
Public WithEvents sht As Worksheet
Public WithEvents Cbx1 As ComboBox
Public WithEvents Cbx2 As ComboBox
Sub Class_Initialize()
Set Me.Sheet = ActiveSheet
Set Me.Combo1 = Me.Sheet.OLEObjects("TypeofCalculation").Object
Set Me.Combo2 = Me.Sheet.OLEObjects("UnitSystem").Object
End Sub
'*****************************************
''Worksheet
'*****************************************
Public Property Set Sheet(ws As Worksheet)
Set sht = ws
End Property
Public Property Get Sheet() As Worksheet
Set Sheet = sht
End Property
'*****************************************
''Combobox1 (TypeofCalculation)
'*****************************************
Public Property Set Combo1(c1 As ComboBox)
Set Cbx1 = c1
End Property
Public Property Get Combo1() As ComboBox
Set Combo1 = Cbx1
End Property
'*****************************************
''Combobox2 (UnitSystem)
'*****************************************
Public Property Set Combo2(c1 As ComboBox)
Set Cbx2 = c1
End Property
Public Property Get Combo2() As ComboBox
Set Combo2 = Cbx2
End Property
Private Sub cbx1_Change()
Dim sh As Shape
Application.ScreenUpdating = False
For Each sh In sht.Shapes
If sh.Type = msoPicture Then
sh.Delete
End If
Next
With sht.Cells(12, 2)
Select Case Cbx1.Value
Case "Gas"
Call CopyPasteResizePicture _
("DataImage", "eq. 3-4", "Sheet1", 12, 2, 1, 3)
Case "Liquid"
Call CopyPasteResizePicture _
("DataImage", "eq. 3-7", "Sheet1", 12, 2, 1, 3)
Case "Steam"
Call CopyPasteResizePicture _
("DataImage", "eq. 3-8", "Sheet1", 12, 2, 1, 3)
End Select
End With
Call ActiveXControl_Set
End Sub
Private Sub cbx2_Change()
Select Case Cbx2.Value
Case "SI"
sht.Cells(2 + 1, 4).Value = "mm"
sht.Cells(2 + 2, 4).Value = ""
sht.Cells(2 + 3, 4).Value = "Degree C"
sht.Cells(2 + 4, 4).Value = "kPa"
With sht.Cells(2 + 5, 4)
.Value = "m3/hr"
.Characters(Start:=2, Length:=1).Font.Superscript = True
End With
sht.Cells(2 + 6, 4).Value = "mm of water"
Case "FPS"
sht.Cells(2 + 1, 4).Value = "inch"
sht.Cells(2 + 2, 4).Value = ""
sht.Cells(2 + 3, 4).Value = "Degree F"
sht.Cells(2 + 4, 4).Value = "Psia"
With sht.Cells(2 + 5, 4)
.Value = "ft3/hr"
.Characters(Start:=3, Length:=1).Font.Superscript = True
End With
sht.Cells(2 + 6, 4).Value = "inch of water"
End Select
Call ActiveXControl_Set
End Sub
Sub CopyPasteResizePicture(FrWS, FrPic, ToWS, r, c, rs, cs)
On Error Resume Next
ThisWorkbook.Worksheets(FrWS).Pictures(FrPic).Copy
Worksheets(ToWS).Paste
With Selection
.ShapeRange.LockAspectRatio = msoFalse
.Left = Cells(r, c).Left
.Top = Cells(r, c).Top
.Width = Cells(r, c).Resize(1, cs).Width
.Height = Cells(r, c).Resize(rs, 1).Height
End With
Application.CutCopyMode = False
ActiveWindow.RangeSelection.Select
On Error GoTo 0
End Sub
4.2 Standard module
The objects defined in the class module are pointed from the procedure in the standard module using Set keyword.
Option Explicit
Option Base 1
Option Compare Text
Dim cls As New cl_ActiveX
Sub ActiveXControl_Set()
'**************************************************
'Point sheet and OleObjects to class module object.
'**************************************************
Set cls.Sheet = ActiveSheet
Set cls.Combo1 = cls.Sheet.OLEObjects("TypeofCalculation").Object
Set cls.Combo2 = cls.Sheet.OLEObjects("UnitSystem").Object
End Sub
Sub ActiveXControl_Reset()
Set cls.Sheet = Nothing
Set cls.Combo1 = Nothing
Set cls.Combo2 = Nothing
End Sub
5. User Interface
You can create your preferred method to run the program referring to the page
User Interface 6 methods
The program in the file you can download adds an Icon on the Quick Access Tool bar (Method 4).
6. Download Workbook
Download the Excel Addin and a demo file.
Download a Zip file with Excel AddinDated on: 24-November-2019