Add problem

E

E.Z.

Hi,
Can some1 tell me what's wrong with this line:

UserForm1.Controls.Add "Excel.Sheet", "List1", True

I get Run time error - Invalid argument

Tnx in advance
 
R

royUK

Here's some sample code to add a userform with a listbox & some code

Code:
--------------------


Option Explicit
Dim uFrm
Sub uFrm_Create()

Dim Lbox As MSForms.Control


uFrm_Remove ' If Userform1 exists then remove it.

With ThisWorkbook.VBProject.VBComponents
Set uFrm = .Add(3).Designer ' Create & set form designer
End With

With uFrm.Controls
Set Lbox = .Add("Forms.listbox.1")

With Lbox
.ColumnCount = 1
.Top = 12
.Left = 6
.Width = 100
.Height = 40
End With
End With
ufrm_Code
ufrm_Show
uFrm_Remove
End Sub
Sub ufrm_Show()

With UserForm1.ListBox1
Dim ws As Worksheet
On Error Resume Next
For Each ws In ActiveWorkbook.Sheets

If ws.Visible And Application.CountA(ws.Cells) > 0 Then
With UserForm1.ListBox1
.AddItem ws.Name
End With
End If
Next ws
End With
With UserForm1
.Caption = "Sheet List"
.Height = 95
.Width = 125
.Show
End With

On Error GoTo 0
End Sub
Function ufrm_Code()
With ThisWorkbook.VBProject.VBComponents("Userform1").CodeModule
.InsertLines 2, "Private Sub ListBox1_Click()"
.InsertLines 3, "Sheets(Me.ListBox1.Value).Activate"
.InsertLines 4, "End Sub"
End With
End Function
Function uFrm_Remove()
On Error Resume Next
With ThisWorkbook.VBProject
.VBComponents.Remove .VBComponents("Userform1")
End With
Err.Clear
End Function
 
E

E.Z.

Tnx,
It's a UserForm and the problem is with adding an "Excel.Sheet" control
class.
np with adding a ListBox
 
Top