My application is to programmatically create new excel sheet in current workbook on which an activeX command button and its associated event code are created (also programmatically). When this command button is clicked, several activeX checkboxes are created on the same sheet. If the command button is clicked again, the macro should be able to delete all existing checkboxes and create same number of checkboxes again as previously created. I used a standard for loop to create the desired number of checkboxes. On my computer, the macro works if I loop the iterator of that for loop from 1 to 3. However, if I literally change the looping cycles from 3 to 10 (for i =1 to 10), it only works as expected when I click the commandbutton once, the next time I click the commandbutton, the macro does nothing and when I try to compile the code, the compile error: 'object library invalid or contains references to object definitions that could not be found' arises.
All the references linked to my VBA project are:
- Visual Basic for Appications
- Microsoft Excel 14.0 object library
- OLE Automation
- Microsoft Office 14.0 object library
- Microsoft Forms 2.0 object library
- Microsoft Visual Basic for Applications Extensibility 5.3
My computer runs the OS Windows 7 enterprise, MS Excel 2010 and VBA 7.0
What shown below is how I implement my excel vba application:
1. Open new Excel file with sheet1 only (delete the default sheet 2 & sheet3)
2. Create a commandbutton (captioned: cb1, named: cb1)
3. vba code for this sheet is as below:
[VBA]Private Sub cb1_Click()
Module1.thu1
End Sub[/VBA]
where Module1 is a code module inserted to my project
4. vba code for this module is:
[VBA]Public Sub thu1()
Dim wscount As Integer
Dim wks As Sheets
Dim mybutton As OLEObject
Dim mystring As String
Dim code As String
Dim VBP As VBProject
Dim VBCs As VBComponents
Dim VBC As VBComponent
Set wks = ActiveWorkbook.Worksheets
'create new Excel worksheet in the active workbook that is a copy of the active worksheet
wks.Add After:=ActiveSheet
wscount = Worksheets.count
Worksheets.Item(wscount).Select
'create new cmdbutton
Set mybutton = ActiveSheet.OLEObjects.Add(ClassType:="forms.CommandButton.1")
'specify geometric properties
With mybutton
.Left = 150
.Top = 100
.Width = 100
.Height = 20
.Object.Caption = "Add checkboxes"
End With
'add command button code
'create the code associated with the newly generated commandbutton
'code text for CheckBox:
code = "Sub CommandButton1_Click()" & vbCrLf 'Sub CommandButton1_Click()
code = code & "Module1.thu2" & vbCrLf ' Module1.thu2
code = code & "End Sub" 'End Sub
'add this exceprt of code to the active sheet
mystring = ActiveSheet.Name
With ActiveWorkbook.VBProject.VBComponents(mystring).CodeModule
.AddFromString (code)
End With
End Sub
Public Sub thu2()
Dim obj As OLEObject
Dim mystring As String
Dim code As String
Dim i As Integer
Dim VBP As VBProject
Dim VBCs As VBComponents
Dim VBC As VBComponent
'Delete all existing checkboxes
For Each obj In ActiveSheet.OLEObjects
If obj.progID = "Forms.CheckBox.1" Then obj.Delete
Next obj
' Here I loop from 1 to 3 to create 3 checkboxes and it works on my computer,
' but on the same computer if I loop from 1 to 10 to create 10 checkboxes then it stops functioning
For i = 1 To 3
addcheckbox i
Next i
End Sub
Public Sub addcheckbox(ByVal i)
Dim mybox As OLEObject
'create CheckBox
Set mybox = ActiveSheet.OLEObjects.Add(ClassType:="forms.CheckBox.1")
'specify geometric properties
With mybox
.Left = 380
.Top = 15 * i
.Width = 100
.Height = 18
.Name = "CheckBox" & i
End With
End Sub[/VBA]
I really don't know why it works that inconsistent??
Has anyone experienced the same thing? Can anyone explain to me the reason causing this trouble?
Many thanks in advance.
Reply With Quote
