If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
To Ian Thanks!
Yeah! and thanks Ian,
I made my chord builder spreadsheet work-- so far. You can see the results at http://www.ssor.net/Sheet/songbuilder.xls now I just have to figure out how to add # to a key. I could have created a separate button for sharp for each chord, but that would have been too many text boxes. What I want is this: When I select the cell that has the chord already in it, say A I want the cursor to move one character to the right within the cell and then insert # -- so if it was A it would become A# if Am it would become A#m. Doable? Ross |
#2
|
|||
|
|||
Ross,
Is this what you are after? http://cjoint.com/?jzb7KH7Ufd -- HTH Bob Phillips "Ross" wrote in message ... Yeah! and thanks Ian, I made my chord builder spreadsheet work-- so far. You can see the results at http://www.ssor.net/Sheet/songbuilder.xls now I just have to figure out how to add # to a key. I could have created a separate button for sharp for each chord, but that would have been too many text boxes. What I want is this: When I select the cell that has the chord already in it, say A I want the cursor to move one character to the right within the cell and then insert # -- so if it was A it would become A# if Am it would become A#m. Doable? Ross |
#3
|
|||
|
|||
Almost!,
But fori instance in testing it I put my cursor over a line and caused G9 (G ninth) by the macro. Then I clicked the # textbox and the result was G9# what I need is for it to be G#9 (G sharp ninth) The # must be placed as the second carecter in the selected cell. But thanks again, we are getting there! Ross "Bob Phillips" wrote in message ... Ross, Is this what you are after? http://cjoint.com/?jzb7KH7Ufd -- HTH Bob Phillips "Ross" wrote in message ... Yeah! and thanks Ian, I made my chord builder spreadsheet work-- so far. You can see the results at http://www.ssor.net/Sheet/songbuilder.xls now I just have to figure out how to add # to a key. I could have created a separate button for sharp for each chord, but that would have been too many text boxes. What I want is this: When I select the cell that has the chord already in it, say A I want the cursor to move one character to the right within the cell and then insert # -- so if it was A it would become A# if Am it would become A#m. Doable? Ross |
#4
|
|||
|
|||
Also, I guess that is in HTML so how do I see what the macro is?
"Bob Phillips" wrote in message ... Ross, Is this what you are after? http://cjoint.com/?jzb7KH7Ufd -- HTH Bob Phillips "Ross" wrote in message ... Yeah! and thanks Ian, I made my chord builder spreadsheet work-- so far. You can see the results at http://www.ssor.net/Sheet/songbuilder.xls now I just have to figure out how to add # to a key. I could have created a separate button for sharp for each chord, but that would have been too many text boxes. What I want is this: When I select the cell that has the chord already in it, say A I want the cursor to move one character to the right within the cell and then insert # -- so if it was A it would become A# if Am it would become A#m. Doable? Ross |
#5
|
|||
|
|||
Hi Ross,
I waste most of my time trying to play guitar and trying to use excel so I found your post interesting. If you haven't yet solved the problem with sharps the following worked for me: Sub Sharp() If ActiveCell.Value "" Then Dim Chord As String Chord = ActiveCell.Value With ActiveCell .Value = Left(Chord, 1) & "#" & Right(Chord, Len(Chord) - 1) .EntireColumn.AutoFit End With End If End Sub I added the autofit line to improve the chord's visibility. I hope this is useful. Ken Johnson |
#6
|
|||
|
|||
Thanks Ken,
It works perfectly. Ross PS Any ideas of how to traspose keys with a macro? "Ken Johnson" wrote in message oups.com... Hi Ross, I waste most of my time trying to play guitar and trying to use excel so I found your post interesting. If you haven't yet solved the problem with sharps the following worked for me: Sub Sharp() If ActiveCell.Value "" Then Dim Chord As String Chord = ActiveCell.Value With ActiveCell .Value = Left(Chord, 1) & "#" & Right(Chord, Len(Chord) - 1) .EntireColumn.AutoFit End With End If End Sub I added the autofit line to improve the chord's visibility. I hope this is useful. Ken Johnson |
#7
|
|||
|
|||
Ross,
I've never done it but I'm sure it could be done since transposing is just applying rules to the chord pattern. You've got me thinking now, trouble is fast thinking's not my forte. Do you plan on somehow using the table you have on the worksheet? That's a key transposition table I think. |
#8
|
|||
|
|||
Ross,
I've discovered a little problem with the code I gave you that occurs when the chord to be sharpened is in column 1. The lyrics are in column 1 and the autofit causes autofit of the the lyrics. To overcome this problem you should change the line of code just before the End With from ".EntireColumn.AutoFit" to "If ActiveCell.Column 1 Then .EntireColumn.AutoFit" Silly of me! Ken Johnson |
#9
|
|||
|
|||
Ross,
The autofit creates more problems than it solves. I've found a better solution to the visibility problem: All the cells on your sheet at the moment have no fill. This makes the gridlines visible and they get in the way. If you give all of the visible cells a white fill the gridlines disappear and you don't have to worry about the autofit rubbish, then the code can be simplified to:- Sub Sharp() If ActiveCell.Value "" Then Dim Chord As String Chord = ActiveCell.Value ActiveCell.Value = Left(Chord, 1) & "#" & Right(Chord, Len(Chord) - 1) End If End Sub One other thing, the lyrics on the sheet are George Harrison's Something, the first three chords of which are C, CMaj7 then C7. Do you plan on adding a Maj7 button? I'm working on my own version of your idea. I'm including buttons for other chord types such as diminished, augmented, sus4 etc and I hope to eventually have a transposing button. I'll let you know if I have any success. |
#10
|
|||
|
|||
I've given it a little thought too. The chart I have on the screen:
A A# B C C# D D# E F F# G G# A# B C C# D D# E F F# G G# A B C C# D D# E F F# G G# A A# C C# D D# E F F# G G# A A# B C# D D# E F F# G G# A A# B C D D# E F F# G G# A A# B C C# D# E F F# G G# A A# B C C# D E F F# G G# A A# B C C# D D# F F# G G# A A# B C C# D D# E F# G G# A A# B C C# D D# E F G G# A A# B C C# D D# E F F# I created some time ago to use manually to trans pose a song. Say the chords foir a song in C are C, F & G Look at from top to bottom, you see C, F & G correspond to C#, F# & G# in the next column, etc. That is how I used it. But, for the purpose of creating a macro, what I think should happen is each cord should be assigned a number A=1, A#=2, B=3, C=4, C#=5, D=6 ... G=11. and when the macro is invoked, you should be prompted to enter the key the song is in, say C and then you should be prompted as to what key you want it transposed to, say D and then the macro would go through all the chords in the song, and somehow add 2 (not sure if that is the way to say that) to each one, because D is 2 numbers up from A and replace eacht chord with the new one that is 2 up from it, so that C, F and G become, would now be D, G and A. I have no idea how to do this. I am not a programmer and don't know how to write macros at all. I can reverse engineer them -- that is if you send me a formual, or a bit of macro code, I can see what the result is and then be able to use it when needed, but understand it? Not yet. The macro would probably use search and replace somehow, but it would have to ignore everything in the lines with the lyrics somehow. Another thing is that our current setup is fairly week, because it only includes, major, 7th, minor and 9th chords. Say the chords of the song are already in place above the lyrics and a A, b7, Bflat diminished, C+ (augmented). We would want the macro only to deal with replacing the letter A, B and c, so changing the key one step up would give us A# C7, B diminished, C#+. Ross "Ken Johnson" wrote in message oups.com... Ross, I've never done it but I'm sure it could be done since transposing is just applying rules to the chord pattern. You've got me thinking now, trouble is fast thinking's not my forte. Do you plan on somehow using the table you have on the worksheet? That's a key transposition table I think. |
Thread Tools | |
Display Modes | |
|
|