Putting Items (sheet names) in a listbox

[Jim Adams]

Friends, how can I complete this and make it Excel97 friendly? I'm not
sure what I'm doing is correctly, but I'm trying. Iwant the userform
to appear when the workbook is opened/activated. I hope the comments
below will give an indication of what I'm trying to do.

Sub Workbook_Open
ListAllSheets()
End Sub

Sub ListAllSheets()
'Display a list of sheets in active worksheet
'So User then can choose a sheetname from the list then goto that
sheet

'Initialize The List Box
ListBox1.ColumnCount=1
ListBox1.MatchRequired:=True
ListBox1.SetFocus

'Put Sheet Names In Listbox1for selection
SelectedItem=1
IndexNum=0
For Each Item in ActiveWorkbook.Sheets
IndexNum=IndexNum+1
NextItem

I'm stuck here, I know it is a simple statement, but I can't find info
in the VB help to answer my question

'Display UserForm
frmGoTo.show

[George Nicholson]

Jim:

I use the following code to initialize a user form (ufSheetList) containing
a 2 column listbox (lboSheetList) which lists the sheet name and sheet type
of all sheets in the active workbook.

I call my form from a menu, but it could just as easily be called from
Auto_Open, etc.

FYI: My listbox is multi-select since I use it to determine which sheets get
printed or have their headers/footers changed as a batch, etc. Therefore
the userform also includes buttons for Done, Select All, Deselect All and
Cancel (all of which require additional code). The form also includes a
caption stating "x # of sheets selected" which automatically updates upon a
selection change.

I hope this helps,
George

Private Sub UserForm_Initialize()
' Load list box with listing of sheets in current workbook
' Note: This could be adjusted to only list VISIBLE sheets.
Dim var_aSheetnames() As String
Dim x As Integer
Dim varSheet As Variant
Dim strSheetType As String

' Get count of sheets in current workbook
x = ActiveWorkbook.Sheets.Count
' Resize array to contain correct amount of sheet info
ReDim var_aSheetnames(1 To x, 1)
' Loop to fill array
For Each varSheet In ActiveWorkbook.Sheets
' Fill array dimension1 with sheetname
var_aSheetnames(varSheet.Index, 0) =
ActiveWorkbook.Sheets(varSheet.Index).Name
' Determine sheettype
If TypeName(varSheet) = "Worksheet" Then
Select Case varSheet.Type
Case Is = xlWorksheet
strSheetType = "Worksheet"
Case Is = xlExcel4MacroSheet
strSheetType = "Excel 4.0 Macro Sheet"
Case Is = xlExcel4IntlMacroSheet
strSheetType = "Excel 4.0 International Macro Sheet"
Case Else
strSheetType = "Unknown Worksheet"
End Select
' Fill array dimension2 with sheettype
Else: strSheetType = TypeName(varSheet)
End If
var_aSheetnames(varSheet.Index, 1) = strSheetType
Next
' Fill 2-column listbox with array contents
ufSheetList.lboSheetList.List() = var_aSheetnames
' Show form
ufSheetList.Show
End Sub

Jim Adams wrote in message <36b50f49....@news.ntr.net>...

[Denny Campbell]

Jim,

I think the following code will set up the listbox like want.

Sub ListAllSheets()

'Initialize The List Box
With frmGoTo.ListBox1
For Each Item In Sheets
.AddItem Item.Name
Next
.Selected(0) = True
.SetFocus
End With

'Display UserForm
frmGoTo.Show
End Sub

HTH
Denny Campbell
Grand Rapids, MI USA