Search for tabs from a cell value
 

Dear Sir/Mdm,

Can anyone please advice how to search for a specific worksheet tab based on
a cell value (user input) on a worksheet.

Eg, I have 50 worksheets in 1 workbook.
Sheet1=Main Menu
Sheet2=Customer ID 1
Sheet3=Customer ID 2
Sheet4=Customer ID 3 and etc.

If I input a value say "Customer ID 45" in a cell in Main Menu (Sheet 1),
how can Excel "intelligently" brings me to Sheet 46 which is also Customer ID
45?

Thank you

Alvyn

Search for tabs from a cell value
 

Are you doing this strictly to be taken to the desired worksheet? If so,
would right clicking on the tab navigation toolbar (the 4 VCR looking
buttons to the left of the tabs) and selecting the worksheet from the list
box that appears be an acceptable solution?

Rick


"Alvyn" wrote in message
...
Dear Sir/Mdm,

Can anyone please advice how to search for a specific worksheet tab based
on
a cell value (user input) on a worksheet.

Eg, I have 50 worksheets in 1 workbook.
Sheet1=Main Menu
Sheet2=Customer ID 1
Sheet3=Customer ID 2
Sheet4=Customer ID 3 and etc.

If I input a value say "Customer ID 45" in a cell in Main Menu (Sheet 1),
how can Excel "intelligently" brings me to Sheet 46 which is also Customer
ID
45?

Thank you

Alvyn

Search for tabs from a cell value
 

Dear Rick Rothstein

Thanks for your reply.
That is one way of doing it. I apologised for not making things clear
initially.

Actually, I am trying to create something with an "auto complete" function
on a single cells to search for the tabs name.

Eg,
Sheet1=Search menu
Sheet2=John
Sheet3=Donald
Sheet4=Don
Sheet5=Bill & etc

Once I type something like "do" on a cell on the Search Menu(Sheet1), it
will lookup and filter, leaving Donald & Don in the selection list.
Thereafter, when I choose "don" on that cell, it will bring me to Sheet4(Don).

Thanks

Alvyn


"Rick Rothstein (MVP - VB)" wrote:

Are you doing this strictly to be taken to the desired worksheet? If so,
would right clicking on the tab navigation toolbar (the 4 VCR looking
buttons to the left of the tabs) and selecting the worksheet from the list
box that appears be an acceptable solution?

Rick


"Alvyn" wrote in message
...
Dear Sir/Mdm,

Can anyone please advice how to search for a specific worksheet tab based
on
a cell value (user input) on a worksheet.

Eg, I have 50 worksheets in 1 workbook.
Sheet1=Main Menu
Sheet2=Customer ID 1
Sheet3=Customer ID 2
Sheet4=Customer ID 3 and etc.

If I input a value say "Customer ID 45" in a cell in Main Menu (Sheet 1),
how can Excel "intelligently" brings me to Sheet 46 which is also Customer
ID
45?

Thank you

Alvyn

Search for tabs from a cell value
 

I have an idea for this, but I'm going to sleep for now... check back
tomorrow (well, later today here) and I should have something usable for
you.

Rick


"Alvyn" wrote in message
...
Dear Rick Rothstein

Thanks for your reply.
That is one way of doing it. I apologised for not making things clear
initially.

Actually, I am trying to create something with an "auto complete" function
on a single cells to search for the tabs name.

Eg,
Sheet1=Search menu
Sheet2=John
Sheet3=Donald
Sheet4=Don
Sheet5=Bill & etc

Once I type something like "do" on a cell on the Search Menu(Sheet1), it
will lookup and filter, leaving Donald & Don in the selection list.
Thereafter, when I choose "don" on that cell, it will bring me to
Sheet4(Don).

Thanks

Alvyn


"Rick Rothstein (MVP - VB)" wrote:

Are you doing this strictly to be taken to the desired worksheet? If so,
would right clicking on the tab navigation toolbar (the 4 VCR looking
buttons to the left of the tabs) and selecting the worksheet from the
list
box that appears be an acceptable solution?

Rick


"Alvyn" wrote in message
...
Dear Sir/Mdm,

Can anyone please advice how to search for a specific worksheet tab
based
on
a cell value (user input) on a worksheet.

Eg, I have 50 worksheets in 1 workbook.
Sheet1=Main Menu
Sheet2=Customer ID 1
Sheet3=Customer ID 2
Sheet4=Customer ID 3 and etc.

If I input a value say "Customer ID 45" in a cell in Main Menu (Sheet
1),
how can Excel "intelligently" brings me to Sheet 46 which is also
Customer
ID
45?

Thank you

Alvyn

Search for tabs from a cell value
 

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 the lower case letters
"ab" followed by a vertical line) and then click/drag on the UserForm 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

' *************** 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 ***************


"Alvyn" wrote in message
...
Dear Rick Rothstein

Thanks for your reply.
That is one way of doing it. I apologised for not making things clear
initially.

Actually, I am trying to create something with an "auto complete" function
on a single cells to search for the tabs name.

Eg,
Sheet1=Search menu
Sheet2=John
Sheet3=Donald
Sheet4=Don
Sheet5=Bill & etc

Once I type something like "do" on a cell on the Search Menu(Sheet1), it
will lookup and filter, leaving Donald & Don in the selection list.
Thereafter, when I choose "don" on that cell, it will bring me to
Sheet4(Don).

Thanks

Alvyn


"Rick Rothstein (MVP - VB)" wrote:

Are you doing this strictly to be taken to the desired worksheet? If so,
would right clicking on the tab navigation toolbar (the 4 VCR looking
buttons to the left of the tabs) and selecting the worksheet from the
list
box that appears be an acceptable solution?

Rick


"Alvyn" wrote in message
...
Dear Sir/Mdm,

Can anyone please advice how to search for a specific worksheet tab
based
on
a cell value (user input) on a worksheet.

Eg, I have 50 worksheets in 1 workbook.
Sheet1=Main Menu
Sheet2=Customer ID 1
Sheet3=Customer ID 2
Sheet4=Customer ID 3 and etc.

If I input a value say "Customer ID 45" in a cell in Main Menu (Sheet
1),
how can Excel "intelligently" brings me to Sheet 46 which is also
Customer
ID
45?

Thank you

Alvyn

Search for tabs from a cell value
 

Dear Rick Rothstein

Thank you very much for the precise instruction. I will try it though I am
unfamiliar with VB.

Alvyn


"Rick Rothstein (MVP - VB)" wrote:

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 the lower case letters
"ab" followed by a vertical line) and then click/drag on the UserForm 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

' *************** 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 ***************


"Alvyn" wrote in message
...
Dear Rick Rothstein

Thanks for your reply.
That is one way of doing it. I apologised for not making things clear
initially.

Actually, I am trying to create something with an "auto complete" function
on a single cells to search for the tabs name.

Eg,
Sheet1=Search menu
Sheet2=John
Sheet3=Donald
Sheet4=Don
Sheet5=Bill & etc

Once I type something like "do" on a cell on the Search Menu(Sheet1), it
will lookup and filter, leaving Donald & Don in the selection list.
Thereafter, when I choose "don" on that cell, it will bring me to
Sheet4(Don).

Thanks

Alvyn


"Rick Rothstein (MVP - VB)" wrote:

Are you doing this strictly to be taken to the desired worksheet? If so,
would right clicking on the tab navigation toolbar (the 4 VCR looking
buttons to the left of the tabs) and selecting the worksheet from the
list
box that appears be an acceptable solution?

Rick


"Alvyn" wrote in message
...
Dear Sir/Mdm,

Can anyone please advice how to search for a specific worksheet tab
based
on
a cell value (user input) on a worksheet.

Eg, I have 50 worksheets in 1 workbook.
Sheet1=Main Menu
Sheet2=Customer ID 1
Sheet3=Customer ID 2
Sheet4=Customer ID 3 and etc.

If I input a value say "Customer ID 45" in a cell in Main Menu (Sheet
1),
how can Excel "intelligently" brings me to Sheet 46 which is also
Customer
ID
45?

Thank you

Alvyn

Search for tabs from a cell value
 

I don't think I missed anything, so you shouldn't have any trouble
implementing it. Don't worry about your familiarity with VB... the code is
completely written and proofed... all you have to do is construct the
housing for it. However, if you should run into a problem, just write back
(try and provide as much detail as you can) and someone here (probably
me<g) will try and talk you through it.

Rick


"Alvyn" wrote in message
...
Dear Rick Rothstein

Thank you very much for the precise instruction. I will try it though I am
unfamiliar with VB.

Alvyn


"Rick Rothstein (MVP - VB)" wrote:

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 the lower case
letters
"ab" followed by a vertical line) and then click/drag on the UserForm 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

' *************** 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 ***************


"Alvyn" wrote in message
...
Dear Rick Rothstein

Thanks for your reply.
That is one way of doing it. I apologised for not making things clear
initially.

Actually, I am trying to create something with an "auto complete"
function
on a single cells to search for the tabs name.

Eg,
Sheet1=Search menu
Sheet2=John
Sheet3=Donald
Sheet4=Don
Sheet5=Bill & etc

Once I type something like "do" on a cell on the Search Menu(Sheet1),
it
will lookup and filter, leaving Donald & Don in the selection list.
Thereafter, when I choose "don" on that cell, it will bring me to
Sheet4(Don).

Thanks

Alvyn


"Rick Rothstein (MVP - VB)" wrote:

Are you doing this strictly to be taken to the desired worksheet? If
so,
would right clicking on the tab navigation toolbar (the 4 VCR looking
buttons to the left of the tabs) and selecting the worksheet from the
list
box that appears be an acceptable solution?

Rick


"Alvyn" wrote in message
...
Dear Sir/Mdm,

Can anyone please advice how to search for a specific worksheet tab
based
on
a cell value (user input) on a worksheet.

Eg, I have 50 worksheets in 1 workbook.
Sheet1=Main Menu
Sheet2=Customer ID 1
Sheet3=Customer ID 2
Sheet4=Customer ID 3 and etc.

If I input a value say "Customer ID 45" in a cell in Main Menu
(Sheet
1),
how can Excel "intelligently" brings me to Sheet 46 which is also
Customer
ID
45?

Thank you

Alvyn

Search for tabs from a cell value
 

Alvyn... I was wondering how you made out implementing this. Did you do it
yet? Any problems?

Rick


"Alvyn" wrote in message
...
Dear Rick Rothstein

Thank you very much for the precise instruction. I will try it though I am
unfamiliar with VB.

Alvyn


"Rick Rothstein (MVP - VB)" wrote:

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 the lower case
letters
"ab" followed by a vertical line) and then click/drag on the UserForm 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

' *************** 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 ***************


"Alvyn" wrote in message
...
Dear Rick Rothstein

Thanks for your reply.
That is one way of doing it. I apologised for not making things clear
initially.

Actually, I am trying to create something with an "auto complete"
function
on a single cells to search for the tabs name.

Eg,
Sheet1=Search menu
Sheet2=John
Sheet3=Donald
Sheet4=Don
Sheet5=Bill & etc

Once I type something like "do" on a cell on the Search Menu(Sheet1),
it
will lookup and filter, leaving Donald & Don in the selection list.
Thereafter, when I choose "don" on that cell, it will bring me to
Sheet4(Don).

Thanks

Alvyn


"Rick Rothstein (MVP - VB)" wrote:

Are you doing this strictly to be taken to the desired worksheet? If
so,
would right clicking on the tab navigation toolbar (the 4 VCR looking
buttons to the left of the tabs) and selecting the worksheet from the
list
box that appears be an acceptable solution?

Rick


"Alvyn" wrote in message
...
Dear Sir/Mdm,

Can anyone please advice how to search for a specific worksheet tab
based
on
a cell value (user input) on a worksheet.

Eg, I have 50 worksheets in 1 workbook.
Sheet1=Main Menu
Sheet2=Customer ID 1
Sheet3=Customer ID 2
Sheet4=Customer ID 3 and etc.

If I input a value say "Customer ID 45" in a cell in Main Menu
(Sheet
1),
how can Excel "intelligently" brings me to Sheet 46 which is also
Customer
ID
45?

Thank you

Alvyn