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
