Find tab in worksheet

R

Rodney

I have a workbook with many tabs & many users and would like to create a 'Go
to / find' function that finds a particular tab when opening workbook, so
that user will enter tab in text box and will then go directly to tab
 
J

Jacob Skaria

Try any one of these macros..

You can try out the below macro. If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub Macro1()
Dim strSheet As String
strSheet = InputBox("Enter sheet name")
Sheets(strSheet).Activate
End Sub

Sub Macro2()
Dim strSheet As String
strSheet = InputBox("Enter part of sheet name")
For Each ws In Sheets
If ws.Name Like "*" & strSheet & "*" Then ws.Activate: Exit For
Next
End Sub
 
R

Rodney

Thank you v much, i have used the first one, is there any way I can make this
box appear anytime the workbook is opened ??
 
J

Jacob Skaria

You can. Use the workbook open event

From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the
left treeview search for the workbook name and click on + to expand it.
Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

Double click 'This WorkBook' and paste the below code to the right code pane.



Private Sub Workbook_Open()
'Place your code here
End Sub
 
Top