Forms collection in vba

[mp]

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

 

[Jim Cone]

There is a UserForms collection with a Count property...
If UserForms.Count > 0 Then ' you've got one or more.
'---
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)




"mp" <[email protected]>
wrote in message
news:[email protected]...

 

[mp]

duh!? i coulda sworn i looked for that too...will check again
thanks
mark

 

[Hans Terkelsen]

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

....

It might be enough for your purpose to right-click on the sheet navigation buttons.
A menu of the sheet names is shown.
Hans T

 

[Rick Rothstein]

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

Perhaps this UserForm solution I posted several years ago to an old
newsgroup message might work for you. Here is it, presented as I originally
posted it (basically, it shows a ListBox of all sheets which you can click
directly in (and get taken to the clicked on tab name) or start to type the
beginning text of a tab name into a text box which will parse down the list
of tab names so you can either click or arrow down to make a selection). My
previous post follows....

Okay, here is something I think will meet your needs (don't be put off by
the its length, it is really worth the effort)... its a UserForm which lets
you type partial entries, pick from a list and takes you to the indicated
sheet. I'm going to assume you do not know how to implement a UserForm and
talk you through the whole process.

Go to the Visual Basic editor (press Alt+F11 from any worksheet). Once
there, click Insert/UserForm form the menu bar. You will see a blank form
and a window with the caption Toolbox. The Toolbox has controls (TextBox,
ListBox, etc.) on it that you will be placing on the blank UserForm (note..
the Toolbox is displayed only when the UserForm has focus). Before we put
any controls on the UserForm, let's rename it. On the left side of the VB
editor should be a window with the caption "Properties - UserForm1" (if you
don't see this, press F4). The first field of the Properties window is
labeled (Name) and next to it is the word UserForm1... click the word
UserForm1 and type GoToSheetSelector in its place. Next, look down and find
the Height and Width properties and set them to these values...

Height = 230
Width = 165

These, and other properties for the controls we will be putting on the
UserForm, are initial settings to get you started... you will be able to
modify them later to suit your own tastes. The rest of the default values
for the UserForm should be fine for now.

Okay, let's set the UserForm up. There will only be two controls on the
UserForm... a TextBox and a ListBox.

First, the TextBox. Give the UserForm focus (to make the Toolbox visible)
and click on the TextBox icon in the Toolbox (it is one with the lower case
letters "ab followed by a vertical bar) to place it. You can use the resize
handles to make the TextBox whatever size you want and you can click/drag it
to any position you want, but we will use the Properties window to set them
to exact values. There are only seven properties I want you to set
initially. In the Properties window, find and set the following values...

Height = 18
Left = 10
MultiLine = True
Top = 10
Width = 140
WordWrap = False

The seventh property is actually a dialog box for an object where you will
set 3 properties of the object. Click on the field to the right of the Font
property and you will see a little button with 3 dots in it... click that
button. On the dialog box that appears, set these properties....

Font = Arial
Font Style = Bold
Size = 10

Okay, that takes care of the TextBox. Next, let's add the ListBox. Click the
ListBox icon in the Toolbox (it is a rectangle with 3 horizontal lines on
the left and what are supposed to be up/down arrows on the right... if you
hover the mouse over the controls, a tooltip will appear with the control's
name... you are looking for ListBox) and then click/drag on the UserForm to
place it. Set the following properties for it in the Properties window...

Height = 165
Left = 10
Top = 38
Width = 140

Okay, that should take care of setting up the UserForm; now let's install
the code. Press F7 to bring up the code window for the UserForm (or simply
double click anywhere on the UserForm or one of its controls). Delete
anything you see in the code window that appears and copy/paste **all** of
the marked off code that follows my signature into this code window.

Okay, that takes care of the UserForm itself, now we just need a way to call
it up. Still in the VB editor, click Insert/Module from the menu bar.
Another code window will appear; copy/paste this into it...

Sub CallGoToSheetSelector()
GoToSheetSelector.Show
End Sub

Okay, that is pretty much it... the UserForm is usable right now. From any
sheet in your workbook, press Alt+F8, select CallGoToSheetSelector from the
list and click Run (we can do this differently and I'll explain how in a
moment). The UserForm will be displayed showing all sheets in your workbook
in the ListBox and the cursor will be in the TextBox. Here is how the
UserForm works. Start to type in the name of a sheet in your workbook and
the ListBox will show only sheet names starting with that text. You can
continue to type until only one name is left in the ListBox (you might not
have to type the full sheet name to completion for this to happen) and then
press the Return key to go to that sheet. But you don't have to keep typing
until there is only one sheet name left in the ListBox... at any time, you
can press either the down or right arrow and you will find yourself in the
ListBox itself where you can continue to use the arrow keys to place the
highlight on the sheet name you want, then press Return to go to that sheet.
If you find yourself in the TextBox by mistake, just press the left arrow
key to put yourself back into the TextBox. You can edit the text in the
TextBox and the ListBox will display the sheet names corresponding to the
type in text. If you type text that is not the starting text of a sheet
name, the ListBox will not display anything (if you mistype a letter so this
occurs, just delete the mistyped letter and the ListBox will adjust
accordingly). Oh, and you can also just click an entry in the ListBox with
your mouse and that will take you to the clicked on sheet name.

Okay, now about alternate methods of activating the UserForm (besides always
using Alt+F8/Select/Run). Go to any worksheet and press Alt+F8, select
CallGoToSheetSelector from the list and then click the Options button. This
will bring up a dialog box which lets you assign a shortcut key to your
macro. Type a lower case "g" in the indicated field, then click OK and close
the dialog box. Now, from any sheet in your workbook, press Ctrl+g and your
UserForm will appear, ready to use.

I think that is everything. If you have any questions, feel free to post
back.

Rick Rothstein (MVP - Excel)

 

[mp]

"Hans Terkelsen" <dk> wrote in message

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

....

It might be enough for your purpose to right-click on the sheet navigation
buttons.
A menu of the sheet names is shown.
Hans T

Ha!!!
I never knew that...it seemed there should be something like that built in
but I never knew!!
Thanks
mark

 

[mp]

Perhaps this UserForm solution I posted several years ago to an old
newsgroup message might work for you.

Thanks Rick
I don't see the form code
mine doesn't have the type in autocomplete feature yours mentions but it
works good enough.
I'm just having trouble figureing out how to ignore my form creation code if
the form already exists
(if i click the menu button that calls up the form after the form is already
up)

if i iterate the UserForms collection the form is in the collection but the
caption is blank
(even though it's not blank on the screen)
so I can't identify it
....actually in my case I know there's only one form and it's mine so that's
kind of pedantic to check the caption
still it seems odd the caption property is blank even though the userform
showing does have the correct caption

Sub ListSheetNames()
'list names of all sheets in active workbook
Dim f1 As UserForm
Dim f As frmSheetList
Dim ws As Worksheet
If UserForms.Count > 0 Then
Debug.Print "Form count " & UserForms.Count <--- this shows 1
For Each f1 In UserForms
If f1.Caption = "Sheet List" Then <---this never is true as caption is
blank
Debug.Print "Got form"
Set f = f1
'Exit For
Else
Debug.Print "caption not match <" & f1.Caption & ">"
End If

Next f1
Else
Set f = New frmSheetList
f.Caption = "Sheet List"

End If

f.Show vbModeless
End If


End Sub

 

[Rick Rothstein]

Sorry, I forgot to post the code...

' *************** START OF CODE ***************
Option Explicit

Dim SheetNames() As String

Private Sub UserForm_Initialize()
Dim Obj As Object
TextBox1.Text = ""
TextBox1.EnterKeyBehavior = True
ReDim SheetNames(0 To Sheets.Count - 1)
For Each Obj In Sheets
SheetNames(Obj.Index - 1) = Obj.Name
ListBox1.AddItem Obj.Name
Next
TextBox1.SetFocus
End Sub

Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
With TextBox1
If KeyCode = vbKeyLeft Then
ListBox1.ListIndex = -1
.SelStart = Len(.Text)
.SetFocus
ElseIf KeyCode = vbKeyReturn Then
If ListBox1.ListCount > 0 Then
Sheets(ListBox1.Text).Activate
Unload Me
End If
End If
End With
End Sub

Private Sub ListBox1_MouseUp(ByVal Button As Integer, ByVal Shift As
Integer, ByVal X As Single, ByVal Y As Single)
Sheets(ListBox1.List(ListBox1.ListIndex)).Activate
Unload Me
End Sub

Private Sub TextBox1_Change()
Dim X As Long
Dim Pages() As String
Pages = Filter(SheetNames, TextBox1.Text, True, vbTextCompare)
If Len(TextBox1.Text) Then
If UBound(Pages) > -1 Then
With ListBox1
.Clear
For X = 0 To UBound(Pages)
.AddItem Mid$(Pages(X), 1)
Next
End With
Else
ListBox1.Clear
End If
Else
ListBox1.Clear
For X = 0 To UBound(SheetNames)
ListBox1.AddItem Mid$(SheetNames(X), 2)
Next
End If
End Sub

Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
With ListBox1
If KeyCode = vbKeyReturn Then
KeyCode = 0
If .ListCount = 0 Then
Exit Sub
ElseIf .ListCount = 1 Then
Sheets(.List(0)).Activate
Unload Me
Else
.SetFocus
.Selected(0) = True
.ListIndex = 0
End If
ElseIf (KeyCode = vbKeyDown Or (KeyCode = vbKeyRight And
TextBox1.SelStart = Len(TextBox1.Text))) And .ListCount > 0 Then
.SetFocus
.Selected(0) = True
.ListIndex = 0
End If
End With
End Sub
' *************** END OF CODE ***************






"mp" wrote in message

Perhaps this UserForm solution I posted several years ago to an old
newsgroup message might work for you.

Thanks Rick
I don't see the form code
mine doesn't have the type in autocomplete feature yours mentions but it
works good enough.
I'm just having trouble figureing out how to ignore my form creation code if
the form already exists
(if i click the menu button that calls up the form after the form is already
up)

if i iterate the UserForms collection the form is in the collection but the
caption is blank
(even though it's not blank on the screen)
so I can't identify it
....actually in my case I know there's only one form and it's mine so that's
kind of pedantic to check the caption
still it seems odd the caption property is blank even though the userform
showing does have the correct caption

Sub ListSheetNames()
'list names of all sheets in active workbook
Dim f1 As UserForm
Dim f As frmSheetList
Dim ws As Worksheet
If UserForms.Count > 0 Then
Debug.Print "Form count " & UserForms.Count <--- this shows 1
For Each f1 In UserForms
If f1.Caption = "Sheet List" Then <---this never is true as caption is
blank
Debug.Print "Got form"
Set f = f1
'Exit For
Else
Debug.Print "caption not match <" & f1.Caption & ">"
End If

Next f1
Else
Set f = New frmSheetList
f.Caption = "Sheet List"

End If

f.Show vbModeless
End If


End Sub

 

[Jim Cone]

This simple code provides access to all workbook sheets...
I believe it was Jim Rech who first published it.
'--
Sub ShowMeTheNames()
On Error Resume Next
Application.CommandBars("Workbook Tabs").Controls("More Sheets...").Execute
If Err.Number > 0 Then Application.CommandBars("Workbook Tabs").ShowPopup
End Sub
'--
Also, a separate Table of Contents sheet (with return links on each workbook sheet) is convenient
for some users.
Take a look at the free trial of my "Extras for Excel" add-in for an implementation...
http://tinyurl.com/ExtrasXL

--
Jim Cone
Portland, Oregon USA
http://www.mediafire.com/PrimitiveSoftware
(free and commercial excel programs)





"mp" <[email protected]>
wrote in message

 

[Rick Rothstein]

This simple code provides access to all workbook sheets...

I believe it was Jim Rech who first published it.
'--
Sub ShowMeTheNames()
On Error Resume Next
Application.CommandBars("Workbook Tabs").Controls("More
Sheets...").Execute
If Err.Number > 0 Then Application.CommandBars("Workbook
Tabs").ShowPopup
End Sub

That code produces the same popup window that you get when you right-click
the navigation icons located to the left of the first worksheet tab... seems
like right-clicking is just a easy as clicking a button (assuming the your
posted macro was assigned to one).

Rick Rothstein (MVP - Excel)

 

[SanCarlosCyclist]

That code produces the same popup window that you get when you right-click
the navigation icons located to the left of the first worksheet tab... seems
like right-clicking is just a easy as clicking a button (assuming the your
posted macro was assigned to one).

Rick Rothstein (MVP - Excel)

This looks like a neat thing. Can someone post the entire code that I
need to copy to a new module? I would love to be able to click a menu
to take me to spreadsheets.

 

[Gord Dibben]

That is the entire code that you will copy to a module.

As Rick points out a simple right-click on any of the sheet navigation arrows
will show you a list of 15 sheets and "more sheets".

That is what Jim's code does.

I like this code from Bob Phillips which pops up a selectable list of all sheets
to choose from using an option button to click.
..

Sub Browse_Sheets()
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption

Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add
With thisDlg
.Name = sID
.Visible = xlSheetHidden
'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters > cMaxLetters Then
cMaxLetters = cLetters
End If
iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13
Next i
.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24
CurrentSheet.Activate
With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If
Application.DisplayAlerts = False
.Delete
End With
End Sub


Gord Dibben MS Excel MVP

 

[Rick Rothstein]

This looks like a neat thing. Can someone post the entire

code that I need to copy to a new module? I would love to
be able to click a menu to take me to spreadsheets.

You might be interested in the two posts I made to "mp" elsewhere in this
thread then... the first message shows how to set everything up and the
second message has the code I forgot to include in my first message.

Rick Rothstein (MVP - Excel)

 

[Jim Cone]

Re: "will show you a list of 15 sheets and "more sheets.
That is what Jim's code does."

Well almost:
Actually it only requires one click and it only shows one popup...
either a single list or a list with scrollbars (depending on the number of sheets).
--
Jim Cone
Portland, Oregon USA
http://excelusergroup.org/

 

[Gord Dibben]

Thanks for the correction Jim.

I had not tested properly.

Only 10 sheets in my workbook so did not get to the listbox.


Gord