Search function that searches worksheet names

[welsh dai]

Hi everyone you have another Mr Excel virgin here!!

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

 

[patrickmuldoon99]

If you press Alt + F11 in Excel the VBA editor will appear -then goto Insert --> Module

Paste the above code there, then close down the VBA editor

In excel, right click on your command button and click assign macro.

When it asks you what macro you want to assign to, selet the macro called test

Give it a whirl, it should work - it works on mine.

Patrick

 

[welsh dai]

Can't find any option called assign macro when I right click!!

Bum!

 

[patrickmuldoon99]

Are you sure its a command button that you are using? You need to goto View --> Toolbars --> Forms and use the command button from there

That should work for you


alternatively, you could right click on your existing button, and select view code - then paste the code i supplied above, but remove the following lines

sub test

end sub

this is so that the code that originally fitted between those two lines will now start with something like

sub commandbutton1_click


There needs to be one end sub at the end (before the function) but pasting the code as is above will generate two end subs (one from my code, and one that will be there already)

 

[welsh dai]

wow.....you are the best!! That is awesome!!! Its works a treat!!

Thank you so much Patrick!!

 

[patrickmuldoon99]

No probs my friend

Glad to help

 

[welsh dai]

Last question I promise!!

Now that I have a working search function. Can it be situated on a floating userform that is always visable even when a different worksheet is selected??

Therefore the user will have the search function always on the active worksheet!!

 

[patrickmuldoon99]

If you can setup the userform okay, and give it the name 'companysearch' then you can do the following

Move the code that is situated on that command buttons click event, and double click the command button on your form and put it in there...

Are you running a version of excel later than 97?

If so, use the following code to launch the form

companysearch.show 0

Which will make it a modeless form, allowing the user to interact with excel whilst its open.

When pasting the code into the command button of your form, you need to make sure that the function is kept separately from the routine that is run on that event. In fact, you only need to copy the code that is between the sub test and end sub into that command button. Leave the function where it is.

Let me know if you have any probs...

 

[Joe Was]

This code will add an In-Cell DropDown listing all the sheet names, using a Validation List. When the user selects a name from this DropDown that Sheet is selected. The dropdown list is automatically updated every time you add a new sheet, AKA: Company Name.

You need to add this code to the ThisWorkbook code module!
You need to Change the Range of the In-Cell DropDown in the code below!


Public cellAddressOfDropDown As Range

Private Sub Workbook_NewSheet(ByVal Sh As Object)
'ThisWorkbook code, only!
Dim myShtLst$

'**************************************************************************
'*** Change the location of your DropDown of Sheets here! *******
'**************************************************************************
Set cellAddressOfDropDown = Sheets("Sheet1").Range("B3")
'**************************************************************************

Worksheets(1).Select

For Each Sh In Worksheets
If myShtLst = "" Then
myShtLst = Sh.Name
Else
myShtLst = myShtLst & "," & Sh.Name
End If
Next Sh

On Error GoTo myNew
If cellAddressOfDropDown.Validation.Type <> 0 Then

With cellAddressOfDropDown.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=myShtLst
.IgnoreBlank = True
.InCellDropdown = True
End With
Else

myNew:
With cellAddressOfDropDown.Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=myShtLst
.IgnoreBlank = True
.InCellDropdown = True
End With
End If
End Sub

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
'ThisWorkbook code, only!

If Target.Address <> cellAddressOfDropDown.Address Then Exit Sub
Sheets(cellAddressOfDropDown.Value).Select
End Sub