To Ian Thanks!

Ross,
I did a fair bit yesterday but I haven't yet finished. I've changed
things a bit. There is a textbox for each of A, B, C, D, E, F & G, #,
b, m, Maj, sus4, b5, 6, 7, 9, 11, 13, aug and dim. All the textboxes,
except # and b, are assigned to the one macro which reads the caption
on the textbox the user clicked then adds it to the activecell (I'm
learning new stuff doing this!).
The # and b textboxes are each assigned to their own macro which now
has a toggle action ie if there is already a #, clicking the # textbox
deletes it or clicking the b textbox replaces it with a b.
Each of the chord macros formats the Font in the activecell to be Bold
(Activecell.Font.Bold = True). I have done this so that my transposing
code can differentiate Chords (Bold) from Lyrics (Not Bold).
For transposing I'm working towards having an up arrow (one of the
autoshapes) with the caption "Tranpose up one step" and a down arrow
with "Transpose down one step". Clicking the appropriate textbox runs
a macro that works on the range A1:Z100, which should be big enough so
that it doesn't miss any of the chords.
I'm using Excel's SpecialCells Method to pick out the cells with text,
then testing for Bold Format, if True it must be a chord.
The code then parses the chord to extract its key name eg CMaj7=C,
C#Maj7=C#.
It then looks up that key name in an array of sequential key names ie
an array whose elements are A,Bb,C,C#,D,Eb,E,F,F#,G,Ab. The chord's key
name is then changed to the next one up or down depending on which
macro was run.
I'm working on the loop today (I too am not a programmer, I'm too slow.
I'm just a high school science teacher who loves to dabble in Excel as
well as the guitar.).
I'll keep you posted.
Ken Johnson

Ross,
just thought I'd show you my new code for the chord building textboxes
in case you're interested. It's heavily commented. I hope it makes
sense. This macro is run when the user clicks on any of the following
TextBoxes - A,B,C,D,E,F,G, m,Maj,sus4,b5,6,7,9,11,13,dim or aug.
I'll post the Sharp and Flat macros later:


Sub NameChord()
'I have changed the setup of the worksheet so that
'chords are on even numbered rows and lyrics are on
'odd numbered rows. The user will need to be aware
'of this otherwise they will have problems if they
'try to put a chord into a cell in an odd numbered row.
'Gaps between verses can be increased by adjusting row
'height or skipping an even number of rows to keep the
'lyrics in odd numbered rows.
'The first three lines of code is a block If End If which
'checks that the activecell is in an even numbered row.
'If this is not the case the sub is exited so that a chord
'is not entered into a lyric row.
If ActiveCell.Row Mod 2 0 Then
Exit Sub
End If
'It took me ages to get the syntax right in the next five
'lines. When the user clicks one of the textboxes you can
'get Excel to tell you the name of that textbox, which
'happens to be the value returned by the expression
'"Application.Caller". To get to the caption on that
'textbox you have to use an object variable, which here I
'have named WhichTextBox, and since textboxes are Shape
'objects I've dimensioned it as a Shape (It would probably
'still work if it was dimensioned As Object).
'The line starting with the Set verb is crucial. This line
'gives the variable WhichTextBox all of the properties of
'the Clicked Textbox. The property I'm after is the caption.
'Unfortunately Excel doesn't call it that. Excel calls it
'TextFrame.Characters.Text, which is partly why it took so long.
'Anyhow, the string variable ChordName becomes equal to the clicked
'TextBox's caption.
'One other change I made to the worksheet setup involves the
'TextBox names.Excel automatically names them as "Text Box N" where
'N increases by 1 for each new TextBox. I manually renamed them
'"TextBox 01","TextBox 02" etc.
'The expression "Right(WhichTextBox.Name,2)" gets the last two
'characters of the clicked TextBox's name. CInt, which is short for
'convert to integer, then converts the two character string, eg "05"
'to an integer, eg 5. Ensuing code uses this integer to determine
'whether the clicked TextBox is for a Chord Key (A,B,C,D,E,F or G)
'or one of the other TextBoxes.
Dim ChordName As String, WhichTextBox As Shape, _
TextBoxNumber As Integer
Set WhichTextBox = ActiveSheet.Shapes(Application.Caller)
ChordName = WhichTextBox.TextFrame.Characters.Text
TextBoxNumber = CInt(Right(WhichTextBox.Name, 2))
'The next Block If End If prevents the user from starting with anything
'other than A,B,C,D,E,F, or G
If TextBoxNumber 7 And ActiveCell.Value = "" Then
Beep
Exit Sub
End If
'The next Block If End If prevents the user from using two Chord Keys
'eg AA, which is musically incorrect.
If TextBoxNumber 8 And ActiveCell.Value "" Then
Beep
ActiveCell.Clear
Exit Sub
End If
'Finally, the following With End With appends the Clicked TextBoxes
'caption (ChordName variable value) to the ActiveCell and makes it
'Bold, just incase it wasn't already Bold.
With ActiveCell
.Value = .Value & ChordName
.Font.Bold = True
End With
End Sub

Ken,
That is fantastic! can't wait to see the results of this!
Just curious. Where are you? I am in central New York State and am an
itinerant Orientation & Mobility instructor (teach blind people to travel,
using cane, guide dog, etc), who also dabbles at excel (not on your level
though) and the guitar.
Ross

"Ken Johnson" wrote in message
oups.com...
Ross,
just thought I'd show you my new code for the chord building textboxes
in case you're interested. It's heavily commented. I hope it makes
sense. This macro is run when the user clicks on any of the following
TextBoxes - A,B,C,D,E,F,G, m,Maj,sus4,b5,6,7,9,11,13,dim or aug.
I'll post the Sharp and Flat macros later:


Sub NameChord()
'I have changed the setup of the worksheet so that
'chords are on even numbered rows and lyrics are on
'odd numbered rows. The user will need to be aware
'of this otherwise they will have problems if they
'try to put a chord into a cell in an odd numbered row.
'Gaps between verses can be increased by adjusting row
'height or skipping an even number of rows to keep the
'lyrics in odd numbered rows.
'The first three lines of code is a block If End If which
'checks that the activecell is in an even numbered row.
'If this is not the case the sub is exited so that a chord
'is not entered into a lyric row.
If ActiveCell.Row Mod 2 0 Then
Exit Sub
End If
'It took me ages to get the syntax right in the next five
'lines. When the user clicks one of the textboxes you can
'get Excel to tell you the name of that textbox, which
'happens to be the value returned by the expression
'"Application.Caller". To get to the caption on that
'textbox you have to use an object variable, which here I
'have named WhichTextBox, and since textboxes are Shape
'objects I've dimensioned it as a Shape (It would probably
'still work if it was dimensioned As Object).
'The line starting with the Set verb is crucial. This line
'gives the variable WhichTextBox all of the properties of
'the Clicked Textbox. The property I'm after is the caption.
'Unfortunately Excel doesn't call it that. Excel calls it
'TextFrame.Characters.Text, which is partly why it took so long.
'Anyhow, the string variable ChordName becomes equal to the clicked
'TextBox's caption.
'One other change I made to the worksheet setup involves the
'TextBox names.Excel automatically names them as "Text Box N" where
'N increases by 1 for each new TextBox. I manually renamed them
'"TextBox 01","TextBox 02" etc.
'The expression "Right(WhichTextBox.Name,2)" gets the last two
'characters of the clicked TextBox's name. CInt, which is short for
'convert to integer, then converts the two character string, eg "05"
'to an integer, eg 5. Ensuing code uses this integer to determine
'whether the clicked TextBox is for a Chord Key (A,B,C,D,E,F or G)
'or one of the other TextBoxes.
Dim ChordName As String, WhichTextBox As Shape, _
TextBoxNumber As Integer
Set WhichTextBox = ActiveSheet.Shapes(Application.Caller)
ChordName = WhichTextBox.TextFrame.Characters.Text
TextBoxNumber = CInt(Right(WhichTextBox.Name, 2))
'The next Block If End If prevents the user from starting with anything
'other than A,B,C,D,E,F, or G
If TextBoxNumber 7 And ActiveCell.Value = "" Then
Beep
Exit Sub
End If
'The next Block If End If prevents the user from using two Chord Keys
'eg AA, which is musically incorrect.
If TextBoxNumber 8 And ActiveCell.Value "" Then
Beep
ActiveCell.Clear
Exit Sub
End If
'Finally, the following With End With appends the Clicked TextBoxes
'caption (ChordName variable value) to the ActiveCell and makes it
'Bold, just incase it wasn't already Bold.
With ActiveCell
.Value = .Value & ChordName
.Font.Bold = True
End With
End Sub

Ross,
I'm in Sydney, Australia, which I guess puts us on opposite sides of
the Earth, explaining the gaps in our communications. I would normally
be asleep right now (5:30 am) but my daughter had to be up early to go
to a Duke of Edinburgh excursion so I thought I'd just check my Google
Groups.
I finished the transposing macros late last night and I'm happy with
the way it works.
Clicking the up arrow runs a single line macro that makes an integer
variable called intDirection equal to 1, which it then passes on to the
Transpose macro.
Clicking the down arrow runs another single line macro that makes
intDirection equal to -1, which is then passed on to the Transpose
macro.
The Transpose macro has intDirection declared inside the brackets after
its name:
Private Sub Transpose (intDirection As Integer)
so that it can receive and use the value of intDirection.
The Transpose macro basically does the following:
1.Set up a string array variable with fourteen rows and two columns of
chord key names:
Ab,empty string
A,A
Bb,A#
B,Cb
C,C
C#,Db
D,D
Eb,D#
E,Fb
F,E#
F#,Gb
G,G
Ab,G#
A,empty string

2.Locate a cell in the range A1:Z100 with text using the SpecialCells
Method. This is definitely the fastest way to do it.

3.Test the cell for Bold format. If True the cell contains a chord.

4.Make a string variable of the chord's key name by Parsing the chord
name, eg C#Maj7 results in C#. Also, make a string variable of the
chord type eg Maj7.

5.Search the array of chord key names for the chord key name. This
search starts on the second row of the array. The search sequence is
R2C1 then R2C2 then R3C1 then R3C2 then R4C1 then R4C2 etc up to R13C2.
This covers all the possible combinations of A to G with and without #
or b. The extra row at the top and bottom of the array is only used
when an A is transposed to an Ab or an Ab is transposed to an A, which
occurs later in the code.

6.If a chord found in A1:Z100 has a key name that is not found in the
array the offending cell is selected, a message pops up stating that
there is something wrong with the chord in the selected cell and the
macro is aborted. The user then has to fix up the problem before
retrying the transpose.
NB so far no changes have been made to the worksheet so we don't end up
with some chords transposed and others not, which would be disasterous!

7.If no problems were encountered by the code it repeats all the above
steps but, when a match between a cell's chord key name and a chord key
name in the array is found the chord key name is changed to the
previous or next chord key name in column 1 of the array. Whether it is
previous or next is determined by the value of intDirection, which
depends on which arrow was clicked.

8.Combine the new chord key name with the original chord type and
replace the cell with this new value.

This all happens in a short time thanks to the speed of the
SpecialCells method.

I'm thinking of adding one extra featu
What if the user is not happy with say F# (my preference) and would
prefer the enharmonic Gb. I would like the user to be able to select
one of offending chords then click a button that runs a macro that
changes all the instances of F# to Gb. Should be easy. I'll try it
later today.

It's now 7:15 am and I'm on holiday so I'm going back to bed. I won't
post the code because its proper function depends on the set up of the
worksheet.
I'll put the workbook on the web. However, that's something I've never
done before and I will need my son's help to do that. He too is on
holiday and rarely rises before noon.
I'll keep you posted.
Ken Johnson

You have a good nap down there. I guess you are getting ready for summer
pretty soon and we will be getting our usual dose of 4 to 5 months of ice
and snow.
Anyway, I will be waiting to go to that link and check out that spreadsheet
as soon as you get it done.
Ross
"Ken Johnson" wrote in message
oups.com...
Ross,
I'm in Sydney, Australia, which I guess puts us on opposite sides of
the Earth, explaining the gaps in our communications. I would normally
be asleep right now (5:30 am) but my daughter had to be up early to go
to a Duke of Edinburgh excursion so I thought I'd just check my Google
Groups.
I finished the transposing macros late last night and I'm happy with
the way it works.
Clicking the up arrow runs a single line macro that makes an integer
variable called intDirection equal to 1, which it then passes on to the
Transpose macro.
Clicking the down arrow runs another single line macro that makes
intDirection equal to -1, which is then passed on to the Transpose
macro.
The Transpose macro has intDirection declared inside the brackets after
its name:
Private Sub Transpose (intDirection As Integer)
so that it can receive and use the value of intDirection.
The Transpose macro basically does the following:
1.Set up a string array variable with fourteen rows and two columns of
chord key names:
Ab,empty string
A,A
Bb,A#
B,Cb
C,C
C#,Db
D,D
Eb,D#
E,Fb
F,E#
F#,Gb
G,G
Ab,G#
A,empty string

2.Locate a cell in the range A1:Z100 with text using the SpecialCells
Method. This is definitely the fastest way to do it.

3.Test the cell for Bold format. If True the cell contains a chord.

4.Make a string variable of the chord's key name by Parsing the chord
name, eg C#Maj7 results in C#. Also, make a string variable of the
chord type eg Maj7.

5.Search the array of chord key names for the chord key name. This
search starts on the second row of the array. The search sequence is
R2C1 then R2C2 then R3C1 then R3C2 then R4C1 then R4C2 etc up to R13C2.
This covers all the possible combinations of A to G with and without #
or b. The extra row at the top and bottom of the array is only used
when an A is transposed to an Ab or an Ab is transposed to an A, which
occurs later in the code.

6.If a chord found in A1:Z100 has a key name that is not found in the
array the offending cell is selected, a message pops up stating that
there is something wrong with the chord in the selected cell and the
macro is aborted. The user then has to fix up the problem before
retrying the transpose.
NB so far no changes have been made to the worksheet so we don't end up
with some chords transposed and others not, which would be disasterous!

7.If no problems were encountered by the code it repeats all the above
steps but, when a match between a cell's chord key name and a chord key
name in the array is found the chord key name is changed to the
previous or next chord key name in column 1 of the array. Whether it is
previous or next is determined by the value of intDirection, which
depends on which arrow was clicked.

8.Combine the new chord key name with the original chord type and
replace the cell with this new value.

This all happens in a short time thanks to the speed of the
SpecialCells method.

I'm thinking of adding one extra featu
What if the user is not happy with say F# (my preference) and would
prefer the enharmonic Gb. I would like the user to be able to select
one of offending chords then click a button that runs a macro that
changes all the instances of F# to Gb. Should be easy. I'll try it
later today.

It's now 7:15 am and I'm on holiday so I'm going back to bed. I won't
post the code because its proper function depends on the set up of the
worksheet.
I'll put the workbook on the web. However, that's something I've never
done before and I will need my son's help to do that. He too is on
holiday and rarely rises before noon.
I'll keep you posted.
Ken Johnson

Ross,
I finally finished around 10 pm.
My son assures me you should be able to download the file from:
http://s36.yousendit.com/d.aspx?id=0...A30204HHJY9LHR
He said something about changing http to hxxp if it doesn't work as is.
I haven't the foggiest what he's on about you might have a better idea.
Sorry I couldn't figure out how to paste it as a hyperlink.
I would've preferred to use the free webspace through our isp,
unfortunately our best computer went in for repair today (CPU
overheating to 100 deg C then shutting down) and the necessary and
forgotten username and password are on it.
Let me know if you have any problems getting the file and I'll email
it, it's only 96k.
When you do get the file let me know what you think and whether there
are any problems or possible improvement that could be made. I haven't
had time to thoroughly test it.
I'm happy with the way it is working. Ignore all the random chords that
are on it.
I've just spotted one little problem - the font seems to sometimes
randomly change from Verdana to Times Roman. I might have to set the
font name in the code.

One useful thing about the way it works is if you prefer say + to aug
you could change the caption on the aug textbox to + instead of having
to change the code.

Make sure the lyrics are never bold because the code relies on
chords/bold, lyrics/regular for proper function. If this is a problem I
could change the code to use chords/even rows, lyrics/odd rows. The
chord building code already relies on odd/even rows.

I've included a comment in U1. Just hold the cursor over U1 to read the
information about the enharmonic button.

I've done most of the code on an old iMac which doesn't have a very
large screen view so everything might look a bit bunched up on your
screen.

That's all for now. I've got to put the garbage bins out now for
collection tomorrow.

Ross,
I notice that the web address turns out to be a hyperlink once it is
posted. I learn something new everday :-)
Ken Johnson

Ken
So far it works like a charm! Here is a funny one. It just so happens
that tomorrow is garbage day and if you hadn't reminded me, I would have
totally forgotton.
Great Job. I will get lots of use out this. I will paste in some songs I
have and check it out and let you know ASAP
Ross
"Ken Johnson" wrote in message
oups.com...
Ross,
I finally finished around 10 pm.
My son assures me you should be able to download the file from:
http://s36.yousendit.com/d.aspx?id=0...A30204HHJY9LHR
He said something about changing http to hxxp if it doesn't work as is.
I haven't the foggiest what he's on about you might have a better idea.
Sorry I couldn't figure out how to paste it as a hyperlink.
I would've preferred to use the free webspace through our isp,
unfortunately our best computer went in for repair today (CPU
overheating to 100 deg C then shutting down) and the necessary and
forgotten username and password are on it.
Let me know if you have any problems getting the file and I'll email
it, it's only 96k.
When you do get the file let me know what you think and whether there
are any problems or possible improvement that could be made. I haven't
had time to thoroughly test it.
I'm happy with the way it is working. Ignore all the random chords that
are on it.
I've just spotted one little problem - the font seems to sometimes
randomly change from Verdana to Times Roman. I might have to set the
font name in the code.

One useful thing about the way it works is if you prefer say + to aug
you could change the caption on the aug textbox to + instead of having
to change the code.

Make sure the lyrics are never bold because the code relies on
chords/bold, lyrics/regular for proper function. If this is a problem I
could change the code to use chords/even rows, lyrics/odd rows. The
chord building code already relies on odd/even rows.

I've included a comment in U1. Just hold the cursor over U1 to read the
information about the enharmonic button.

I've done most of the code on an old iMac which doesn't have a very
large screen view so everything might look a bit bunched up on your
screen.

That's all for now. I've got to put the garbage bins out now for
collection tomorrow.

Ross,
I have noticed that my iMac doesn't have Verdana so it was using Times
New Roman instead and I'm not sure why because I think Arial is the
default Font. Anyhow, I'm hoping that explains the random Font change.
If the problem persists:
ActiveCell.Font.Name = "Verdana"
could be included in the code whenever it changes the value of a Chord
cell.

I did a bit of experimenting to see what would happen if the code
changes the Font Name to one that is not on the machine. I was
expecting an error to occur.
ActiveCell.Font.Name = "Poo" did not cause an error! The appearance of
the font in the affected cell did not change, it didn't even change to
the default Font, and "Poo" appeared in the Font Name Box on the
toolbar at the top of the window, even though the machine does not have
a Font named Poo.

Ken Johnson

Ross,
I'm glad to hear that, I'll add Garbage Reminding Service to my CV ;-)
Actually I don't have a CV, I've been teaching in state schools for
almost 29 years and am looking forward to retiring at the end of 2009,
then I can do all the Excel I like. Excel is so complex even if I live
to a hundred I'll still only know a tiny fraction of its workings.
What sort of guitar do you play?
The best guitars come from USA.I've got an old Epiphone acoustic that
I bought second hand in 1978. It's got a lovely mellow tone and is
really easy to play. I've also got a Gibson ES 175D but I haven't
played it for a long time. One of the plastic tuning knobs perished and
broke and I haven't yet been able to replace it.
I love the Beatles' music (who doesn't) and I try to play jazz. I can't
improvise unfortunately, but my chord playing is OK. I know it's not
guitar, but I'm a big fan of Bix Beiderbecke. I had a go at playing the
cornet when I started teaching, which was in a country town, Finley,
with a population of just 2000 and a 7 hour drive from Sydney, where I
grew up. I joined the Finley Brass Band and was taught how to play.
They were pretty desperate for players. They even supplied the cornet,
which was an American long cornet, just like the one Bix played. I was
gobsmacked. However, my playing never came within a bull's roar of
sounding like Bix, I rarely got to play solo cornet and my lip got
tired very quickly. Still, I did manage to play in the band for most of
the 9 years I taught in Finley. By the way, have you heard of a band
called Spider Bait? Their lead singer,Janet English was Girls' School
Captain at Finley High School when I was teaching there. Before Spider
Bait had even formed I was playing (rhythm guitar and the occasional
cornet for a laugh) in a 50/50 Bush/Rock band and we called ourselves
Blinky Bill, which was partly a reformation of an earlier band called
Mulwala Bill and the Far Canals (Finley is in the Riverina Irrigation
area and gets its water from the Mulwala Canal). After I left Blinky
Bill, because I was moving back to Sydney, Blinky Bill became Spider
Bill. I still don't know if the name Spider Bait was just a
coincidental similarity or whether it was a deliberate copy. Spider
Bait would have formed after Spider Bill.
My favourite jazz guitarist is John Scofield. He has played in
Australia a few times and I've seen him twice, quite a while ago now.
I'd better go now I have to deliver about 450 local newspapers today.
My daughter starting doing it about 4 years ago, quickly lost interest
and left it for me to do, which I don't mind, I need the exercise
(Doing Excel exercises the mind but not the body!) and I get paid for
it.

Ken Johnson