list sheet tab names in listbox

[pswanie]

how do i list all the sheet tab names in the current workbook in listbox1?
the listbox1 is on userform1

thanx

 

[JLGWhiz]

Put in the UserForm Initialize event code.

For Each sh In ActiveWorkbook.Sheets
If Not sh.Name Is Nothing Then
UserForm1.ListBox1.AddItem sh.Name
End If
Next

Untested.

 

[pswanie]

i get error msg "object required" what should go after the next?


thank you

 

[FSt1]

hi
something like this might work
Private Sub UserForm_Initialize()
Dim WS As Worksheet
For Each WS In Worksheets
Listbox1.AddItem WS.Name
Next
End Sub

regards
FSt1

 

[pswanie]

that worked...

now can i get a this to run when a user click on commandbutton1

sub comandbutton1_click
if nothing in listbox1 selected then

msgbox "please select month"

else
call savetosheet
end sub

 

[pswanie]

i added this following



With Me.ListBox1
.MultiSelect = fmMultiSelectMulti
.ListStyle = fmListStyleOption


End With

 

[FSt1]

hi
i assumed that it would be part of the forms initialization but it would be
easy enought just to swith
Sub UserForm_Initialize()
to
sub commandbutton1_click()
or just add it to code you may already have in commandbutton1.

Regards
FSt1