Forms collection in vba

Giganews Newsgroups
Subject: Forms collection in vba
Posted by:  MP (nospam@Thanks.com)
Date: Sat, 1 Jan 2011

I don't think there's a built in Forms collection in vba like there is in vb

what i'm trying to do...
I have a workbook with several sheets with 'longish' names
such that i can't see all the sheet tabs at one time.
it makes it a pain to navigate through sheets, having to use the arrow
buttons to bring the desired sheet into view
so I created a form to list sheets and double click the sheet name activates
that sheet.
a module sub creates and shows the form...
i'm trying to make it so if the form is already existing the module doesn't
have to create a new one(singleton pattern)

in vb i could iterate the forms collection to see if it already exists,
how could i do that in vba?
....in the module
Sub ListSheetNames()
'list names of all sheets in active workbook

Dim f As frmSheetList
Set f = New frmSheetList
f.ListBox1.Clear
Dim ws As Worksheet
For Each ws In Worksheets
    f.ListBox1.AddItem ws.Name
Next
f.Show vbModeless

End Sub

Replies