Add problem
 

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

Add problem
 

Are you adding to a UserForm or the sheet?


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=101878

Add problem
 

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").C odeModule
.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


--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=101878

Add problem
 

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


"royUK" wrote in message
...

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").C odeModule
.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


--------------------


--
royUK

Hope that helps, RoyUK
For tips & examples visit my 'web site' (http://www.excel-it.com/)
------------------------------------------------------------------------
royUK's Profile: http://www.thecodecage.com/forumz/member.php?userid=15
View this thread:
http://www.thecodecage.com/forumz/sh...d.php?t=101878