How to enter sheetnames into an Excel Listbox

[Guest_imported]

I have only recently started programming in Excel, however, recently I got stuck in something that might be really simple, I just can't figure it out, can somebody help me?

The situation is:
I have a UserForm8 that contains ListBox1,
I want to fill the Listbox with all the names of the sheets in the ActiveWorkbook.

What is the code for this?

Thanks.

 

[akn846]

what you need to do is make a call to the .additem method for the name of the listbox control you've got on the worksheet.

something like ActiveWorkbook.Worksheets("Userform8&quot.listbox1.additem (" ....what ever you need to add...&quot

Hope this helps

 

[Guest_imported]

Thanks for the advice,

However, it doesn't work (at least not what I'm entering as code.)

I made a Procedure in a module called ShowUserForm8 and tried (at least I think I did) to enter what you advised.

The code looks like this:

Private Sub ShowUserForm8()
ActiveWorkbook.Sheets
UserForm8.ListBox1.AddItem Sht.Names
UserForm8.Show

End Sub

But it doesn't work. I've tried all kinds of things, I just can't figure it out, HEEEELLLLPPPPP!!!!!!

I will be very gratefull with any tip anyone can give me.

 

[Guest_imported]

BTW,

I might not have been clear with my actual intentions.
I'm trying the procedure to check how many sheets are available in the workbook, and then automatically put their names into the Listbox.

He has to do this automatically, because the intended program whil work with a changing workbook.

Cheers.

 

[acron]

Try the following code in your UserForm, assuming the Listbox is named ListBox1.

Private Sub UserForm_Initialize()
Dim ws As Worksheet
    For Each ws In ThisWorkbook.Sheets
        Me.ListBox1.AddItem ws.Name
    Next
End Sub

A.C.

 

[Guest_imported]

thanks acron, works like a charm.

Really appreciate it

 

[conby]

I am new to the VBA world and have managed to create the list box. Can someone help me go a little further and click on a sheet name within the list and go to it?

thanks