EXCEL SEARCH 2

[maeling]

I have a worksheet with four columns and 80,000 rows of data.
When I distribute this file to my end users I would like them to be able to open the file and be presented with a search field that enables the to search for the data located in any of the columns by using part words and complete words.
I have never used VBA before - can you help ?

 

[TonyJollans]

Hi maeling,

1. If you have 80,000 rows of data you do not have an Excel worksheet (max 65,536 rows).

2. Can your users not use Excel's Find? Or do you simply want it to fire automatically when the worksheet opens?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[maeling]

Hi Tony,
OK sorry I was rounding up an approximate number. The issue is my excel file (csv) is a dump of a database table. The raw CSV has no formatting, sorting or even column headings. I was planning on creating a new workbook and using the MS query to refresh the work sheet data. I would like to worksheet to sort automatically, have column headings but more importantly to have the search of find facility start automatically so that it looks more like a database application with a mini front end rather than just a spreadsheet ? Does this makes sense ?

Maeling

 

[TonyJollans]

Hi maeling,

I wasn't just making a facetious point. If you do have, or might have, more than 64K rows then you will have to consider what to do with your data.

Enough on that. What I suggest you do is record yourself using Excel's Find so that you can see the code needed to invoke it, and then designing a Userform for your own front end as you want it to be. Setting it to run when the workbook opens is a minor matter when you have it designed.

I, and others here, can help with the mechanics. The design is down to you! To create a Userform, Select Insert > Userform from the menu in the VBE. Add controls, change the properties, etc. See how you get on and come back with specific questions as a nd when you have them

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[maeling]

Thanks for the information.
You are going to laugh at my next statement - I don't currently have VBE, is this the development environment ? Is it available in Excel ? Can I down load it, or do I have to buy it ?

 

[dhulbert]

maeling.

Goto tools-macro-visual basic editor
or just press Alt-F11 to open the VBA environment.
To record a Macro use Tools-Macro-Record new Macro

 

[maeling]

OK - be patient with me - I have found the VBE - !!!!!
I have started to design my form and the tools are very simialr to MS Access which I am more fanmiliar with. However my MS Access forms are usually based on tables and I seem to missing a trick with linking my text boxes to Excel Fields (columns)???

 

[TonyJollans]

Hi maeling,

Excel Userforms do ahve some similarities with, but are not the same as, Access Forms. Whilst you can, almost, make them appear to behave in the same way, they are not really meant for that. They can display information or be used for gathering it, but cannot be bound to queried data in the same way.

I thought you were just looking to provide a front end to a search. Do you really want something more sophisticated?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[maeling]

Hi Tony,
sorry I was going off on one.
Yes your right I only want the end user to be able to search on two of the columns of data but would like to form to display the results of the entire row eg

Part Code Part Desc Qty Price


I would like form to be able to search on either the part code field or the part description. Onnce Excel has completed the search I would like to be able to see all the fields above.
I have created a test worksheet and have inserted a user form - what control do I now need ?

 

[TonyJollans]

Hi maeling,

Not really sure what you're asking, but ..

I think you probably want a couple of textboxes to allow input to your search and a button (with appropriate code in the Click event) to kick it off.
Then (assuming you don't have lots and lots of columns) a few more textboxes to be populated from the search result row. You might find the Control Source property helpful.

I don't know if you want to add Next and Previous Buttons but it wouldn't be hard to do. Basically at every user interaction you go away and find the data and populate the textboxes and refresh the display.

HOWEVER, before going any further you should take a look at the built in basic Data Form (Data > Form from the Menu)

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[maeling]

Hi Tony,
the built in form looks good the layout is almost exactly what I want apart from the search.
How can I search using this form, other than by next record ?

Can I edit this form ? (to rip off the code)

How can i get my form to start automatically when I open the document ?

Am I asking too much ?

 

[TonyJollans]

Hi maeling,

Just click on Criteria, enter what you want to find and click on Find Next

AFAIK, you can't get at the 'code' behind the Form, although you can drive it from code with

[blue]Application.CommandBars("Data").Controls("F&orm...").Execute[/blue]

and you can get code to run automatically by putting it in the Workbook_Open Event - in the ThisWorkbook Code Module, select Workbook from the dropdown at the top left (it will default to Open I think, but if not select Open from the dropdown top right) and put your code in the skeleton procedure it provides.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[maeling]

Hi Tony,
thanks again for your help.

I have managed to deploy the code you provided me with for the deafult form ("data") and it works well.

How can I now get my form to work in the same way as the default form i.e the ability to search. I don't suppose you would be interested in having a look at my form would you ?

 

[TonyJollans]

Hi maeling,

As a rule, I don't like to take things off line. There are occasions when it helps to be able to see things but this is a public forum and information and help are posted for the benefit of all, and that aspect of it is lost if work is done behind the scenes, as it were - also you lose the benefit of many experts here (who know more than me).

Searching over multiple columns isn't one of Excel's strongest points, particularly in code. It can be done with Advanced Filtering but that's not so useful in code. The best way to proceed depends on how your data are organized. Look in Help for all the Lookup functions, and also Find. Or if you can post some sample data and your requirements I, or someone else will take a look.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[KenWright]

Just in case it's of any use, John Walkenbach provides a free enhanced Data Form that is far more flexible than the built in one.

If you want to get to the code behind the form then even that is available for a small fee (ie $20)

http://j-walk.com/ss/dataform/index.htm

Regards
Ken.............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[maeling]

Hi Ken,
Sorry for the delay in getting back to you. I have been away and have been playing with the form you advised me of.
Can you help me further?
Using the code TonyJollans provided me with how do I edit this to call in the j-walk form
Application.CommandBars("Data").Controls("F&orm...").Execute
Also do you know if it’s possible to have the form permanently displayed on screen whilst the workbook is open?

Thanks

 

[tbl]

To display a form permanently, use Show Form VBmodeless

Richard

 

[maeling]

Hi tbl,
thanks - as yo can probably guess I am a novice user but getting better everyday. Can you assist with my previous query i.e how do I get the Jwalk form to display when I open the workbook and where do I enter the code that you kindly provided ?

 

[TonyJollans]

Hi maeling,

What you would have to do is show the form when the workbook opened (In Workbook_Open Event), disable the Exit button (and other exit strategies), and close the Form when the workbook closed. It's the disabling of form exitting that would be your problem - you would need the source code for that.

The startup code, once the AddIn is installed, could be:
[blue][tt] Application.CommandBars("Data").Controls("JWalk Enhanced Data Form...").Execute[/tt][/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[