Finding a sheet

  • Thread starter Thread starter Linda
  • Start date Start date
L

Linda

Since you were so quick and helpful about my index question......... If I
happen to know the name of the sheet is there a way to type the first few
letters of the name and then find the sheet that way?

Thanks
Linda
 
Find sheet in a file?

Put your cursor down in the tab area -- all way to left and right click

Select sheet

: Since you were so quick and helpful about my index question......... If I
: happen to know the name of the sheet is there a way to type the first few
: letters of the name and then find the sheet that way?
:
: Thanks
: Linda
:
:
 
Thanks...I was wondering if there were a way to find it by knowing the name.
We have about 50 sheets so there are 2 lists to sort through. I guess I'm
getting really lazy now, huh?

Linda
 
Linda
You can roll your own macro to do that. There are a number of ways to
do what you want by writing a macro to do it. One way would be for you to
have a cell into which you would type in, say, the first 4 characters and
hit Enter and your sheet pops up. The first question that comes to mind is
how are you going to find the sheet that has that cell? Well, you would
have to attach the macro to every sheet and use the same cell in every sheet
for this task. Post back if you want to explore this further. HTH Otto
 
If you can explain it step by step and I can get one step at a time done, I
am game. I'm not in a big rush either so if you are busy at certain times I
don't mind waiting for the next step. I have no programming experience. If
you change your mind, that's ok too. It looks like E1 is blank on all
sheets.

Thanks,
Linda
 
How about a floating toolbar that displays all the worksheet names?

The default behavior when you type into the combobox on that toolbar is to match
the entries.

Option Explicit
Sub auto_close()
On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0
End Sub

Sub auto_open()

Dim cb As CommandBar
Dim ctrl As CommandBarControl
Dim wks As Worksheet

On Error Resume Next
Application.CommandBars("MyNavigator").Delete
On Error GoTo 0

Set cb = Application.CommandBars.Add(Name:="myNavigator", temporary:=True)
With cb
.Visible = True
Set ctrl = .Controls.Add(Type:=msoControlButton, temporary:=True)
With ctrl
.Style = msoButtonCaption
.Caption = "Refresh Worksheet List"
.OnAction = ThisWorkbook.Name & "!refreshthesheets"
End With

Set ctrl = .Controls.Add(Type:=msoControlComboBox, temporary:=True)
With ctrl
.AddItem "Click Refresh First"
.OnAction = ThisWorkbook.Name & "!changethesheet"
.Tag = "__wksnames__"
End With
End With

End Sub
Sub ChangeTheSheet()

Dim myWksName As String
Dim wks As Worksheet

With Application.CommandBars.ActionControl
If .ListIndex = 0 Then
MsgBox "Please select an existing sheet"
Exit Sub
Else
myWksName = .List(.ListIndex)
End If
End With

Set wks = Nothing
On Error Resume Next
Set wks = Worksheets(myWksName)
On Error GoTo 0

If wks Is Nothing Then
Call refreshthesheets
MsgBox "Please try again"
Else
wks.Select
End If

End Sub
Sub refreshthesheets()
Dim ctrl As CommandBarControl
Dim wks As Worksheet

Set ctrl = Application.CommandBars("myNavigator") _
.FindControl(Tag:="__wksnames__")
ctrl.Clear

For Each wks In ActiveWorkbook.Worksheets
ctrl.AddItem wks.Name
Next wks
End Sub

(If you swap workbooks, just click the other button to get a fresh list of
worksheet names in the dropdown.)
 
Yikes, Dave. That looks like something for the advanced user. I'm going to
paste this in a cell and see what happens.

Linda
 
It's a macro that you (as the developer) can create. Once you create it, it's
there whenever you open the workbook (well, and enable macros).

Try it on a test workbook with some nicely named worksheets.

When you're in excel,
hit alt-F11 (to get to the VBE)
then hit ctrl-R to see the project explorer (like windows explorer)
rightclick on your workbook/project (VBAProject (book1.xls))
select Insert, then Module

then paste that whole conglomeration in.

then back to excel and hit alt-F8 and doubleclick on Auto_open.
(auto_open will run when ever you open the workbook--so this time it's just for
testing)

Try it out.

It might be overkill, but once you set it up, you're done.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
That was fun. I opened up a new excel sheet. It just pasted the text like
it is......Nothing happened. I found the tools/macro and made a new macro.
There was nothing in the dialog box so I typed Dave in the Name box. I
selected "create", then the VB window (or Macro Window) opened up. I pasted
your code in there. Now when I open up the macro window there are 5 items
listed. auto_close, auto_open, ChangeTheSheet, Dave, and refreshthesheets.

It says Macros in "This Workbook"

I don't see a floating tool bar though.

Linda
 
Hit alt-F8 (to see that dialog again), then double click on auto_open.

(Or close your workbook (save it, too). then reopen. xl will run the auto_open
macro whenever you open the workbook. and the auto_open code actually builds
the toolbar.)
 
Hey, that's very cool!

Thanks,

Linda
Dave Peterson said:
Hit alt-F8 (to see that dialog again), then double click on auto_open.

(Or close your workbook (save it, too). then reopen. xl will run the auto_open
macro whenever you open the workbook. and the auto_open code actually builds
the toolbar.)
Click to expand...
 
Back
Top