Search function that searches worksheet names

welsh dai

New Member
Joined
Oct 6, 2006
Messages
8
Hi everyone you have another Mr Excel virgin here!! (y)

Firstly want to say what a great message board system going on here...so much information and knowledge its incredible!! Keep it up guys!!

Now down to my question...I have searched for the answer but being very new to the whole world of Excel and VBA i thought it would be easier to just ask!!

I have designed a workbook that contains details of various software companies. There are numerous work sheets each sheet being specific to one company. The work sheet tab names are labled with the software companies name.....

Zebra Computer, Casetrack, Videss, Peapod ...and the list goes on!!! About 30 sheets in total.

What I am after is to create a function on the first page so that the user can input the software company name and click search and the function locates the relevant worksheet and makes it active.

From searching the site I know this is very possible...Its just putting it into practice. Is there a code that would enable me to do this? Would the code be linked to the search command button?

Any advice would be grately appreciated.

Thanks in advance for helping me out with this guys.

Dai
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Presuming the company is in cell A1, then setup a command button and assign the following code to it:

Code:
dim s as object, target as string, destination as string
target = [a1]
for each s in sheets
if s.name = target then destination = s.name
next s

if destination = "" then 
msgbox("Could not find company")
else sheets(destination).select
end if
 
Upvote 0
How quick was that for a reply!!!

Thanks for that Patrick! I notice that in your code the company that is being searched is entered into cell a1. Is there any way this can be eneterd into a text box?

Also I take it that this code searches for the names of the worksheets? If so what changes would need to be made to the code for it to search through all the information on each sheet. Basically searching the whole workbook for the company name??
 
Upvote 0
Absolutely,

If you goto the Control Toolbox (View-->Toolbars) then you can use the textbox from that, right click it and select properties. You will then be able to give the control a name.

After you have named it, edit the code so that instead of saying [a1], it says

Code:
yourtextboxname.text

obviously, where yourtextboxname is the name you gave to the box when you put it on the sheet.

Yes the code does search through names of worksheets, if you wanted to search within every cell in each worksheet, the code would take a performance hit, as there are 65000x256 cells in each sheet. Would you be able to narrow down the searchable range a bit - i.e. if you wanted to just look at the range a1:z100 in each sheet?
 
Upvote 0
That is brilliant....I am amazed!! You are a Excel God!!

I have a cell, well its a merged cell that covers B:2 - D:2 this would have company name and name of software. So to look in this cell on each sheet would reduce the work Excel would have to do to search for a match??

Is the search Case sensetive? How would I de sensetise the search?
 
Upvote 0
haha excel god i doubt, there are some people on these forums that are a lot lot better than me

If the sheet is called the company name already, then it would not make a difference whether we look in a cell or whether we just look at the sheet name (in fact it would probably be ever so slightly slower to look in a cell)

The following code makes it case insensitive:

Code:
dim s as object, target as string, destination as string 
target = ucase(yourtextbox.text) 
for each s in sheets 
if ucase(s.name) = target then destination = s.name 
next s 

if destination = "" then 
msgbox("Could not find company") 
else sheets(destination).select 
end if


Hope this helps
 
Upvote 0
Excellent.....I love it!!

Sorry to keep asking questions but I am on a roll!! Haha!!

How about just searching for part of the name. On one worksheet name for instance it is:

Zebra Computer - Castrack. With Zebra Computer being the company name and Castrack being the software name.

Now the supplier may only know the software name so will try to search for Castrack but the search comes back with 'Cannot be found' how can I programme the serach function to match the queried word in any part of the sheet name??

Patrick thanks for your continued help.
 
Upvote 0
Code:
sub test
dim s as object, target as string, destination as string 
target = ucase(yourtextbox.text) 
for each s in sheets 
if testname(ucase(s.name), ucase(target)) = true then
destination = s.name
end if
next s 

if destination = "" then 
msgbox("Could not find company") 
else sheets(destination).select 
end if 

end sub


function testname(sheetname as string, tofind as string) as boolean
dim a as integer, tofindlen as integer, boolcheck as boolean
a=1
tofindlen = len(tofind)
do while a + tofindlen < (len(sheetname) + 1)
if mid(sheetname,a,tofindlen) = tofind then
     boolcheck = true
     exit do
end if
a=a+1
loop
end function


Give the above a try, its untested here though - if it doesn't work, let me know and i'll mock up a simulation of what you're doing to see where its falling over
 
Upvote 0
Scrap that, try this code:

Code:
Sub test()
Dim s As Object, target As String, destination As String
target = yourtextboxname.text
For Each s In Sheets
If testname(UCase(s.Name), UCase(target)) = True Then
destination = s.Name
GoTo 1
End If
Next s
1
If destination = "" Then
MsgBox ("Could not find company")
Else: Sheets(destination).Select
End If

End Sub


Function testname(sheetname As String, tofind As String) As Boolean
Dim a As Integer, tofindlen As Integer, boolcheck As Boolean
a = 1
tofindlen = Len(tofind)
Do While a + tofindlen < (Len(sheetname) + 2)
If Mid(sheetname, a, tofindlen) = tofind Then
     testname = True
     Exit Do
End If
a = a + 1
Loop
End Function
 
Upvote 0
Thanks for this Patrick....sorry to be really thick but where would I paste the above code?? Would it be linked to the command button??
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top