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