|
|
|
|
Full Member
|
|
Jul 27, 2014, 12:21 PM
|
|
Need VBA beyond my knowledge
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.
|
|
|
Computer Expert and Renaissance Man
|
|
Jul 27, 2014, 12:45 PM
|
|
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.
|
|
|
Full Member
|
|
Jul 28, 2014, 09:31 AM
|
|
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.
|
|
|
Computer Expert and Renaissance Man
|
|
Jul 28, 2014, 09:43 AM
|
|
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.
|
|
|
Software Expert
|
|
Jul 28, 2014, 05:19 PM
|
|
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.
|
|
|
Full Member
|
|
Jul 28, 2014, 06:42 PM
|
|
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
|
|
|
Software Expert
|
|
Jul 30, 2014, 01:09 PM
|
|
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?
|
|
|
Full Member
|
|
Jul 31, 2014, 02:01 AM
|
|
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.
|
|
|
Software Expert
|
|
Jul 31, 2014, 08:03 AM
|
|
Just shot in the dark silly suggestions, maybe edit this one line of code:
Code:
ws.UsedRange.AutoFilter 2, Range("D3").Value
|
|
|
Full Member
|
|
Aug 1, 2014, 03:29 AM
|
|
Hah... shot in the dark suggestion fixed it. Thanks JB
Home
|
|
Question Tools |
Search this Question |
|
|
Add your answer here.
Check out some similar questions!
VBA If Statement
[ 4 Answers ]
Ok. Here's my problem. I have a spreadsheet that I use which filters on 6 different filter criteria and copies the result set of each filter criteria to a separate worksheet. However, on occasion there are no results found for a given criteria.
What I want to do is when no results are found...
Can VBA do this for me?
[ 7 Answers ]
Refer attachment;
For sheet "SPPA" cell "D18" value to be recorded on sheet "HO plan" cell "T7", and this is where it gets nasty, as the date rolls over on the "SPPA" sheet cell "F1", so must the macro record the new value in the next cell for sheet "HO plan", ie: If "SPPA" sheet cell "F1"...
Can this be done using VBA?
[ 5 Answers ]
Ok - I have a spreadsheet that is exported from an application I use at work. It formats the data in a manner that makes it really difficult to manipulate but I think I've worked out a solution that gets me what I need; only it is very tedious.
The data starts in row 16 of the sheet. There is...
View more questions
Search
|