Find tab in worksheet

  • Thread starter Thread starter Rodney
  • Start date Start date
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
 
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
 
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 ??
 
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
 
Back
Top