I'd like to create a Form to set criteria for MS Query in Excel 2003

[charlylynn]

Hello,

I have a spreadhseet that has an excess of 30,000 rows (information about housing stock) and I want my users to be able to specify the criteria they require so they only see the rows of information relevant to them. Normally 10-20 out of the 30,000+.

I have used MS Query to successfully manipulate the data and return the required results on a new worksheet, but my end users are non-IT (case work) staff so I want to provide them with a basic form for specifying the relevant criteria (as much or little as required) and a 'go' button to run the query. - The User form does part of this, but doesn't provide a printable list like MS Query does.

E.G.
Number of bedrooms = >2
Post Code = G13
Floor = <1

The crietria will change everytime someone uses the form, so a fixed query will not work. Is it possible to use VBA to create the form that will then run the query with the specified criteria?

I also have the added issue that the workbook needs to be protected to prevent changes, but with protection enabled I cannot start the Query Wizard.

I know I should (and I would prefer) to do this in MS Access, but for a number of reasons that is not an option. Also, I am hindered by still (!) using Office 2003...

Any and all help will be gratefully appreciated!

[Izandol]

Perhaps you can use advanced filter rather than MSQuery? So your users may enter the criteria below each column heading and then your code will copy the filtered data from the hidden data sheet to a new results sheet.

[charlylynn]

Thank you for your very prompt reply, but I'm afraid I don't fully understand what you mean.

I currently don't have any hidden data sheets (why would I hide them?) and as I stated in my original post, I don't have any code yet either... Can you maybe give me an example of what you mean (sorry if I am being dim!)

I have tried to use the filter options to return desired results but it is limited, I will have another look at the advanced filter though.

Thanks again,
Charly

[Izandol]

It was my assumption that you did not want the users to be able to see or change the data? Hiding the sheet would be a simple way to do that - but not foolproof of course!

A code example would be like this:

Please Login or Register  to view this content.

[charlylynn]

Thank again for the reply. Users should be able to see all the data (that's why I am giving it to them) but I want it to be read-only.

When I tried using the advanced filter I didn't get the results I expected (I was missing some rows, which definietly matched the criteria) and it is still somewhat complicated for users with limited/no Excel experience, hence wanting to provide them a simple form like the Userform which will run a query/filter for them...