Suggestions

 Adapt VBA in a VBAProject using VBA

1 Workbook and VBProject

2 Modules

3 Macromodule

3.1 Macro

3.2 Function

4 Workbook

5 Worksheet

6 ActiveX control

7 Userform
1 Workbook and VBProject

2 Modules
2.1 Refer to a module
2.2 Inventory modulenames

2.3 Modulename adapt
2.3.1 Workbook
2.3.2 Worksheet
2.3.3 Userform
2.3.4 Macromodule
2.3.5 Classmodule

2.4 Module: add
2.4.1 Worksheet
2.4.2 Macromodule
2.4.3 Userform
2.4.4 Classmodule

2.5 Module: export
2.5.1 Macromodule
2.5.2 Userform
2.5.3 Worksheett
2.5.4 Classmodulet
2.5.5 Workbook
2.5.6 All modules

2.6 Module import
2.6.1 Worksheet
2.6.2 Macromodule
2.6.3 Userform
2.6.4 Classmodule
2.6.5 Workbook

2.7 Module: delete
2.7.1 Worksheet
2.7.2 Macromodule
2.7.3 Userform
2.7.4 Classmodule
2.7.5 All modules

2.8 Module: copy to file
2.8.1 Worksheet
2.8.2 Macromodule
2.8.3 Userform
2.8.4 Classmodule
2.8.5 All VBA modules

2.9 Modules: move to file
2.9.1 move within a book
2.9.2 Worksheet
2.9.3 Userform
2.9.4 Macromodule
2.9.5 Classmodule
 3 Codemodule content

3.1 Codemodule
3.1.1 Complete VBA code
3.1.2 Part of VBA-code
3.1.3 Delete all VBA-code

3.2 Macromodule

3.2.1 Macro
3.2.1.1 inventory
3.2.1.2 find
3.2.1.3 add
3.2.1.4 read
3.2.1.5 copy
3.2.1.6 replace
3.2.1.7 rename
3.2.1.8 delete
3.2.1.9 move

3.2.2 Functions
3.2.2.1 inventory
3.2.2.2 find
3.2.2.3 add
3.2.2.4 read
3.2.2.5 copy
3.2.2.6 replace
3.2.2.7 rename
3.2.2.8 delete
3.2.2.9 move

3.3 Workbook
3.3.1 Macros
3.3.2 Functions
3.3.3 Eventprocedures
3.3.3.1 inventory
3.3.3.2 find
3.3.3.3 read
3.3.3.4 delete
3.3.3.5 add
3.3.3.6 copy
3.3.3.7 move

3.4 Worksheet
3.4.1 Macros
3.4.2 Functions
3.4.3 Eventprocedures
3.4.3.1 inventory
3.4.3.2 find
3.4.3.3 read
3.4.3.4 delete
3.4.3.5 add
3.4.3.6 copy
3.4.3.7 move

 4 ActiveX controls

4.1 inventory

4.2 add
4.2.1 TextBox
4.2.2 OptionButton
4.2.3 CheckBox
4.2.4 Label
4.2.5 CommandButton
4.2.6 ToggleButton
4.2.7 SpinButton
4.2.8 ComboBox
4.2.9 ListBox
4.2.10 Frame
4.2.11 Tabstrip
4.2.12 MultiPage
4.2.13 ScrollBar
4.2.14 Image

4.3 copy

4.4 rename

4.5 delete

4.6 eventcode
4.6.1 eventcode: find
4.6.2 eventcode: read
4.6.3 eventcode: add
4.6.4 eventcode: copy
4.6.5 eventcode: move
4.6.6 eventcode: replace
4.6.7 eventcode: delete

 5 Userform

5.1 Macros

5.2 Functions

5.3 Eventprocedures
5.3.1 inventory
5.3.2 find
5.3.3 read
5.3.4 delete
5.3.5 add
5.3.6 copy
5.3.7 move

5.4 Controls in a Userform
5.4.1 inventory

5.4.2 add
5.4.2.1 TextBox
5.4.2.2 OptionButton
5.4.2.3 CheckBox
5.4.2.4 Label
5.4.2.5 CommandButton
5.4.2.6 ToggleButton
5.4.2.7 SpinButton
5.4.2.8 ComboBox
5.4.2.9 ListBox
5.4.2.10 Frame
5.4.2.11 Tabstrip
5.4.2.12 MultiPage
5.4.2.13 ScrollBar
5.4.2.14 Image

5.4.3 copy

5.4.4 rename

5.4.5 delete

5.4.6 Userformcontrol events
5.4.6.1 inventory
5.4.6.2 find
5.4.6.3 read
5.4.6.4 add
5.4.6.5 copy
5.4.6.6 replace
5.4.6.7 adapt
5.4.6.8 delete

Adapt VBA in a workbook using VBA

VBA can create and maintain macros, functions, userforms, ActiveX controls and userformcontrols.
In the course of running a macro you can create, adapt, copy or delete macros, functions or userforms.
In this page we take VBA in Excel as an example. So we are describing VBA in a workbook.
All green marked text is meant as example; you can adapt it to your own situation.

Before applying code it's best to load the referenc to the VBA extensiblility library:
- manually : open the VBE Editor: Alt-F11/Menu Bar/ Extra / references/ check 'Microsoft Visual Basic for Application Extensibility 5.3'
- using VBA

Method 'AddFromGuid'
Sub load_reference_1()
ThisWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 5, 3
end sub
Method 'AddFromFile'
Sub Load_reference_2()
ThisWorkbook.VBProject.References.AddFromFile "C:\Program Files\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"
end sub

1 Workbook and VBProject

In the VBA language an Excelfile is a 'Workbook'.
If you want to refer to the workbook that contains the code that is running you should use the VBA object: ThisWorkbook.
An Excel workbook can contain VBA code; in the VBA language: the VBproject.
ThisWorkbook.VBproject

2 Modules

VBA code is always part of a file and is not an independent file.
VBA code is always part of a 'module' in an Excel file.

An Excel file has 5 kinds of modules (in VBA language: VBComponents):
o ThisWorkbook: the file
o Sheet: a worksheet or a chartsheet
o Macromodule: a module that can only contain macros and/or functions
o Userform: a userinterface that can contain macros, functions and controls
o Classmodule: a module that can only contain macros and/or functions; meant to be called from other modules
ThisWorkbook.VBProject.VBComponents
A Workbook contains 2 VBA-modules minimally: ThisWorkbook and 1 worksheet.
A Workbook contains 1 codemodule maximally 'ThisWorkbook'.
All other modules can be added ad libitum.
All modules can contain VBA-code.

VBA has different kinds of code:
- declarations
- macros (starting/ending with 'Sub ...End Sub'
- functions (starting/ending with 'Function ... End Function'
- events: thisWorkbok has certain builtin events, like every sheet has
- ActiveX controls and their events
- Userformcontrols and their events

Not every module can contain all those different kinds of code:

ThisWorkbook:
- macros
- functions
- events

Worksheet
- macros
- functions
- events
- ActiveX controls and ActiveX-events

Macromodule
- macros
- functions

Userform
- macros
- functions
- events
- formcontrols and formcontrol events

Classmodule
- macros
- functions
- events

2.1 How to refer to a module

In VBA you can refer to a module using it's name or it's indexnumber.
The indexnumber of ThisWorkbook is always 1, the indexnumber of the first worksheet is 2.
ThisWorkbook.VBProject.VBComponents(1)
or
ThisWorkbook.VBProject.VBComponents("ThisWorkbook")

2.2 An inventory of all modulenames

All modules have a 'VBA-name': in VBA language 'codename'.
Using that name you can refer to that module directly.
The workbook and the worksheets also have a 'common' name:
the workbook: the filename,
the worksheet: the name on it's tabstrip

The modules of a VBproject constitute the collection 'VBComponents'.
Sub Modules_namen()
For j= 1 to ThisWorkbook.VbProject.VBComponents.Count
msgbox ThisWorkbook.VBProject.VBComponents(j).Name
Next
End Sub
or
Sub Modules_namen2()
For each cp in ThisWorkbook.VbProject.VBcomponents
msgbox cp.Name
Next
End Sub
You can retrieve the VBA-name of a workbook or a worksheet from the property 'CodeName'
Sub Module_workbook_codenaam()
msgbox=ThisWorkbook.CodeName
End Sub
Sub module_worksheet_codenamen()
For each sh in ThisWorkbook.Sheets
msgbox sh.CodeName
Next
End Sub

2.3 Modulename adapt

2.3.1 Workbookname: adapt

You can retrieve the workbook's VBA name using it's property 'CodeName'.
This is a readonly property, you can't use it to change it's VBA-name.
Therefore we need:
Sub modulenaam_workbook_wijzigen()
ThisWorkbook.VbProject.VBcomponents("ThisWorkbook").Name ="hoofdbestand"
End Sub
NB. Word's VBA-library contains a special method to accomplish this: Application.OrganizerRename.

2.3.2 Worksheetname: adapt

The worksheet's 'readonly' property 'CodeName' can't be used to change a worksheets VBA name.
Also in this case we need another method.
Sub Modulenaam_worksheet_wijzigen()
ThisWorkbook.VbProject.VBcomponents(2).Name ="worksheet overzicht"
End Sub

2.3.3 Userform name: adapt

Sub Modulenaam_userform_wijzigen()
ThisWorkbook.VbProject.VBcomponents("Userform1").Name ="invoer"
End Sub

2.3.4 Macromodule name: adapt

Sub Modulenaam_macromodule_wijzigen()
ThisWorkbook.VbProject.VBcomponents("Module1").Name ="macroos"
End Sub

2.3.5 Classmodule name: adapt

Sub Modulenaam_classmodule_wijzigen()
ThisWorkbook.VbProject.VBcomponents("Klasse1").Name ="Klas_I"
End Sub

2.4 Module: add

In VBA VBA these modules can be added:
- a worksheet
- a macromodule (vbext_ct_StdModule)
- a Userform (vbext_ct_MSForm)
- a classmodule (vbext_ct_ClassModule)

2.4.1 Werksheet module: add

Sub Worksheetmodule_toevoegen()
ThisWorkbook.Sheets.Add
End Sub

2.4.2 Macromodule: add

Sub Macromodule_add1()
ThisWorkbook.VBProject.VBComponents.Add vbext_ct_StdModule
End Sub
or
Sub Macromodule_add2()
ThisWorkbook.VBProject.VBComponents.Add 1
End Sub
A new module will be named automatically Module1, Module2, Module3, etc.
If you prefer custom names:

Macromodule: add with custom name
Sub Macromodule_toevoegen_naam1()
ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule).Name ="Macroos"
End Sub
or
Sub Macromodule_toevoegen_naam2()
ThisWorkbook.VBProject.VBComponents.Add(1).Name ="Macroos"
End Sub

2.4.3 Userform: add

Sub Userform_toevoegen1()
ThisWorkbook.VBProject.VBComponents.Add vbext_ct_MSForm
End Sub
or
Sub Userform_toevoegen2()
ThisWorkbook.VBProject.VBComponents.Add 3
End Sub
A new Userform will be named automatically Userform1, Userform2, Userform3, etc.
If you prefer a custom name:

Userform: add with custom name
Sub Userform_toevoegen_naam1()
ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm).Name="invoer"
End Sub
or
Sub Userform_toevoegen_naam2()
ThisWorkbook.VBProject.VBComponents.Add(3).Name ="invoer"
End Sub

2.4.4 Classmodule: add

Sub Classmodule_toevoegen1()
ThisWorkbook.VBProject.VBComponents.Add vbext_ct_ClassModule
End Sub
or
Sub Classmodule_toevoegen2()
ThisWorkbook.VBProject.VBComponents.Add 2
End Sub
A new classmodule will be named automatically Class1, Class2, etc.
If you prefer a custom name:

Classmodule add with custom name
Sub Classmodule_toevoegen_naam1()
With ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm).Name="Klassenaam"
End Sub
or
Sub Classmodule_toevoegen_naam2()
ThisWorkbook.VBProject.VBComponents.Add(2).Name ="Klassenaam"
End Sub

2.5 Module: export

Every module can be stored as a separate file with a module specific extension.
Extensions:
- .bas is a macromodule
- .frm is a userform
- .cls is a workbook, worksheet or classmodule

These exported modules ca easily be distributed and being imported in different Excel files.

2.5.1 Macromodule: export

Sub Macromodule_export()
With ThisWorkbook.VBProject.VBComponents("Macroos")
.export"E:\OF\"&.Name & ".bas"
End With
End Sub

2.5.2 Userform: export

Sub Userform_export()
With ThisWorkbook.VBProject.VBComponents("invoer")
.export"E:\OF\"&.Name & ".frm"
End With
End Sub

2.5.3 Worksheet export

Sub Worksheet_export()
With ThisWorkbook.VBProject.VBComponents("Sheet1")
.export"E:\OF\"& .Name & ".cls"
End With
End Sub

2.5.4 Classmodule export

Sub Classmodule_export()
With ThisWorkbook.VBProject.VBComponents("Klasse1")
.export"E:\OF\"& .Name & ".cls"
End With
End Sub

2.5.5 Workbook export

Sub Workbook_export()
with ThisWorkbook.VBProject.VBComponents(1)
.export"E:\OF\"&.Name & ".cls"
End With
End Sub

2.5.6 All modules: export

If the modulenames nor the kind of module is known, you can use each module's property 'Type' to determine the extension of it's exportfile.
- Workbook (Type = 100)
- Worksheet, classmodule (Type = 2)
- Userform (Type = 3)
- Macromodule (Type = 1).
Sub Alle_modules_export()
For Each cp In ThisWorkbook.VBProject.VBComponents
cp.Export"E:\OF\"& cp.Name & Switch(cp.Type = 1, ".bas", cp.Type = 3, ".frm", cp.Type = 2, ".cls", cp.Type = 100, ".cls")
Next
End Sub

2.6 Module import

You can import files containing the VBA code of a module using:

2.6.1 Worksheet import

Sub Worksheet_import()
ThisWorkbook.VBProject.VBComponents.Import "E:\OF\sheet1.cls"
End Sub

2.6.2 Macromodule import

Sub Macromodule_import()
ThisWorkbook.VBProject.VBComponents.Import "E:\OF\Macroos.bas"
End Sub

2.6.3 Userform import

Sub Userform_import()
ThisWorkbook.VBProject.VBComponents.Import "E:\OF\invoer.frm"
End Sub

2.6.4 Classmodule import

Sub Classmodule_import()
ThisWorkbook.VBProject.VBComponents.Import "E:\OF\klasse5.cls"
End Sub

2.6.5 Workbook import

The VBA code in the workbook codemodule can be exported to a file.
Since a workbook can only contain 1 workbookmodule the import could seem to be problematic.
VBA adds the imported file automatically as a classmodule to the collection VBComponents.

2.7 Module: delete

2.7.1 Worksheet delete

Sub worksheet_delete()
ThisWorkbook.Sheets(1).Delete
End Sub

2.7.2 Macromodule delete

Sub Macromodule_delete()
With ThisWorkbook.VBProject
.VBComponents.Remove .VBComponents("Macroos")
End With
End Sub

2.7.3 Userform: delete

Sub Userform_delete()
With ThisWorkbook.VBProject
.VBComponents.Remove .VBComponents("invoer")
End With
End Sub

2.7.4 Classmodule: delete

Sub Classmodule_delete()
With ThisWorkbook.VBProject
.VBComponents.Remove .VBComponents("Klasse1")
End With
End Sub

2.7.5 All modules: delete

Sub Modules_delete()
With ThisWorkbook.VBProject
for each cp in .VBComponents
.VBcomponents.Remove cp
Next
End With
End Sub

2.8 Module: copy to another Excelfile

2.8.1 Worksheet: copy

Sub worksheet_copy()
Thisworkbook.sheets(1).copy workbooks(2).sheets1
End sub
NB. Word's VBA library contains a special method: Application.OrganizerCopy

2.8.2 Macromodule: copy

Excel's VBA library doens't contain a VBA method to copy a macromodule or a Userform directly into another Excelfile. We have to construct a 'workaround', e.g.:

Method 1

· Export the module to a '.bas' (macromodule) or '.frm' (userform) file, it's name idential to the module name
· Open a new Excel file; import the '.bas' (resp. the '.frm')-file.
Sub Macromodule_copy1()
ThisWorkbook.VBProject.VBComponents("Macroos").export "E:\Macroos.bas"
With Workbooks.Add
.VBProject.VBComponents.import "E:\Macroos.bas"
End With
End Sub
Method 2

1. Read the module's name and it's code.
2. Open a new Excel file; add a new module, it's name identicel to the module to copy.
3. Put the code of the original module into the new module (I'll discuss the methode - AddFromString- later)
Sub Macromodule_copy2()
With ThisWorkbook.VBProject.VBComponents("Macroos")
c00 = .Name
c01 = .CodeModule.Lines(1, .CodeModule.CountOfLines)
End With
With Workbooks.Add
With .VBProject.VBComponents.Add(vbext_ct_StdModule)
.Name = c00
.CodeModule.AddFromString c01
End With
End With
End Sub

2.8.3 Userform: copy

A Userform contains formcontrols and VBA code; if you want to copy both there's only 1 method available.
Sub Userform_copy1()
ThisWorkbook.VBProject.VBComponents("invoer").export "E:\ invoer.frm"
With Workbooks.Add
.VBProject.VBComponents.import "E:\invoer.frm"
End With
End Sub

2.8.4 Classmodule: copy

Method 1
Sub Classmodule_copy1()
ThisWorkbook.VBProject.VBComponents("Klasse1").export "E:\Klasse_I.cls"
With Workbooks.Add
.VBProject.VBComponents.import "E:\Klasse_I.cls"
End With
End Sub
Method 2
Sub Classmodule_copy2()
With ThisWorkbook.VBProject.VBComponents("Klasse1")
c00 = .Name
c01 = .CodeModule.Lines(1, .CodeModule.CountOfLines)
End With
With Workbooks.Add
With .VBProject.VBComponents.Add(vbext_ct_ClsModule)
.Name = c00
.CodeModule.AddFromString c01
End With
End With
End Sub

2.8.5 All macromodules and userforms: copy

Sub Modules_copy()
for each cp in ThisWorkbook.VBProject.VBComponents
if cp.type<>100 then
if Workbooks.count=1 then workbooks.Add
With workbooks(2).VBProject.VBComponents.Add(vbext_ct_MSForm)
.Name = cp.name
.CodeModule.AddFromString cp.codemodule.lines(1,cp.codemodule.countofLines)
End with
End if
Next
End Sub

2.9 Modules: move to another file

In the VBEditor' project subscreen you can drag modules (worksheets, macromodules, userforms and classmodules) form one file to another.
The 'receiving' file gets a copy of the dragged module.
So far I couldn't find a VBA equivalent for this.

VBA only has a method to move worksheets within a file or between files.

2.9.1 Worksheet: move within a workbook

Sub verplaats_worksheet()
ThisWorkbook.Sheets(1).Move ThisWorkbook.Sheets(4)
End Sub

2.9.2 Worksheet: move to another file

Sub verplaats_worksheet_naar_ander_workbook()
ThisWorkbook.Sheets(1).Move Workbook(2).Sheets(4)
End Sub

2.9.3 Userform: move to another file

Use the export-import methode to accomplish this

2.9.4 Macromodule: move to another file

Use the export-import method to accomplish this

2.9.5 Classmodule: move to another file

Use the export-import method to accomplish this

3 The content of a module

A module (workbook, worksheet, userform, macromodule, classmodule) has a property 'codemodule' that contains all it's VBA code.
This codemodule contains all VBA procedures: macros, events, functions.
There are 5 kinds of procedures:

1. Module events
The workbook, each worksheet, each classmodule and each userform has module specific events.
For example:
- the opening of a workbook (Private Sub Workbook_Open)
- the changing of a worksheet (Private Sub Worksheet_Change)
- the initialising of a userform (Private Sub Userform_Initialize)
- the initialising of a classmodule (Private Class_initialize()

These events refer to the module (workbook, worksheet, userform) itself.
The VBA code for these events is in the codemodule of the workbook, worksheet, userform, classmodule respectively.

2. Macros
All modules (workbook, worksheet, userform, macromodule, classmodule) can contain macros.

3. Function
All modules (workbook, worksheet, userform, macromodule, classmodule) can contain functions.

4. ActiveX controls
Only worksheets can contain ActiveX controls and ActiveX-eventprocedures.
ActiveX controls derive from the set ActiveX controls (e.g. textbox, optionbutton, checkbox, listbox, combobox, commandbutton, label, etc).
Don't confuse them with formcontrols in a worksheet.

5. Formcontrols
Only Userforms can contain userformcontrols and userformcontrol eventprocedures.
For instance textbox, optionbutton, checkbox, listbox, combobox, commandbutton, label, etc.
They originate from a collection formcontrols in a separate library.

  
 module
eventcode		macrofunctionActiveX
eventcode		formcontrol
eventcode
workbookxxx  
worksheetxxxx 
userformxxx x
macromodule xx  
classmodulexxx  

Procedures

A procedure (macro, event or function) isn't a separate object. That's why VBA doens't provide a methode to add, to copy, to rename/move or to delete/remove).
A procedure (macro, event or function) is nothing more than a set of lines in the codemodule.
The beginning and the end of a procedure are marked by a startline and an endline:

Macros and events:
Sub name (arguments)
End Sub
Functions:
Function name (arguments)
End Function
You can add, copy, move, adapt or delete a procedure (macro, event or function) by adapting the codelines in the codemodule.
The codemodule can be considered to be 1 page of text. The basic unit in this page is a line.
The startlines and the endlines of a procedure mark the procedures in the page.

The scope of eventprocedures is restricted to a module.
So an eventprocedure always has as scope indicator 'Private': Private Sub Workbook_Open()
Codemodule Eventprocedures can only contain builtin arguments: Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Writing in a codemodule

You can add VBA code using the method AddFromString, InsertLines or AddFromFile.
VBA has a distinct method to add an eventprocedure : CreateEventProc.
This method CreateEventProc adds the startline and endline automatically:
Private Sub ***_methode(ByVal ...)
End Sub

3.1 Codemodule

3.1.1 Read the complete VBA code

Sub Code_in_workbookmodule_lezen()
With ThisWorkbook.VBProject.VBComponents(1).CodeModule
MsgBox .Lines(1, .CountOfLines)
End With
End Sub

3.1.2 Read a part of the VBA-code



' all lines except the last 20 lines
' all lines except the first 10 lines
' line 15 to line 45
' all declarationlines
Sub Code_in workbook_deels_lezen()
With ThisWorkbook.VBProject.VBComponents(2).CodeModule
c00 = .Lines(1, .CountOfLines-20)
c01 =.Lines(10,.CountOflines-10)
c02 =.Lines(15,30)
c03 =.Lines(1,.CountOfDeclarationLines)
End With
End Sub

3.1.3 Delete all code from a codemodule

Sub Code_workbookmodule_delete()
With ThisWorkbook.VBProject.VBComponents(1).CodeModule
.DeleteLines 1, .CountOfLines
End With
End Sub

3.2 Macromodule

3.2.1 Macro

3.2.1.1 Read all macros in a module

VBA has no method to show all VBA procedures in a module.
We construct that method ourselves: every line in a module has the property 'ProcOfLine' that returns the name of the procedure (macro, function) it is part in.
If we constitute a list of unique names based on that property the result will be a list of all macros.
We can put that list in a combobox in a userform.
Sub Macros_van_macromodule_in_userform_combobox()
For j = 1 To ThisWorkbook.VBProject.VBComponents("Macroos").CodeModule.CountOfLines
c02 = ThisWorkbook.VBProject.VBComponents(1).CodeModule.ProcOfLine(j, 0)
If InStr(c01, c02) = 0 Then c01 = c01 & "|" & c02
Next
keus1.List = Split(Mid(c01, 2), "|")
End Sub

3.2.1.2 Macro: find

Sub Macro_zoeken()
MsgBox "macro3" & IIf(ActiveWorkbook.VBProject.VBComponents("Macroos").CodeModule.Find("Sub macro3(", 1, 1, -1, -1), "", "Not") & "found."
End Sub

3.2.1.3 Macro: add

You can use 3 VBA methods;
- AddFromString
- InsertLines
- AddFromFile

- Method AddFromString: the textstring containing VBA code will always be added to the beginning of a codemodule.
Sub Macro_in_macromodule_maken()
c00 =replace(Replace(Replace("Sub nieuwe_macro()#*MsgBox ^QED ^#End Sub", "#", vbCr), "*", vbTab),"^",chr(34))
ThisWorkbook.VBProject.VBComponents("Macroos").CodeModule.AddFromString c00
End Sub
NB.
* A macroname can't contain spaces.
* In the example a string is being made, before adding it to the codemodule.
* To avoid &'s in the code I used 'replace'.

"Sub nieuwe_macro()" & vbCr & vbTab &"MsgBox " & Chr(34) & "QED" & Chr(34) & vbCr & "End Sub"

- Method Insertlines

You can indicate where the starting line of the new code has to begin. So, using insertlines you can specify whether the code has to be added at the beginning, at the end or somewhere between the existing lines.
Therefore it can be necessary to check whether the new lines won't interfere with existing ones in the codemodule.

at the beginning of the macromodule
Sub Macro_in_macromodule_maken2()
c00 =replace(Replace(Replace("Sub nieuwe_macro()#*MsgBox ^QED ^#End Sub", "#", vbCr), "*", vbTab),"^",chr(34))
ThisWorkbook.VBProject.VBComponents("Macroos").CodeModule.Insertlines 1, c00
End Sub
at the end of the macromodule
Sub Macro_in_macromodule_maken2()
c00 =replace(Replace(Replace("Sub nieuwe_macro()#*MsgBox ^QED ^#End Sub", "#", vbCr), "*", vbTab),"^",chr(34))
ThisWorkbook.VBProject.VBComponents("Macroos").CodeModule.Insertlines ThisWorkbook.VBProject.VBComponents("Macroos").CodeModule.countoflines, c00
End Sub
- Method AddFromFile

You can import all VBA code form an ASCII file integrally.
With this method it's not possible to import a part lf the file.
Sub Macro_in_macromodule_maken3()
ThisWorkbook.VBProject.VBComponents("Macroos").CodeModule.Addfromfile "E:\OF\macro1.txt"
End Sub

3.2.1.4 Macro: read the code

If you know a macro's name you can retrieve all the macrocode using
- ProcStartLine: the first line of a specified macro and
- ProcCountLines: the number of codelines of a specified macro
Sub Macro_read_code()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
c00 = .Lines(.ProcStartLine("macro3", 0), .ProcCountLines("macro3", 0))
End With
End Sub

3.2.1.5 Macro: copy

Sub Macro_copy()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
c00 = .Lines(.ProcStartLine("macro3", 0), .ProcCountLines("macro3", 0))
End With
ThisWorkbook.VBProject.VBComponents("Macromodule_2").AddFromString c00
End Sub

3.2.1.6 Macro: replace code

Sub Macro_code_vervangen()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
.DeleteLines .ProcStartLine("macro3", 0) + 1, .ProcCountLines("macro3", 0) - 2
.InsertLines .ProcStartLine("macro3", 0) + 1, "c00 = " & Chr(34) & "Dit is de nieuwste tekst"
End With
End Sub

3.2.1.7 Macro: rename

Sub Macro_hernoemen()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
.ReplaceLine .ProcStartLine("macro3", 0), Replace(.Lines(.ProcStartLine("macro3", 0), 1), "macro3", "Macro37a")
End With
End Sub

3.2.1.8 Macro: delete

Sub Macro_delete()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
.DeleteLines .ProcStartLine("macro3", 0), .ProcCountLines("macro3", 0)
End With
End Sub

3.2.1.9 Macro: move to another module

Sub Macro_verplaatsen()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
c00 = .Lines(.ProcStartLine("macro3", 0), .ProcCountLines("macro3", 0))
.DeleteLines .ProcStartLine("macro3", 0), .ProcCountLines("macro3", 0)
End With
ThisWorkbook.VBProject.VBComponents("Macromodule_2").AddFromString c00
End Sub

3.2.2 Functions

3.2.2.1 All functions in a module

The startline of every function contains the word 'Function'; after filtering all those lines the functionname s will befiltered.
The list of functions will be put into an ActiveX listbox ('keus1') in worksheet 'Sheet1'.
Sub Alle_functions_in_macromodule()
c00 = "Function "
With ThisWorkbook.VBProject.VBComponents("Macroos").CodeModule
c01=.Lines(1, .CountOfLines)
Sheet1.keus1.List = Split(Replace(Replace(Join(Filter(Split(Join(Filter(Filter(Split(c01, vbCr & Chr(10)), c0), "=", False), "("), "("), c0), "|"),
c00, ""),"Private ",""), "|")
End With
End Sub

3.2.2.2 Function: find

Sub Function_zoeken()
MsgBox "function2" & iif(With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule.Find("function2","","not ") & "found"
End Sub

3.2.2.3 Function: add

Method AddFromString
Sub Function_maken1()
c00 =replace(Replace(Replace("Function function1()#*MsgBox ^QED ^#End Sub", "#", vbCr), "*", vbTab),"^",chr(34))
ThisWorkbook.VBProject.VBComponents(1).CodeModule.AddFromString c00
End Sub
Method Insertlines
Sub Function_maken2()
c00 =replace(Replace(Replace("Function function1()#*MsgBox ^QED ^#End Sub", "#", vbCr), "*", vbTab),"^",chr(34))
ThisWorkbook.VBProject.VBComponents("Macroos").CodeModule.Insertlines 1, c00
End Sub
Method AddFromFile
Sub Function_maken3()
ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule.Addfromfile "E:\OF\function.txt"
End Sub

3.2.2.4 Function: read the code

Sub Function_code_lezen()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
c00 = .Lines(.ProcStartLine("function2", 0), .ProcCountLines("function2", 0))
End With
End Sub

3.2.2.5 Function: copy

Sub Function_copy()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
c00 = .Lines(.ProcStartLine("function2", 0), .ProcCountLines("function2", 0))
End With
ThisWorkbook.VBProject.VBComponents("Macroos_2").CodeModule.AddFromString c00
End Sub

3.2.2.6 Function: replace code

Sub Function_code_vervangen()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
.DeleteLines .ProcStartLine("function2", 0) + 1, .ProcCountLines("function2", 0) - 2
.InsertLines .ProcStartLine("function2", 0) + 1,"c00 = " & Chr(34) & "Dit is de nieuwste tekst"
End With
End Sub

3.2.2.7 Function: rename

Sub Function_hernoemen()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
.ReplaceLine .ProcStartLine("function2", 0), Replace(.Lines(.ProcStartLine("function2", 0), 1), "function2", "Function27a")
End With
End Sub

3.2.2.8 Function: delete

Sub Function_delete()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
.DeleteLines .ProcStartLine("function2", 0), .ProcCountLines("function2", 0)
End With
End Sub

3.2.2.9 Function: move

Sub Function_verplaatsen()
With ThisWorkbook.VBProject.VBComponents("Macroos").codemodule
c00 = .Lines(.ProcStartLine("function2", 0), .ProcCountLines("function2", 0))
.DeleteLines .ProcStartLine("function2", 0), .ProcCountLines("function2", 0)
End With
ThisWorkbook.VBProject.VBComponents("Macroos_2").AddFromString c00
End Sub

3.3 Workbook

3.3.1 Macros

See the description of macros in a macromodule.

3.3.2 Functions

See the description of functions in a macromodule.

3.3.3 Eventprocedures

A workbook can contain these builtin events:

ActivateAddinInstallAddinUninstallBeforeClose
BeforePrintBeforeSaveDeactivateNewSheet
OpenSheetActivateSheetBeforeDoubleClickSheetBeforeRightClick
SheetCalculateSheetChangeSheetDeactivateSheetFollowhyperlink
SheetSelectionChangeWindowActivateWindoDeactivateWindowResize

3.3.3.1 All eventprocedures in a workbook

The startline of each eventprocedure in a workbook contains the string 'Private Sub Workbook_'.
We can filter all lines in the codemodule that contain that string.
Sub alle_eventprocedures_in_workbook()
With ThisWorkbook.VBProject.VBComponents(1).CodeModule
MsgBox join(filter( split(.Lines(1, .CountOfLines),vbCrLf),"Private Sub Workbook_"),vbLf)
end with
end sub

3.3.3.2 Eventprocedure: find

Sub Workbook_eventprocedure_zoeken()
msgbox "Workbook_Open" & iif(ThisWorkbook.VBProject.VBComponents(1).CodeModule.Find("Sub Workbook_Open", 1, 1, -1, -1),"", "not ") & "found"
End Sub

3.3.3.3 Eventprocedure: read

Het argument vbext_pk_Proc is equivalent to 0
Sub Workbook_eventprocedure_code_lezen()
With ThisWorkbook.VBProject.VBComponents(1).CodeModule
c01= .Lines(.ProcStartLine("Workbook_Open",0),.ProcCountlines("Workbook_Open",0))
End With
End Sub

3.3.3.4 Eventprocedure: delete

Sub Workbook_eventprocedure_delete()
With ThisWorkbook.VBProject.VBComponents(1).CodeModule
.DeleteLines .ProcStartLine("Workbook_Open",0),.ProcCountlines("Workbook_Open",0)
End With
End Sub

3.3.3.5 Eventprocedure: add

Sub Workbook_eventprocedure_toevoegen()
With ThisWorkbook.VBProject.VBComponents(1).CodeModule
.InsertLines .CreateEventProc("Open", "Workbook") + 1, vbTab & "MsgBox " & Chr(34) & "This is a new workbook"
End With
End Sub

3.3.3.6 Eventprocedure: copy

A combination of reading and writing an eventprocedure.
Sub Workbook_eventprocedure_copy()
With ThisWorkbook.VBProject.VBComponents(1).CodeModule
c01= .Lines(.ProcStartLine("Workbook_Open",0),.ProcCountlines("Workbook_Open",0))
End With
With Workbooks(2).VBProject.VBComponents(1).CodeModule
.AddFromString c01
End With
End Sub

3.3.3.7 Eventprocedure: move

A combination of reading, writing and deleting an eventprocedure.
Sub Workbook_eventprocedure_verplaatsen()
With ThisWorkbook.VBProject.VBComponents(1).CodeModule
c01= .Lines(.ProcStartLine("Workbook_Open",0),.ProcCountlines("Workbook_Open",0)
) .deletelines .ProcStartLine("Workbook_Open",0),.ProcCountlines("Workbook_Open",0)
End With With Workbooks(2).VBProject.VBComponents(1).CodeModule
.AddFromString c01
End With
End Sub

3.4 Worksheet

3.4.1 Macros

See the desciption of macros in a macromodule.

3.4.2 Functions

See the desciption of functions in a macromodule.

3.4.3 Eventprocedures

A worksheet can contain these events:

ActivateBeforeDoubleClickBeforeRightClickCalculate
ChangeDeactivateFollowHyperlinkSelectionChange

3.4.3.1 All eventprocedures in a worksheet

The startline of every eventprocedure in a worksheet contains the string 'Private Sub Worksheet_'
Sub Worksheet_alle_eventprocedures()
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
MsgBox join(filter( split(.Lines(1, .CountOfLines),vbCrLf),"Private Sub Worksheet_"),vbLf)
end with
end sub

3.4.3.2 Eventprocedure: find

Sub Worksheet_eventprocedure_zoeken()
msgbox "Worksheet_Change " & iif(ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule.Find(" Sub Worksheet_Change", 1, 1, -1, -1),"", "not ") & "found"
End Sub

3.4.3.3 Eventprocedure: read

Sub Worksheet_eventprocedure_code_lezen()
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
c01= .Lines(.ProcStartLine("Worksheet_Change",0),.ProcCountlines("Worksheet_Change",0))
End With
End Sub

3.4.3.4 Eventprocedure: delete

Sub Worksheet_eventprocedure_delete()
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
.DeleteLines .ProcStartLine("Worksheet_Change",0),.ProcCountlines("Worksheet_Change",0)
End With
End Sub

3.4.3.5 Eventprocedure: add

Sub Worksheet_eventprocedure_toevoegen()
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
.InsertLines .CreateEventProc("Change", "Worksheet") + 1,vbTab & "MsgBox " & Chr(34) & "This is a new workbook"
End With
End Sub

3.4.3.6 Eventprocedure: copy

A combination of reading and writing an eventprocedure.
Sub Worksheet_eventprocedure_copy()
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
c01= .Lines(.ProcStartLine("Worksheet_Change",0),.ProcCountlines("Worksheet_Change",0))
End With
With Workbooks(2).VBProject.VBComponents("Sheet2").CodeModule
.AddFromString c01
End With
End Sub

3.4.3.7 Eventprocedure: move

A combination of reading, writing and deleting an eventprocedure.
Sub Worksheet_eventprocedure_verplaatsen()
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
c01= .Lines(.ProcStartLine("Worksheet_Change",0),.ProcCountlines("Worksheet_Change",0)
) .deletelines .ProcStartLine("Worksheet_Change",0),.ProcCountlines("Worksheet_Change",0)
End With
With Workbooks(2).VBProject.VBComponents("Sheet2").CodeModule
.AddFromString c01
End With
End Sub

4 ActiveX controls

You can put ActiveX controls in a worksheet.
You can add code to a activeX control event (e.g. Click, Change, Activate).
The VBA code for an ActiveX control event is part of the worksheet codemodule.

4.1 All ActiveX controls in a worksheet

Sub alle_ActiveX_controls()
For Each cl In Sheets("Sheet1").OLEObjects
c01 = c01 & vbLf & cl.Name
Next
MsgBox c01
End Sub

4.2 ActiveX control: add

Sub ActiveX_object_toevoegen()
With sheets("Sheet1")
With .OLEObjects.Add("Forms.CommandButton.1",,,,,,,40,60,40,24)
.Name="knop_vervolg"
With .Object
.Caption="Mededeling"
.Font.Size=9
.ForeColor=vbBlue
End With
End With
End With
End With
End Sub

4.2.1 Worksheet TextBox: add

Sub ActiveX_tekstvak_toevoegen()
Sheets("Sheet1").OLEObjects.Add("Forms.Textbox.1", , , , , , , 40, 60, 40, 24).Name ="tekst_1"
End Sub

4.2.2 Worksheet OptionButton: add

Sub ActiveX_keuzerondje_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.OptionButton.1").Name =""keus_1""
End Sub

4.2.3 Worksheet CheckBox: add

Sub ActiveX_selectievak_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.CheckBox.1").Name ="selectie_1"
End Sub

4.2.4 Worksheet Label: add

Sub ActiveX_bijschrift_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.Label.1").Name ="bijschrift_1"
End Sub

4.2.5 Worksheet CommandButton: add

Sub ActiveX_opdrachtknop_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.CommandButton.1").Name ="opdracht_1"
End Sub

4.2.6 Worksheet ToggleButton: add

Sub ActiveX_wisselknop_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.ToggleButton.1").Name ="wissel_1"
End Sub

4.2.7 Worksheet SpinButton: add

Sub ActiveX_kringveld_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.SpinButton.1").Name ="spinner_1"
End Sub

4.2.8 Worksheet ComboBox: add

Sub ActiveX_keuzelijst_met_invoervak_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.ComboBox.1").Name ="uitklap_1"
End Sub

4.2.9 Worksheet ListBox: add

Sub ActiveX_keuzelijst_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.ListBox.1").Name ="keuzen_1"
End Sub

4.2.10 Worksheet Frame: add

Sub ActiveX_groepsvak_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.Frame.1").Name ="kader_1"
End Sub

4.2.11 Worksheet Tabstrip: add

Sub ActiveX_tabstrook_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.TabStrip.1").Name ="tab_1"
End Sub

4.2.12 Worksheet MultiPage: add

Sub ActiveX_multipage_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.MultiPage.1").Name ="multi_1"
End Sub

4.2.13 Worksheet ScrollBar: add

Sub ActiveX_schuifbalk_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.ScrollBar.1").Name ="schuif_1"
End Sub

4.2.14 Worksheet Image: add

Sub ActiveX_afbeelding_toevoegen()
Sheets("Sheet1").OLEObjects.Add("forms.Image.1").Name ="plaatje_1"
End Sub

4.3 ActiveX control: copy

Sub AciveX_kopie()
Sheets("Sheet1").OLEObjects("tekstvak1").Copy
Sheets("Sheet2").Paste
End Sub

4.4 ActiveX control: rename

Sub ActiveX_hernoemen()
Sheets("Sheet1").OLEObjects("knop_vervolg").Name = "knop_einde"
End Sub

4.5 ActiveX control: delete

Sub ActiveX_delete()
Sheets("Sheet1").OLEObjects("knop_vervolg").delete
End Sub

4.6 ActiveX eventcode

4.6.1 ActiveX eventcode: find

Sub ActiveX_eventcode_zoeken
MsgBox "knop_vervolg_Click" & ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule.Find("Sub knop_vervolg_Click(","","niet ") & "found
End Sub

4.6.2 ActiveX eventcode: read

Sub ActiveX_eventcode_lezen
ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
MsgBox .Lines(.ProcStartLine("knop_vervolg_Click",0), .ProcCountLines("knop_vervolg_Click",0))
End with
End Sub

4.6.3 ActiveX eventcode: add

Sub ActiveX_eventcode_toevoegen()
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
.InsertLines .CreateEventProc("Click", "knop_vervolg") + 1,vbTab & "MsgBox " & Chr(34) & "Je hebt knop_vervolg aangeklikt"
End With
End Sub

4.6.4 ActiveX eventcode: copy

to similar controls (e.g. 'textbox1' and 'textbox2') in the same worksheet with identical code
Sub ActiveX_eventcode_kopie()
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
.AddFromString replace( .Lines(.ProcStartLine("textbox1_Change",0), .ProcCountLines("textbox1_Change",0)),"textbox1","textbox2")
End With
End Sub
copy eventcode to different sheets: from sheet1 to sheet2
Sub ActiveX_eventcode_copy2()
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
c00 = .Lines(.ProcStartLine("tekstvak1_Change",0), .ProcCountLines("textbox1_Change",0))
End With
ThisWorkbook.VBProject.VBComponents("Sheet2").CodeModule.AddFromString c00
End Sub

4.6.5 ActiveX eventcode: move

With ThisWorkbook.VBProject
with .VBComponents("Sheet1").codemodule
c00 =.lines(.ProcStartLine("knop_vervolg_Click",0), .ProcCountLines("knop_vervolg_Click",0))
.DeleteLines .ProcStartLine("knop_vervolg_Click",0), .ProcCountLines("knop_vervolg_Click",0)
end with
.VBComponents("Sheet2").codemodule.AddFromString c0.
End With

4.6.6 ActiveX eventcode: replace

Sub ActiveX_code_vervangen1()
With ThisWorkbook.VBProject.VBComponents("Sheet1").codemodule
.ReplaceLine .ProcStartLine("knop_vervolg_Click",0), Replace(.Lines(.ProcStartLine("knop_vervolg_Click",0), 1), "_vervolg", "_einde")
End With End Sub
or
Sub ActiveX_code_vervangen2()
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
.DeleteLines .ProcStartLine("knop_vervolg_Click",0) + 1, .ProcCountLines("knop_vervolg_Click",0) - 2
.InsertLines .ProcStartLine("knop_vervolg_Click",0) + 1,"c00 = " & Chr(34) & "Dit is de nieuwste tekst"
End With End Sub

4.6.7 ActiveX eventcode: delete

Sub ActiveX_code_delete()
With ThisWorkbook.VBProject.VBComponents("Sheet1").codemodule
.DeleteLines .ProcStartLine("knop_vervolg_Click",0), .ProcCountLines("knop_vervolg_Click",0)
End With
End Sub

5 Userform

5.1 Macros

See the description of macros in a macromodule.

5.2 Functions

See the description of functions in a macromodule.

5.3 Eventprocedures

A userform can contain these builtin events:

ActivateAddControlBeforeDragoverBeforeDropOrPasteClick
DblClickDeActivateErrorInitializeKeyDown
KeyPressKeyUpLayOutMouseDownMouseMove
MouseUpQueryCloseRemoveControlResizeScroll
TerminateZoom   

5.3.1 All eventprocedures in a Userform

The startline of each eventprocedure in a userform contains the string ' Sub Userform_'
Sub alle_eventprocedures_in_userform()
With ThisWorkbook.VBProject.VBComponents("invoer").CodeModule
MsgBox join(filter( split(.Lines(1, .CountOfLines),vbCrLf),"Sub UserForm_"),vbLf)
End With
End Sub

5.3.2 Eventprocedure: find

Sub eventprocedure_zoeken()
msgbox"Userform_Initialize" & iif(ThisWorkbook.VBProject.VBComponents("invoer").CodeModule.Find("Sub Userform_Initialize(", 1, 1, -1, -1),"", "niet") & "found"
End Sub

5.3.3 Eventprocedure: read

Sub eventprocedure_lezen()
With ThisWorkbook.VBProject.VBComponents("invoer").CodeModule
c01 = .Lines(.ProcStartLine("Userform_Initialize", 0), .ProcCountLines("Userform_Initialize", 0))
End With
End Sub

5.3.4 Eventprocedure: delete

Sub eventprocedure_delete()
With ThisWorkbook.VBProject.VBComponents("invoer").CodeModule
.DeleteLines .ProcStartLine("Userform_Initialize",0),.ProcCountlines("Userform_Initialize",0)
End With
End Sub

5.3.5 Eventprocedure: add

Sub eventprocedure_toevoegen()
With ThisWorkbook.VBProject.VBComponents("invoer").CodeModule
.InsertLines .CreateEventProc("Initialize", "Userform") + 1,vbTab & "MsgBox " & Chr(34) & "This is a new workbook"
End With
End Sub

5.3.6 Eventprocedure: copy

A combination of reading and writng an eventprocedure.
Sub eventprocedure_copy()
With ThisWorkbook.VBProject.VBComponents("invoer").CodeModule
c01= .Lines(.ProcStartLine("Userform_Initialize",0),.ProcCountlines("Userform_Initialize",0))
End With
With Workbooks(2).VBProject.VBComponents("invoer").CodeModule
.AddFromString c01
End With
End Sub

5.3.7 Eventprocedure: move

A combination of reading, writing and deleting an eventprocedure.
Sub eventprocedure_verplaatsen()
With ThisWorkbook.VBProject.VBComponents("invoer").CodeModule
c01= .Lines(.ProcStartLine("Userform_Initialize",0),.ProcCountlines("Userform_Initialize",0))
.deletelines .ProcStartLine("Userform_Initialize",0),.ProcCountlines("Userform_Initialize",0)
End With
With Workbooks(2).VBProject.VBComponents("invoer").CodeModule
.AddFromString c01
End With
End Sub

5.4 Controls in a Userform

5.4.1 All controls

Sub alle_besturingselementen()
For Each ct In ThisWorkbook.VBProject.VBComponents("invoer").Designer.Controls
c01 = c01 & vbLf & ct.Name
Next
msgbox c01 , , "controls in this userform"
End Sub
or
Sub Userform_alle_besturingselementen()
For Each ct InUserForm1.Controls
c01 = c01 & vbLf & ct.Name
Next
MsgBox c01, , "besturingselementen in" &UserForm1.Name
End Sub

5.4.2 Control: add

Sub Userform_besturingselement_toevoegen()
With ThisWorkbook.VBProject.VBComponents("invoer").Designer.Controls.Add("forms.commandButton.1", "knop_einde")
.Caption = "Stop"
.Top =120
.Left =120
End With
End Sub

5.4.2.1 Userform TextBox: add

Sub Userform_tekstvak_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.Textbox.1").Name = "tekst_1"
End Sub

5.4.2.2 Userform OptionButton: add

Sub Userform_keuzerondje_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.OptionButton.1").Name = "keus_1"
End Sub

5.4.2.3 Userform CheckBox: add

Sub Userform_selectievak_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.CheckBox.1").Name = "selectie_1"
End Sub

5.4.2.4 Userform Label: add

Sub Userform_bijschrift_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.Label.1").Name = "bijschrift_1"
End Sub

5.4.2.5 Userform CommandButton: add

Sub Userform_opdrachtknop_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.CommandButton.1").Name = "opdracht_1"
End Sub

5.4.2.6 Userform ToggleButton: add

Sub Userform_wisselknop_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.ToggleButton.1").Name = "wissel_1"
End Sub

5.4.2.7 Userform SpinButton: add

Sub Userform_kringveld_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.SpinButton.1").Name = "spinner_1"
End Sub

5.4.2.8 Userform ComboBox: add

Sub Userform_keuzelijst_met_invoervak_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.ComboBox.1").Name = "uitklap_1"
End Sub

5.4.2.9 Userform ListBox: add

Sub Userform_keuzelijst_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.ListBox.1").Name = "keuzen_1"
End Sub

5.4.2.10 Userform Frame: add

Sub Userform_groepsvak_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.Frame.1").Name = "kader_1"
End Sub

5.4.2.11 Userform Tabstrip: add

Sub Userform_tabstrook_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.TabStrip.1").Name = "tab_1"
End Sub

5.4.2.12 Userform MultiPage: add

Sub Userform_multipage_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.MultiPage.1").Name = "multi_1"
End Sub

5.4.2.13 Userform ScrollBar: add

Sub Userform_schuifbalk_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.ScrollBar.1").Name = "schuif_1"
End Sub

5.4.2.14 Userform Image: add

Sub Userform_afbeelding_toevoegen()
ThisWorkbook.VBProject.VBComponents("invoer").designer.Controls.Add("forms.Image.1").Name = "plaatje_1"
End Sub

5.4.3 Userform control: copy

from Userform1 ("input") to Userform2 ("input2")
Sub Userform_besturingselement_copy()
With ThisWorkbook.VBProject
With .VBComponents("input").Designer
.Controls("knop_einde").SetFocus
.Copy
End With
.VBComponents("input2").Designer.Paste
End With
End Sub

5.4.4 Userform control: rename

Sub Userform_besturingselement_hernoemen()
With ThisWorkbook.VBProject.VBComponents("input")
.Designer.Controls("knop_einde").Name = "knop_slot"
End With
End Sub

5.4.5 Userform control: delete

Sub Userform_besturingselement_delete()
With ThisWorkbook.VBProject.VBComponents("invoer")
.Designer.Controls.Remove "knop_einde"
End With
End Sub

5.4.6 Userform control: events

5.4.6.1 All event code of a certain Userform control

Sub Userform_alle_besturingselementcode_van_een_bepaald_besturingselement()
With ThisWorkbook.VBProject.VBComponents("invoer").CodeModule
sn = Filter(Split(.Lines(1, .countoflines), vbCrLf), "Sub TextBox1_")
For j = 0 To UBound(sn)(br/>
sn(j) = .Lines(.ProcStartLine(Split(Split(sn(j), "Sub ")(1), "(")(0), 0), .ProcCountLines(Split(Split(sn(j), "Sub ")(1), "(")(0), 0))
Next
End With
End Sub

5.4.6.2 Userform control eventcode: find

Sub besturingselement_code_zoeken()
With ThisWorkbook.VBProject.VBComponents("invoer").CodeModule
MsgBox "knop_einde_Click event" & iif(.Find("Sub knop_einde_Click("),"","niet ") & "found"
End with
End Sub

5.4.6.3 Userform control eventcode: read

Sub besturingselement_code_lezen()
With ThisWorkbook.VBProject.VBComponents("invoer").CodeModule
MsgBox .Lines(.ProcStartLine("knop_einde_Click",0), .ProcCountLines("knop_einde_Click",0))
End with
End Sub

5.4.6.4 Userform control eventcode: add

Sub besturingselementcode_toevoegen()
With ThisWorkbook.VBProject.VBComponents("invoer").CodeModule
.InsertLines .CreateEventProc("Click","knop_einde")=1,"MsgBox " & chr(34) & "Controleer alle gegevens"
End With
End Sub

5.4.6.5 Userform control eventcode: copy

copy the eventcode of control Knop_einde_click in Userform1 ("input") to Userform2 ("input2")
Sub besturingselement_code_kopie()
With ThisWorkbook.VBProject
With .VBComponents("input").CodeModule
c00 = .Lines(.ProcStartLine("knop_einde_Click",0), .ProcCountLines("knop_einde_Click",0))
End With
.VBComponents("input2").CodeModule.AddFromString c00
End with
End Sub
to similar controls in the same Userform("input") with identical code: eg. textbox1 and textbox2
Sub besturingselement_code_kopie2()
With ThisWorkbook.VBProject.VBComponents("input").CodeModule
.AddFromString replace(.Lines(.ProcStartLine("textbox1_Change",0), .ProcCountLines("tekstvak1_Change",0)),"textbox1","textbox2")
End With
End Sub

5.4.6.6 Userform control eventcode: replace

The startline and the endline of the eventprocedure remain; the lines inbetween ere being replaced.
Sub besturingselement_code_vervangen()
With ThisWorkbook.VBProject.VBComponents("input").CodeModule
.ReplaceLines .ProcStartLine("knop_einde_Click",0) + 1, .ProcCountLines("knop_einde_Click",0) - 2, .ProcStartLine("knop_einde_Click",0) + 1,"MsgBox " & Chr(34) & "Dit is de nieuwste tekst"
End With
End Sub

5.4.6.7 Userform control eventcode: adapt

Sub besturingselement_code_aanpassen()
With ThisWorkbook.VBProject.VBComponents("invoer").codemodule
.ReplaceLine .ProcStartLine("knop_einde_Click",0), Replace(.Lines(.ProcStartLine("knop_einde_Click",0), 1), "_einde", "_slot")
End With
End Sub

5.4.6.8 Userform control eventcode: delete

Sub besturingselement_code_delete()
With ThisWorkbook.VBProject.VBComponents("invoer").codemodule
.DeleteLines .ProcStartLine("knop_vervolg_Click",0), .ProcCountLines("knop_vervolg_Click",0)
End With
End Sub