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

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

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

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

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

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

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.

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

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.

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.

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

Hey Ken,
Checked it out with a song and it works perfectly. I think you are much
more musically advanced than I am -- I play by ear, but have been playing
for about 30 years and it took me about 15 years before poeple didn't
immediately get up and leave the room when I played, but I do continue to
improve. Anyway, I have sent the song I put in their up to my domain for
you to check out. What is a CV?
Anyway, it is at http://ssor.net/Sheet/ I realized that you can build a
whole songbook with it.
Ross

"Ken Johnson" wrote in message
oups.com...
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

Hi Ken
Last night I was just getting ready for bed when I wrote that post, and
was half asleep. I wanted to add: I tried to paste in the "I Will" after
copying it from a word file that already had the chords over the lines and
realized that the chords, like the text would all be pasted in column A, so
I had to remove the chords after pasting them in and then re-insert them
manually. I can't even begin to tell you how impressed I am that you were
able to create this wonderful tool..
I also wanted to add that I have always been fascinated with Australia and
have even considered pursuing my profession -- itinerant O&M instructor, and
even looked into it a little when I first started in the field(that was at
age 52 when I got my degree and certification 5 years ago). But, when I
contacted officials in the field to discuss it via email, they informed me
that firstly, I would have to have at least one year teaching experience
before I could be considered and secondly, if I did get hired to a
position, the likelihood that I would need to travel by plane to many of my
clients, due to vast distances between them. This put a damper on that. Do
you live in an urban or rural area? Can you look out your window and see
things like Kangaroos, Koalas and Wallabies?
I was pondering the synchronicity of it all between us -- Guitar, excel and
garbage pickup on the same day, when I realized that in fact we don't have
garbage pick-up on the same day. Mine is on Wednesday -- that is today. It
is Thursday for you, right? Or did I figure my international time
incorrectly. If you could, please give me your address, so I can go to
Google Earth (have you checked that out -- a satellite image of any place in
the world! It is a free download from http://earth.google.com/) and check
out where you live. I am at 306 N. Highland Ave, East Syracuse, New York .
If you download the Google Earth program and punch in that address, your
screen will fly you across the planet, as if you were in a space ship and
you can then zoom in and see a satellite image of my house.
Ross
"Ross" wrote in message
...
Hey Ken,
Checked it out with a song and it works perfectly. I think you are much
more musically advanced than I am -- I play by ear, but have been playing
for about 30 years and it took me about 15 years before poeple didn't
immediately get up and leave the room when I played, but I do continue to
improve. Anyway, I have sent the song I put in their up to my domain for
you to check out. What is a CV?
Anyway, it is at http://ssor.net/Sheet/ I realized that you can build a
whole songbook with it.
Ross

"Ken Johnson" wrote in message
oups.com...
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

Ross,
CV = Curriculum Vitae, which is a document outlining your,
qualifications, abilities, achievements etc. You submit your CV to a
prospective employer and if they like what they read you're one step
closer to getting the job, otherwise you're culled out. I've never had
to put one together but my son Eric had to for a job in a carwash cafe,
making coffees and serving food to customers while their car is being
washed and polished. He got the job and he's there right now. My
daughter also had to submit one for her casual job (mainly cashier
work) at Target, a Chain of Department stores. Target is pretty
down-market so everybody jokingly pronounces it Tar-shay to improve its
image.
CV's are a recent phenomenon. I applied for my first job (apprentice
photoengraver-etcher) in 1966 and got the job because I could write my
name. Technological change put an end to that job. After just 2 years
of a 4 year apprenticeship the number of workers had halved. My job
served the letterpress printing industry which was losing work to the
lithographic printing industry. I could see no future in my job so I
completed school at night while completing the apprenticeship by day. I
then went through Uni with the support of a Teachers' Scholarship. I
was actually paid to go to Uni (We baby boomers had it so easy. I feel
sorry for my kids, without parental support they're stuffed).
It's funny you should ask me about seeing kankaroos etc out the window.
It's a long standing joke that Americans think that kangaroos hop down
George St (the main street of Sydney). About 18 years ago I was
teaching at Pit****er High School Mona Vale. Mona Vale is a beachside
suburb in Sydney's north and about a 30 min drive from where I live (68
William St Roseville Sydney, one of the northern suburbs). The school
has a sister school in USA, I think it's called New Trier High, but I
forget where it's located. A visit was organised and we had the company
off a New Trier High science teacher and some students for a couple of
weeks. One morning, during that two week visit, as I was driving to
school a wallaby hopped across the road in front of me. (the road
passes through a National Park). This was a unique experience for me,
and some Americans returned home from Australia knowing that kangaroos
on George St is not all that far from the truth.
I've had a look at your songbook. How do you set up those textboxes
with hyperlinks on the menu sheet? I'd like to know how to do that, it
looks useful.
Could you put one of your song word docs on your site? Word is
controlled by its version of VBA, I've used it a little bit and I might
be able to do something about the problem you explained (could take a
while though). I'm keen to manipulate Word through Excel's VBA. It
involves using references, of which I know very little, and this looks
like an opportunity for me to get started on a new VBA skill.
I haven't yet looked at that Google Planet Earth thingy (what ever it's
called), I clicked on the Preview button to review what I've typed then
when I returned to edit I can no longer refer to your messages. I'm
scared I'll lose everything if I fiddle around trying to get back to
your messages, it's happened to me before.
I too play guitar by ear. The only time I read music is when I play
cornet, which is never these days. I used to do things like play a 45
rpm record at
33 1/3 to slow down fast riffs (still couldn't play some of them) or 78
rpm to raise the pitch and audibility of bass lines, but that was a
long time ago.
I don't have perfect pitch but I have been able to work out some of
John Scofield's weird chords. A music teacher once asked me to work out
the chords off a tape recording and she was pleased with the result. My
daughter plays the trumpet and has done quite well. She's up to grade
whatever(I've lost count). She's wearing braces at the moment, and they
have held her back a bit. They come off sometime next year. She's
studying music for her Higher School Certificate and sometimes hints at
becoming a high school music teacher. I try to steer her away from that
idea. We Australian teachers are constantly having to go on strike to
improve our pay and conditions but we always seem to be going
backwards. When I started teaching in 1977 our pay was equivalent to
that of a politician on the back benches, who are now way ahead of
teachers.
Getting back to the songbuilder: Please don't take offence (We need
them to keep in the kangaroos) but I'm not happy with the chords for I
Will. I've played them and, to me, something sounds wrong. You can call
me a pedant, I won't mind, but my preferred chord sequence for I Will
is A, F#m, Bm, E, A, F#m, C#m (we differ here), A, D, E, F#m, A, D, E,
A, A7, D, E, F#m, D, E, A, D, E, F#m, B, E. I'm just using my ear here,
I haven't bothered to listen to the track or look up the music and
there are, from memory, a couple of spots in the song where there are a
some strong and fast chord changes that I have missed out. Try out my
chords and let me know what you think. Do you sing when you play? When
I was in the Bush/Rock band in Finley I sang a few songs to give our
singer the odd break, and the only thing I had thrown at me was a pair
of panties (obviously done as a joke by a friend in the audience).
Bye for now, I've got some marking I must get done these holidays and
time is slipping away.
Ken Johnson

Ross,
I downloaded Google Earth and it flew straight to your place. Is that a
backyard swimming pool a few houses from you? Does it get that hot in
summer and snow and ice in winter?
I typed in my address and it failed to find it . It even failed to find
Chatswood, Sydney, the next suburb and Sydney's third biggest business
district. I don't know what's wrong there.
Let me know how you go with it? I can find my place manually and email
it. Or maybe you can input latitude and longitude. Do you know if that
is possible?
By the way, you were right first time about our garbage collections,
we're both Wednesday (review your use of the international dateline. I
never get it right either.)

Ken Johnson

Ross,
Latitude and longitude of the cursor are shown at the bottom on the
left. Just keep moving the Earth until the cursor is at 33 deg 47'
18.40" S and 151 deg 11' 00.30" E and you should be on top of the
Hanzmann-Johnson dwelling (My wife wanted to save the Hanzmann name
from extinction by tacking mine on to hers. She is from a family of
three girls.)
Ken Johnson

Couldn't get exactly to those coordinates. Can I just have your phyiscal
address -- Street, number and postal code should do.
"Ken Johnson" wrote in message
oups.com...
Ross,
Latitude and longitude of the cursor are shown at the bottom on the
left. Just keep moving the Earth until the cursor is at 33 deg 47'
18.40" S and 151 deg 11' 00.30" E and you should be on top of the
Hanzmann-Johnson dwelling (My wife wanted to save the Hanzmann name
from extinction by tacking mine on to hers. She is from a family of
three girls.)
Ken Johnson

I will answer longer later on rest of this message, but creating the textbox
hyperlinks is easy
Just create a textbox from the draw menu and put the desired text in it.
then right-click it and one of the items on the menu that drops down is
hyperlink. Select that and in the "insert hyperlink" dialogue box that
opens, click icon on middle left that says "place in this document" Then in
the box in the middle, you will see the tabs of the page listed. Just click
on the desired tab you want the textbox to link to and click ok. Note that
when you click on the tab, the cell reference in box above that comes in at
A1 automatically, but you can make it go anywhere on the sheet by typing in
any cell reference.
Ross

Ken Johnson" wrote in message
oups.com...
Ross,
CV = Curriculum Vitae, which is a document outlining your,
qualifications, abilities, achievements etc. You submit your CV to a
prospective employer and if they like what they read you're one step
closer to getting the job, otherwise you're culled out. I've never had
to put one together but my son Eric had to for a job in a carwash cafe,
making coffees and serving food to customers while their car is being
washed and polished. He got the job and he's there right now. My
daughter also had to submit one for her casual job (mainly cashier
work) at Target, a Chain of Department stores. Target is pretty
down-market so everybody jokingly pronounces it Tar-shay to improve its
image.
CV's are a recent phenomenon. I applied for my first job (apprentice
photoengraver-etcher) in 1966 and got the job because I could write my
name. Technological change put an end to that job. After just 2 years
of a 4 year apprenticeship the number of workers had halved. My job
served the letterpress printing industry which was losing work to the
lithographic printing industry. I could see no future in my job so I
completed school at night while completing the apprenticeship by day. I
then went through Uni with the support of a Teachers' Scholarship. I
was actually paid to go to Uni (We baby boomers had it so easy. I feel
sorry for my kids, without parental support they're stuffed).
It's funny you should ask me about seeing kankaroos etc out the window.
It's a long standing joke that Americans think that kangaroos hop down
George St (the main street of Sydney). About 18 years ago I was
teaching at Pit****er High School Mona Vale. Mona Vale is a beachside
suburb in Sydney's north and about a 30 min drive from where I live (68
William St Roseville Sydney, one of the northern suburbs). The school
has a sister school in USA, I think it's called New Trier High, but I
forget where it's located. A visit was organised and we had the company
off a New Trier High science teacher and some students for a couple of
weeks. One morning, during that two week visit, as I was driving to
school a wallaby hopped across the road in front of me. (the road
passes through a National Park). This was a unique experience for me,
and some Americans returned home from Australia knowing that kangaroos
on George St is not all that far from the truth.
I've had a look at your songbook. How do you set up those textboxes
with hyperlinks on the menu sheet? I'd like to know how to do that, it
looks useful.
Could you put one of your song word docs on your site? Word is
controlled by its version of VBA, I've used it a little bit and I might
be able to do something about the problem you explained (could take a
while though). I'm keen to manipulate Word through Excel's VBA. It
involves using references, of which I know very little, and this looks
like an opportunity for me to get started on a new VBA skill.
I haven't yet looked at that Google Planet Earth thingy (what ever it's
called), I clicked on the Preview button to review what I've typed then
when I returned to edit I can no longer refer to your messages. I'm
scared I'll lose everything if I fiddle around trying to get back to
your messages, it's happened to me before.
I too play guitar by ear. The only time I read music is when I play
cornet, which is never these days. I used to do things like play a 45
rpm record at
33 1/3 to slow down fast riffs (still couldn't play some of them) or 78
rpm to raise the pitch and audibility of bass lines, but that was a
long time ago.
I don't have perfect pitch but I have been able to work out some of
John Scofield's weird chords. A music teacher once asked me to work out
the chords off a tape recording and she was pleased with the result. My
daughter plays the trumpet and has done quite well. She's up to grade
whatever(I've lost count). She's wearing braces at the moment, and they
have held her back a bit. They come off sometime next year. She's
studying music for her Higher School Certificate and sometimes hints at
becoming a high school music teacher. I try to steer her away from that
idea. We Australian teachers are constantly having to go on strike to
improve our pay and conditions but we always seem to be going
backwards. When I started teaching in 1977 our pay was equivalent to
that of a politician on the back benches, who are now way ahead of
teachers.
Getting back to the songbuilder: Please don't take offence (We need
them to keep in the kangaroos) but I'm not happy with the chords for I
Will. I've played them and, to me, something sounds wrong. You can call
me a pedant, I won't mind, but my preferred chord sequence for I Will
is A, F#m, Bm, E, A, F#m, C#m (we differ here), A, D, E, F#m, A, D, E,
A, A7, D, E, F#m, D, E, A, D, E, F#m, B, E. I'm just using my ear here,
I haven't bothered to listen to the track or look up the music and
there are, from memory, a couple of spots in the song where there are a
some strong and fast chord changes that I have missed out. Try out my
chords and let me know what you think. Do you sing when you play? When
I was in the Bush/Rock band in Finley I sang a few songs to give our
singer the odd break, and the only thing I had thrown at me was a pair
of panties (obviously done as a joke by a friend in the audience).
Bye for now, I've got some marking I must get done these holidays and
time is slipping away.
Ken Johnson

Ross,
68 William Street, Roseville 2069. Maybe the postcode will make a
difference.
Thanks for the hyperlink tips. I noticed on yours that when the cursor
changes to the pointing finger the hyperlink address that appears
included C drive and I thought this could be a problem if the file is
being used on another machine at a different address. However, the
hyperlink that I inserted, following your instructions, used an address
that did not include the file's location, so I guess I needn't worry.
Ken Johnson

Hi Ken,
I seem to have encountered a bug. Added another song and I think I
followed the rules, but everything but the G transposes. Check it out at
http://ssor.net/Sheet/MySongbuilder1.xls
The song is have a cup of tea by the Kinks.
Ross
"Ken Johnson" wrote in message
oups.com...
Ross,
68 William Street, Roseville 2069. Maybe the postcode will make a
difference.
Thanks for the hyperlink tips. I noticed on yours that when the cursor
changes to the pointing finger the hyperlink address that appears
included C drive and I thought this could be a problem if the file is
being used on another machine at a different address. However, the
hyperlink that I inserted, following your instructions, used an address
that did not include the file's location, so I guess I needn't worry.
Ken Johnson

Ross,
I noticed that the problem chords were not Bold, and it seemed to be
all the chords in column A. How were they put in place? Did you paste
them in from a Word Doc?
What I think I should do is add a few more lines of code to the
transpose macro that will make all of the even rows Bold and the odd
ones Regular. That should solve it.
I'll have a go later on tonight. I've got to feed the hungry hordes
soon. The wife's at work.
By the way, I was looking at the rest of your website earlier today. I
love the horoscope! It's so spot on. I'll check it again tomorrow to
see if it's predictive powers are consistent ;-)
I thought I knew most of the Kink's songs but I don't remember this
one. I'll see if Eric can download a copy for me.
While you wait for me to change the code you can just reset those
chords back to bold.
Ken Johnson

Ross,
The problem's now solved.
We're back in action with our good computer and Eric has uploaded
MySongBuilder2.xls
to http://hanjohn.customer.netspace.net.au/

One other problem I've noticed when you do a transpose is when chords
are in adjacent cells and the chord on the left changes from a natural
to a sharp or flat. This chord is then partly obscured by the chord to
its right. I'm thinking about a macro that could be used to increase
the number of spaces between words in a line that suffers from this
problem so that chords can be spaced further apart to avoid their
overlapping.
Ken Johnson

Hey Ken,
Well, I tried to enter your address into Google Earth and found that I could
It would only accept "Roseville Australia" and give results. I tried
putting your street address in con****ion with the above numberous ways but
always got the message "your serach returned no results.
Is this --- http://www.ssor.net/pics/Roseville.jpg Roseville? If so I
found that, but I can't figure out how to find you.
Ross

Ross
hnson" wrote in message
oups.com...
Ross,
68 William Street, Roseville 2069. Maybe the postcode will make a
difference.
Thanks for the hyperlink tips. I noticed on yours that when the cursor
changes to the pointing finger the hyperlink address that appears
included C drive and I thought this could be a problem if the file is
being used on another machine at a different address. However, the
hyperlink that I inserted, following your instructions, used an address
that did not include the file's location, so I guess I needn't worry.
Ken Johnson

Firstly,
OK, you were right, those chords do work better -- couldn't quite follow what you meant with all of them, but anyway here is
(I just switched to rich format, so you should be able to go there from that link, but if not then http://ssor.net/Sheet/MySongbuilder2.xls )
I also added a little bonus material
1. My trained squirrel sings for you
2. a print macro button that prints the song out for you.

Ross
Ross
"Ken Johnson" wrote in message oups.com...
Ross,
The problem's now solved.
We're back in action with our good computer and Eric has uploaded
MySongBuilder2.xls
to http://hanjohn.customer.netspace.net.au/

One other problem I've noticed when you do a transpose is when chords
are in adjacent cells and the chord on the left changes from a natural
to a sharp or flat. This chord is then partly obscured by the chord to
its right. I'm thinking about a macro that could be used to increase
the number of spaces between words in a line that suffers from this
problem so that chords can be spaced further apart to avoid their
overlapping.
Ken Johnson

I just realized, that if you click on the link, it will open the songbuilder
on the web and you can hear the little bonus I sent by clicking where
prompted, but not without being prompted as to whether you want to save
songbuilder, because it is closing it to open your windows media file. Not
sure how to embed the mp3 in the excel file itself-I could only link to it..
If you want them to work together at home you will need to download both
files to the same directory the mp3 file at ht www.ssor/Sheet/ I guess you
figured out that ssor is Ross backwards, huh?
Ross

"Ross" wrote in message
...
Firstly,
OK, you were right, those chords do work better -- couldn't quite follow
what you meant with all of them, but anyway here is
(I just switched to rich format, so you should be able to go there from that
link, but if not then http://ssor.net/Sheet/MySongbuilder2.xls )
I also added a little bonus material
1. My trained squirrel sings for you
2. a print macro button that prints the song out for you.

Ross
Ross
"Ken Johnson" wrote in message
oups.com...
Ross,
The problem's now solved.
We're back in action with our good computer and Eric has uploaded
MySongBuilder2.xls
to http://hanjohn.customer.netspace.net.au/

One other problem I've noticed when you do a transpose is when chords
are in adjacent cells and the chord on the left changes from a natural
to a sharp or flat. This chord is then partly obscured by the chord to
its right. I'm thinking about a macro that could be used to increase
the number of spaces between words in a line that suffers from this
problem so that chords can be spaced further apart to avoid their
overlapping.
Ken Johnson

Ross,
It was a bit of a fiddle, but I finally got your strained squirrel to
sing. Our possoms don't sound that good. I bet you made the Squirrel
sing button by copying the Print I Will button. They were both assigned
to the print I Will macro. I've since fixed that. You probably have
too.
I had to get the mp3 off your site. Did you make that site? I don't
know the first thing about web development.
I took your test and only got one wrong. I didn't know Spot's name. I
said Andy. I was thrown by Kung Fu Andy. Is Kung Fu Andy a real dog?
(Could do some harmonies with the squirrel)
I had a look at the eye page and have learnt something. I wrongly
thought that the fovea and macula were the same thing. I now know the
macula surrounds the fovea.
My late mother suffered from age related macula degeneration. She was
an avid reader. The talking books eased her boredom. Her younger
sister, who is still with us, developed the same symptoms and had
successful laser treatment. I'm seeing her and my cousin tomorrow.
Last night I finished the changes I mentioned in my last post. It's
available as Mysongmaker3 at the now usual place:

http://hanjohn.customer.netspace.net.au/

We've now got different versions coming out our ears.
I think the two new buttons are self explanatory. They only affect the
one selected lyric line. I thought it wouldn't be necessary to expand
all lyric lines, just those that have a lot of chord changes.
Another change I made is not realy visible but might prove to be
useful. Before I made this change, the cell range that was worked on by
the transpose and enharmonic macros was A4:Z100. I've extended the
width of this range out to the last visible column. It now might be
possible to fit the whole song on the visible part of the screen with
parts of the song side by side (no scrolling). I've also made it easy
to increase the number of rows in the working range. It is currently 97
rows (row4 to row100). Towards the top of the code in the transpose and
enharmonic macros the variable LastRow is currently made equal to 100.
Change this value to suit the circumstances.
Are you happy with the Chords/bold, Lyrics/regular aspect of the whole
thing. I'm pretty sure it can be done away with. I sometimes
unwittingly apply unnecessary conditions and I think that is the case
here. The chords/even rows, Lyrics/odd rows is enough for the code to
recognise what is what.
Your Google Earth snapshot does show where I live. I'll try to upload a
picture.
Ken Johnson

Ssor,
Yes, I had.
I used to teach with a John Flanagan. Most of us called him Naganalf
because he was a bit of an incessant talker.
I used to teach a girl by the name of Roopi Kalsi, her name backwards
is a social comment that I used for inspiring students to work harder.
Nek Nosnhoj

Ross,
I have successfully uploaded a word doc "I live here"
http://hanjohn.customer.netspace.net.au/
Ken Johnson

Hey Ken,
I sent you a nice long email, complete with pictures, but apparently, the
message list doesn't like it, because I don't see it posted. Could you send
me a normal email address that we can communicate with using our inboxes?
Ross
"Ken Johnson" wrote in message
oups.com...
Ross,
I have successfully uploaded a word doc "I live here"
http://hanjohn.customer.netspace.net.au/
Ken Johnson

Ross,
I used to use our main email address for Google Groups and the kids
recommended I use a Gmail account instead, so Gordon set one up for me.
I never bothered to check it for messages though, I wasn't sure how to.
I just asked Gordon about it and we've received five messages. I
haven't read them yet, but I did see the Google Earth shot with your
annotations plus one of a mass of puppies. I'm about to have a look now
on this older computer. Wish me luck.
Ken Johnson

Ken,
I have sent a few emails at the account you gave me. Have you got them.
What email program are you useing? You are using outlook express right? If
not, why not? ARe you accessing your mail through the web? If that is the
way you are going, you should really try outllook express. It is one hundred
times easier and you have more control over what you are doing. Outlook
express comes packaged for free with internet explorer -- also free. Are
you using XP? With outlook express, each time it is loaded in a series of
frames appear, it opens up with folders in the left frame, a list of
messages in the top right frame and the currently selected message in the
bottom right frame. You can easily ad links to web pages and attach things
like excel files, so you don't have to upload them to the internet, you can
send them directly to me. You can add pictures and make the text scroll
around them. The fact the I.E. and I.E. express reamin to be free, never
ceases to amaze me. Besides the inbox, outbox, saved, deleted, and whatever
other folders you create yourself, through outlook express, you can join any
one of the bajillion newsgroups out there and that is how I access the Ecel
newsgroup, by just switching to that folder.

Ross
"Ken Johnson" wrote in message
oups.com...
Ross,
I used to use our main email address for Google Groups and the kids
recommended I use a Gmail account instead, so Gordon set one up for me.
I never bothered to check it for messages though, I wasn't sure how to.
I just asked Gordon about it and we've received five messages. I
haven't read them yet, but I did see the Google Earth shot with your
annotations plus one of a mass of puppies. I'm about to have a look now
on this older computer. Wish me luck.
Ken Johnson