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