Excel 2003
Workbook with sheets "Search, A, B, C, etc (through alphabet)
Sheets a, b, c, etc have a song name in col A, and a letter code in col B.


Clicking on "search" sheet opens a userform with radio buttons.
Clicking a radio button puts a letter code value into cell D3 on the "search" sheet.


I have the above set up and will attach the file.
---------------------------------------------------------------------


VBA would search sheets (a, b, c, etc) col B for the matching code from "search" sheet cell D3.
Copy the song names with matching code to col A on "search" sheet.


When a different radio button is selected, it would clear the previous copied song names
and serach for the new matching code, and copy those songs to the "search" sheet.

Just a point, Excel is not a database. It can be used to track simple data, but it sounds like you have exceeded the capabilities of Excel in easily tracking data. While I believe you can do what you need using VBA, you can do what you want in Access (a real database) with little if any coding.

I do have Access... looked at it once and was rather confused.
Much rather excel but may have to give it another look.

Thanks for the reply.

Access is not an advanced version of Excel. And if you try to look at it that way, you will be confused.

I took a look at your spreadsheet, So all you have is a single table with title and code. (In Access you would create a lookup table for the codes).

You could use a multi-value field to select as many songs as you want, then print out a report of the selected songs.

I've added the TITLES to every sheet to make the filtering of data clean and simple. Then built a macro into the SEARCH sheet module that will watch for any change in cell D3. If you edit that cell, all the rows from all the sheets with a matching column B value will be collected.

JB... thanks for the file.

Not working as I expected though. It seems to bring all songs in and filters/sorts.

Only wanted to bring songs that match the key code put in D3 by the radio button selection.

If I click "Prelude"... only songs that have the key of "P" will be copied to the Search sheet.

Looked at the code, but did not understand it enough to try and fix.

Thanks

When I click on the Prelude in your form, it puts a P in D3 which causes all the "P" rows to be copied in from all the other sheets. How is that not what you wanted?

Not sure what's going on JB... it does not work that way on my computer.

Does not matter which radio button is selected, it brings all songs in.

http://i819.photobucket.com/albums/zz111/lacogada/SearchSongs1_zps5d72277a.jpg (http://s819.photobucket.com/user/lacogada/media/SearchSongs1_zps5d72277a.jpg.html)


http://i819.photobucket.com/albums/zz111/lacogada/SearchSongs2_zps501213c9.jpg (http://s819.photobucket.com/user/lacogada/media/SearchSongs2_zps501213c9.jpg.html)

Well, that's just odd. I just opened the same file above, working perfectly.

Video: 2014-07-31_0749 - JerryBeaucaire's library (http://screencast.com/t/10prnpZg)

Just shot in the dark silly suggestions, maybe edit this one line of code:


ws.UsedRange.AutoFilter 2, Range("D3").Value

Hah... shot in the dark suggestion fixed it. Thanks JB

Home (http://www.dandrafting.com/JBcode/)