List box problem

[v_gyku]

I am trying this code (given by DRJ):

THere is a browse button on the form.
When user will click it.. file open window will come.
User will select a workbook
Respective worksheet names will be displayed in a list box....

THis code is giving error as object variable or propertynot set
NOt adding the sheetnames to the list box...



[vba]Private Sub cmdbrowsebook_Click()
Dim list1 As ListBox
Dim FName As String
Dim Msg As String
Dim SheetList As Collection
Dim i As Long
Dim n As Long
Dim ws As workSheet
Dim Wkb As Workbook

application.EnableEvents = False
application.ScreenUpdating = False
application.DisplayAlerts = False
Set SheetList = New Collection
FName = application.GetOpenFileName("Excel Files (*.xls),*.xls")
If FName <> "False" Then
Set Wkb = Workbooks.Open(fileName:=FName)
End If
xtFileLocation = FName
For Each ws In Wkb.Worksheets
' list1.AddItem (ws.name)
SheetList.Add ws.name
Next
Wkb.Close SaveChanges:=False

n = SheetList.Count
For i = 1 To n
list1.AddItem (SheetList(i))
'Msg = Msg & vbNewLine & SheetList(i)
Next i
'Msg = vbNewLine & vbNewLine & Msg

' MsgBox Msg, vbInformation, "Workbook Information"
'txtFileLocation = FName

application.EnableEvents = True
application.ScreenUpdating = True
application.DisplayAlerts = True

Set SheetList = Nothing
Set ws = Nothing
Set Wkb = Nothing


End Sub[/vba]

[Killian]

If you explicitly refer to the listbox, it seems to work fine[VBA]Me.List1.AddItem SheetList(i)[/VBA]Is there any particular reason you create a collection?[VBA]Private Sub CommandButton1_Click()

Dim FName As String
Dim ws As Worksheet
Dim Wkb As Workbook

Application.EnableEvents = False
Application.ScreenUpdating = False
Application.DisplayAlerts = False
FName = Application.GetOpenFilename("Excel Files (*.xls),*.xls")
If FName <> "False" Then
Set Wkb = Workbooks.Open(Filename:=FName)
End If
For Each ws In Wkb.Worksheets
Me.List1.AddItem (ws.Name)
Next
Wkb.Close SaveChanges:=False

Application.EnableEvents = True
Application.ScreenUpdating = True
Application.DisplayAlerts = True

Set Wkb = Nothing
End Sub[/VBA]

[v_gyku]

I am using activex component
me.
dosn't gives any functions.
give me solution.

[Killian]

I see, when you mentioned a form, I thought you meant a UserForm, but you're working with an Activex component. (that would have been woth mentioning at the start, btw)
So some more questions:
Where does the code break on the error?
Have you tried to select any items in the listbox after you run the code? Because ScreenUpdating has been switched off, it may not be refreshing

[MOS MASTER]

You're question is in the pay for answer forum.

I'll move your question to the Integration/Automation of Office Applications Help section of the forum so someone can help you there.