Searching for Spreadsheet Data utilizing VBA

[HansHamm]

I am new to this forum and VBA...I have been utilizing formulas to obtain the information I need, but now I REALLY need to start learning and converting over to VB/VBA...
Here is the scenario:
I have 3 columns; Column A is the Store Number, Column B is the Store Name, Column C is the District Manager who has the store. The DM's name can re-occur 15-20 different times. Store Number and Store Name is Unique.
On my userform I have a listbox (NameSelect) for the DM's, Then another one for the Store Number (StoreSelect) Then just a textbox for Store Name (StoreName). What I would like is to have it when the DM selects his/her name and presses the command button (SubmitName) that it will only show the stores assigned to that DM. Then when the DM scrolls thru the store number the store name would also scroll. It is sort of like an index match formula..
Any ideas out there??? I have reviewed several postings and from what I have seen, someone knows! Y'all ARE GOOD!!!

Thanks in Advance!

Hans

 

[CautionMP]

Here is one solution.
Assumptions:[ol][li]This code is in the sheet with the data (hence the use of the Me keyword)[/li][li]The labels/data begin in cell A1[/li][li]You want to 'hide' the filter functionality from the user[/li][/ol]

Public Sub FilterForDM(DM_Name As String)
Dim rngFilter As Range
Me.Activate
Me.Cells(1, 1).Activate
Set rngFilter = ActiveCell.CurrentRegion
'Hide the filter dropdown but dont filter column 1 (Store Number)
rngFilter.AutoFilter 1, , , , False
'Hide the filter dropdown but dont filter column 2 (Store Name)
rngFilter.AutoFilter 2, , , , False
'Hide the filter dropdown and show only DM_Name in Column 3 (District Manager)
rngFilter.AutoFilter 3, DM_Name, , , False
End Sub

Once you change the [tt]Me.[/tt] pieces to match your environment you should be able to add [tt]FilterForDM[/tt] to the Event Procedure for your command button (SubmitName), and pass it the value from listbox (NameSelect).

Hope this helps,
CMP

Instant programmer, just add coffee.

 

[abaldwin]

You seem to be moving in the right direction but the way I see it you are at a cross roads. Excel is not a database by any stretch of the imagination. I understand that you may receive you data in Excel but what you are starting to experience with retrieving and manipulating data points to the need to put the data into a db such as SQL server or more than likely ACCESS.

This will allow you to use standard query functions to return, manipulate, and report on your data more effienciently than what you are trying to do in Excel.

If you are starting to see this need now you will probably be given more complex things to do with the data later and will have a heck of a time do that in Excel.

Excel is a spreadsheet and even though it is quite often used as a storage media, it is NOT a database and its "tables" are not truely TABLES.

Even though CautionMP has given a pliable solution in Excel this same thing can be accomplished with one or two lines of code in an access db.

Just my $.02

Andy

Andy Baldwin

"Testing is the most overlooked programming language on the books!

 

[SkipVought]

Hans,

I would take issue with Andy. If this were a larger, more complex application, his opininons would have more merit, in my opinion.

But I would re-design your table into NORMAILZED tables, which will be much easier to manipulate.

You can use MS Query (built into Excel) to do the restructure AND the queries for your Form. Wheter in Access or Excel, you would be much better served to learn and understand database principles.

RULES:

One sheet per Table

On row of unique headings per table starting in A1

No empty rows/columns within tables

http://www.alphabetcitydataworks.com/FundamentalsOfRelationalDatabaseDesign.doc

Skip,

_{[red]Sign above the facsimile apparatus at the music publisher:[/red]
If it ain't baroque...
Don't FAX it!}

 

[HansHamm]

Hey, I appreciate the help! I have been involved in other issues the last 2 weeks and will look into what you all were stating. Thanks for the help and I have 1 additional question. I realize that I need to REALLY convert & learn VB/VBA. What is your opinion of where to really get the loaddown on VB coding? i.e. from simplistic to advanced. I am going to have to learn this on my own, so it's going to be fun... Thanks!

 

[CautionMP]

Conceptually I agree with Andy, but there are limitations to Access databases*. (I'm going to catch grief for saying that, see note below.)

I am a database guy though.

Places to start:[ol]
[li]VBA:
[ul][li]Start recording macros. Begin with things you know how to do and then pick the recorded scripts apart.[/li][li]While picking the macros apart spend some quality time with the object browser, it's your best friend.[/li][li]Help files, I know, I know, but they can be helpful if you start to think Microsoft.[/li][/ul][/li]
[li]Access:[ul][li]The easiest way to start is to link/import to the data you already have in Excel.[/li][li]Spend some time with Skip's idea, how could the data you have be normailized?[/li][li]Keep in mind that once you have the data in Access you can 'cook' it and dump it back into Excel. I've learned from experience that downstream (upstream?) processes want data in Excel.[/li][/ul][/li][/ol]
The most important things to remember:[ul][li]Do Have fun.[/li][li]Don't have pre-conceived notions about what can/can't be done with VBA.[/li][li]Remember we (at Tek-Tips) are always happy to help out![/li][/ul]

CMP
_{*NOTE: The limitations I've encountered are, er, um, user related in nature.}

Instant programmer, just add coffee.