Extract All worksheet names in workbook
 

Hi...

How do you display all worksheet names in a workbook in a listview???

Thanks

wendy

Extract All worksheet names in workbook
 

I'd do it like this:

Sub aa()
On Error Resume Next
Err = 0
CommandBars("Workbook Tabs").Controls("More sheets...").Execute
If Err < 0 Then CommandBars("Workbook Tabs").ShowPopup
End Sub


--
Jim Rech
Excel MVP

Extract All worksheet names in workbook
 

Hello,

I had to do something similar: Writting all names in a text files with
some formating

The principle is to go thru the Names collection, and to filter special
cases like
- Invisible (system) names
- Formulas
..

Here is the main loop:

dim Nom as Name

For Each Nom In ActiveWbk.Names


'Filtre les parametres systemes (invisibles)
If Nom.Visible Then

'Detection des erreurs
'Cas ou le nom pointe dans le vide
If InStr(Nom.value, "#REF!") Then
reponse = MsgBox(" ERROR The name:[" & Nom.Name & "] has
lost its reference, Delete ?", vbYesNo)
If reponse = vbYes Then
Nom.Delete
End If
Else
Set PlageNom = Nothing
Set PlageNom = Nom.RefersToRange


'Filtre les formules, ainsi que les Noms sur plusieurs
cellules,
If Not PlageNom Is Nothing And Not IsArray(PlageNom)
Then

' Filtre le cas ou une cellule est en erreur de par sa
formule
If PlageNom.Text = "#VALEUR!" Then
MsgBox (" ERROR :" & Nom.Name & "/" & Nom.RefersTo
& ": Reference missing in formula")
Else

NbNoms = NbNoms + 1

Var_pro = Nom.Name & " = " & var_num

End If

Var_pro = StrConv(Var_pro, vbUpperCase)
liste_var = liste_var & Chr(car_sep) & Var_pro


' Convertit tout en majuscules
fichier.WriteLine (Var_pro)
End If
Else
FormulesNommees = True
End If

End If

End If

Next Nom


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements