A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

To Ian Thanks!



 
 
Thread Tools Display Modes
  #1  
Old September 24th, 2005, 10:01 PM
Ross
external usenet poster
 
Posts: n/a
Default 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  
Old September 25th, 2005, 12:47 AM
Bob Phillips
external usenet poster
 
Posts: n/a
Default

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  
Old September 25th, 2005, 01:00 AM
Ross
external usenet poster
 
Posts: n/a
Default

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  
Old September 25th, 2005, 01:03 AM
Ross
external usenet poster
 
Posts: n/a
Default

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  
Old September 25th, 2005, 03:55 AM
Ken Johnson
external usenet poster
 
Posts: n/a
Default

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  
Old September 25th, 2005, 04:19 AM
Ross
external usenet poster
 
Posts: n/a
Default

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  
Old September 25th, 2005, 04:34 AM
Ken Johnson
external usenet poster
 
Posts: n/a
Default

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  
Old September 25th, 2005, 05:00 AM
Ken Johnson
external usenet poster
 
Posts: n/a
Default

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  
Old September 25th, 2005, 08:19 AM
Ken Johnson
external usenet poster
 
Posts: n/a
Default

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  
Old September 25th, 2005, 04:06 PM
Ross
external usenet poster
 
Posts: n/a
Default

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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:40 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.