Hi,
I don't know how to write macros, I only create them by recording them. I
would like to know how I tell the cursor to move cells to right, left, or up
and down, from a cell, so that when I run the macro, the movent will happen
from the current selected cell.

Also, I am trying to create a spreadsheet that I can paste lyrcs to a song
into, and then having a series of text box buttons I create -- each with the
name of a chord, like Am or b7 as the text in it. Each one of these would
be assigned a macro. I want to be able to click above a cell above the
particular line of lyric, and then go and click on the text box associated
with the chord I want and have that cord pop into selected cell. I would
format all the columns as one cell wide. I did this and when I paste in the
lyracs, say starting at A1, the lyrcis are displayed across the page, even
though they are all in the first cells. of column A. (I doulbe space the
lyrics before I paste them in, leaving a row of single spaced cells above
each line, in which to put my chords) Is that doable?

Right click on sheet tab, select View Code and paste this code at the
flashing cursor.

As written the macro will move the cursor down one cell form the current
position.

Sub OffsetSelectedCell()
'Replace r & c below as required
r = 1 ' 0 = stay put, 1 = down 1, -1 = up 1
c = 0 ' 0 = stay put, 1 = right 1, -1 = left 1
ActiveCell.Offset(rowOffset:=r, columnOffset:=c).Activate
End Sub


--
Ian
--
"Ross" wrote in message
...
Hi,
I don't know how to write macros, I only create them by recording them.
I would like to know how I tell the cursor to move cells to right, left,
or up and down, from a cell, so that when I run the macro, the movent will
happen from the current selected cell.

Also, I am trying to create a spreadsheet that I can paste lyrcs to a song
into, and then having a series of text box buttons I create -- each with
the name of a chord, like Am or b7 as the text in it. Each one of these
would be assigned a macro. I want to be able to click above a cell above
the particular line of lyric, and then go and click on the text box
associated with the chord I want and have that cord pop into selected
cell. I would format all the columns as one cell wide. I did this and
when I paste in the lyracs, say starting at A1, the lyrcis are displayed
across the page, even though they are all in the first cells. of column A.
(I doulbe space the lyrics before I paste them in, leaving a row of single
spaced cells above each line, in which to put my chords) Is that doable?

Thanks Ian,
That will be useful. Now what I want to happen for the second part of my
question is this: I want to invoke my chord placement macros (as explained
in second part of first post) so that when I click on the textbox I have a
assigned the macro to it will paste the chord, E or F or F# in the currently
selected cell. I will create a series of small text boxes, each with the
text for the chord A, Am, A7, etc and assign a different macro for each one.
What I want to happen is, I click on a particular cell and when I click the
textbox with chord I want in the selected cell, it is pasted (or typed) into
that cell. Since I will also have a transpostion chart that I have created
on the sheet, using this method, I will easily be able to transpose the
chords of any song I want. I then want to have one more text box that has #
in it and the way the macro would work assigned to that one is that say the
chord I need is A#m I would invoke the Am macro, double-click that cell so
that my cursor is between the A and the m and then click on the # text box
to insert the sharp. That way, I don't have to have a separate text box for
A,A#, Bm,B#m . . .
To make this clearer, I have uploaded the file "Song builder" with an
example song pasted in. Please have a look at it.
It is at: http://www.ssor.net/Sheet/songbuilder.xls

Thanks in advance for your help

wrote in message ...
Right click on sheet tab, select View Code and paste this code at the
flashing cursor.

As written the macro will move the cursor down one cell form the current
position.

Sub OffsetSelectedCell()
'Replace r & c below as required
r = 1 ' 0 = stay put, 1 = down 1, -1 = up 1
c = 0 ' 0 = stay put, 1 = right 1, -1 = left 1
ActiveCell.Offset(rowOffset:=r, columnOffset:=c).Activate
End Sub


--
Ian
--
"Ross" wrote in message
...
Hi,
I don't know how to write macros, I only create them by recording them.
I would like to know how I tell the cursor to move cells to right, left,
or up and down, from a cell, so that when I run the macro, the movent
will happen from the current selected cell.

Also, I am trying to create a spreadsheet that I can paste lyrcs to a
song into, and then having a series of text box buttons I create -- each
with the name of a chord, like Am or b7 as the text in it. Each one of
these would be assigned a macro. I want to be able to click above a cell
above the particular line of lyric, and then go and click on the text box
associated with the chord I want and have that cord pop into selected
cell. I would format all the columns as one cell wide. I did this and
when I paste in the lyracs, say starting at A1, the lyrcis are displayed
across the page, even though they are all in the first cells. of column
A. (I doulbe space the lyrics before I paste them in, leaving a row of
single spaced cells above each line, in which to put my chords) Is that
doable?

I just had a quick look at your file, and I think I understand what you
want, but I'm not sure how to achieve it. I notice you've edited my macro to
offset by 5 rows, but I don't understand why. I assume you want to move up
one cell to put the chord in.

Try these for starters:

' This is necessary to pass the variable s (for sharp) to other subroutines
Dim s As Integer

Sub A() ' Assign to A button
ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate ' Makes the cell
above the current one active
If s = 1 Then ' If # has been pressed
ActiveCell.Value = "A#"
s = 0 'deselect #
Else
ActiveCell.Value = "A"
End If
End Sub

Sub Am() ' Assign to Am button
ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate ' Makes the cell
above the current one active
If s = 1 Then ' If # has been pressed
ActiveCell.Value = "A#m"
s = 0 'deselect #
Else
ActiveCell.Value = "Am"
End If
End Sub

Sub Sharp() ' Assign to # button
s = 1
End Sub

All the other chords follow the same pattern a A and Am above.

I'm not sure what you want to do with the transposition chart, but I'm
assuming it's a case of looking in the first row for your existing chord and
replacing it with the chord in one of the rows below (although it may be
columns instead). You probably need to look at the HLOOKUP or VLOOKUP
functions. I'm afraid I don't have time to look at specifics for you just
now.
--
Ian
--
"Ross" wrote in message
...
Thanks Ian,
That will be useful. Now what I want to happen for the second part of my
question is this: I want to invoke my chord placement macros (as
explained in second part of first post) so that when I click on the
textbox I have a assigned the macro to it will paste the chord, E or F or
F# in the currently selected cell. I will create a series of small text
boxes, each with the text for the chord A, Am, A7, etc and assign a
different macro for each one. What I want to happen is, I click on a
particular cell and when I click the textbox with chord I want in the
selected cell, it is pasted (or typed) into that cell. Since I will also
have a transpostion chart that I have created on the sheet, using this
method, I will easily be able to transpose the chords of any song I want.
I then want to have one more text box that has # in it and the way the
macro would work assigned to that one is that say the chord I need is A#m
I would invoke the Am macro, double-click that cell so that my cursor is
between the A and the m and then click on the # text box to insert the
sharp. That way, I don't have to have a separate text box for A,A#,
Bm,B#m . . .
To make this clearer, I have uploaded the file "Song builder" with an
example song pasted in. Please have a look at it.
It is at: http://www.ssor.net/Sheet/songbuilder.xls

Thanks in advance for your help

wrote in message
...
Right click on sheet tab, select View Code and paste this code at the
flashing cursor.

As written the macro will move the cursor down one cell form the current
position.

Sub OffsetSelectedCell()
'Replace r & c below as required
r = 1 ' 0 = stay put, 1 = down 1, -1 = up 1
c = 0 ' 0 = stay put, 1 = right 1, -1 = left 1
ActiveCell.Offset(rowOffset:=r, columnOffset:=c).Activate
End Sub


--
Ian
--
"Ross" wrote in message
...
Hi,
I don't know how to write macros, I only create them by recording them.
I would like to know how I tell the cursor to move cells to right, left,
or up and down, from a cell, so that when I run the macro, the movent
will happen from the current selected cell.

Also, I am trying to create a spreadsheet that I can paste lyrcs to a
song into, and then having a series of text box buttons I create -- each
with the name of a chord, like Am or b7 as the text in it. Each one of
these would be assigned a macro. I want to be able to click above a
cell above the particular line of lyric, and then go and click on the
text box associated with the chord I want and have that cord pop into
selected cell. I would format all the columns as one cell wide. I did
this and when I paste in the lyracs, say starting at A1, the lyrcis are
displayed across the page, even though they are all in the first cells.
of column A. (I doulbe space the lyrics before I paste them in, leaving
a row of single spaced cells above each line, in which to put my chords)
Is that doable?

Thanks Ian,
But I am afraid I didn't make myself clear. The first question about
moving cells was separate, I just wanted to know how to do that, because I
could not figure out how to do it while recording a macro. The second
question was about the chord thingy. Since the song's text that you see in
that page is pasted into cells in the A colum, I never want to select
anything in the rows that the text is in. I want to select blank cells
above the text:

C <----select this cell before invoking the macro, then click on the C
text box to have C put in there
Something in the way she moves.

Then if I wanted that to be C#, I would select that cell and hit the # text
box and it would become C#

is that more clear?

Just one more totally separate question that relates to tying in text in
just about any windows program. I am typing along, and I am in the default
insert mode, but after using the delete key to fix a mistake the damn thing
goes into delete mode -- I don't want it to do that. Is there some setting
somewhere that I can stop that from going to delete mode? If so, what is it
called and where is it located?

Thanks for all your help

Ross

As far as the transpotition goes, that is manual -- unless you can show me a
way for it to not be-- possibly using search and replace. For example if a
song has chords across the top of a line that go: A A# B C and I wanted to
transpose starting A#, They would become A# B C C#
"Ian" wrote in message
...
I just had a quick look at your file, and I think I understand what you
want, but I'm not sure how to achieve it. I notice you've edited my macro
to offset by 5 rows, but I don't understand why. I assume you want to move
up one cell to put the chord in.

Try these for starters:

' This is necessary to pass the variable s (for sharp) to other
subroutines
Dim s As Integer

Sub A() ' Assign to A button
ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate ' Makes the
cell above the current one active
If s = 1 Then ' If # has been pressed
ActiveCell.Value = "A#"
s = 0 'deselect #
Else
ActiveCell.Value = "A"
End If
End Sub

Sub Am() ' Assign to Am button
ActiveCell.Offset(rowOffset:=-1, columnOffset:=0).Activate ' Makes the
cell above the current one active
If s = 1 Then ' If # has been pressed
ActiveCell.Value = "A#m"
s = 0 'deselect #
Else
ActiveCell.Value = "Am"
End If
End Sub

Sub Sharp() ' Assign to # button
s = 1
End Sub

All the other chords follow the same pattern a A and Am above.

I'm not sure what you want to do with the transposition chart, but I'm
assuming it's a case of looking in the first row for your existing chord
and replacing it with the chord in one of the rows below (although it may
be columns instead). You probably need to look at the HLOOKUP or VLOOKUP
functions. I'm afraid I don't have time to look at specifics for you just
now.
--
Ian
--
"Ross" wrote in message
...
Thanks Ian,
That will be useful. Now what I want to happen for the second part of
my question is this: I want to invoke my chord placement macros (as
explained in second part of first post) so that when I click on the
textbox I have a assigned the macro to it will paste the chord, E or F or
F# in the currently selected cell. I will create a series of small text
boxes, each with the text for the chord A, Am, A7, etc and assign a
different macro for each one. What I want to happen is, I click on a
particular cell and when I click the textbox with chord I want in the
selected cell, it is pasted (or typed) into that cell. Since I will
also have a transpostion chart that I have created on the sheet, using
this method, I will easily be able to transpose the chords of any song I
want. I then want to have one more text box that has # in it and the way
the macro would work assigned to that one is that say the chord I need is
A#m I would invoke the Am macro, double-click that cell so that my cursor
is between the A and the m and then click on the # text box to insert the
sharp. That way, I don't have to have a separate text box for A,A#,
Bm,B#m . . .
To make this clearer, I have uploaded the file "Song builder" with an
example song pasted in. Please have a look at it.
It is at: http://www.ssor.net/Sheet/songbuilder.xls

Thanks in advance for your help

wrote in message
...
Right click on sheet tab, select View Code and paste this code at the
flashing cursor.

As written the macro will move the cursor down one cell form the current
position.

Sub OffsetSelectedCell()
'Replace r & c below as required
r = 1 ' 0 = stay put, 1 = down 1, -1 = up 1
c = 0 ' 0 = stay put, 1 = right 1, -1 = left 1
ActiveCell.Offset(rowOffset:=r, columnOffset:=c).Activate
End Sub


--
Ian
--
"Ross" wrote in message
...
Hi,
I don't know how to write macros, I only create them by recording
them. I would like to know how I tell the cursor to move cells to
right, left, or up and down, from a cell, so that when I run the macro,
the movent will happen from the current selected cell.

Also, I am trying to create a spreadsheet that I can paste lyrcs to a
song into, and then having a series of text box buttons I create --
each with the name of a chord, like Am or b7 as the text in it. Each
one of these would be assigned a macro. I want to be able to click
above a cell above the particular line of lyric, and then go and click
on the text box associated with the chord I want and have that cord pop
into selected cell. I would format all the columns as one cell wide.
I did this and when I paste in the lyracs, say starting at A1, the
lyrcis are displayed across the page, even though they are all in the
first cells. of column A. (I doulbe space the lyrics before I paste
them in, leaving a row of single spaced cells above each line, in which
to put my chords) Is that doable?

Yeah! and thanks Ian,
I made it 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
separed 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" wrote in message
...
Hi,
I don't know how to write macros, I only create them by recording them.
I would like to know how I tell the cursor to move cells to right, left,
or up and down, from a cell, so that when I run the macro, the movent will
happen from the current selected cell.

Also, I am trying to create a spreadsheet that I can paste lyrcs to a song
into, and then having a series of text box buttons I create -- each with
the name of a chord, like Am or b7 as the text in it. Each one of these
would be assigned a macro. I want to be able to click above a cell above
the particular line of lyric, and then go and click on the text box
associated with the chord I want and have that cord pop into selected
cell. I would format all the columns as one cell wide. I did this and
when I paste in the lyracs, say starting at A1, the lyrcis are displayed
across the page, even though they are all in the first cells. of column A.
(I doulbe space the lyrics before I paste them in, leaving a row of single
spaced cells above each line, in which to put my chords) Is that doable?

Try this:

Sub sharp()
If ActiveCell.Value < "" Then ActiveCell.Value = Left(ActiveCell, 1) & "#"
& Right(ActiveCell, Len(ActiveCell) - 1)
End Sub


--
Ian
--
"Ross" wrote in message
...
Yeah! and thanks Ian,
I made it 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 separed 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" wrote in message
...
Hi,
I don't know how to write macros, I only create them by recording them.
I would like to know how I tell the cursor to move cells to right, left,
or up and down, from a cell, so that when I run the macro, the movent
will happen from the current selected cell.

Also, I am trying to create a spreadsheet that I can paste lyrcs to a
song into, and then having a series of text box buttons I create -- each
with the name of a chord, like Am or b7 as the text in it. Each one of
these would be assigned a macro. I want to be able to click above a cell
above the particular line of lyric, and then go and click on the text box
associated with the chord I want and have that cord pop into selected
cell. I would format all the columns as one cell wide. I did this and
when I paste in the lyracs, say starting at A1, the lyrcis are displayed
across the page, even though they are all in the first cells. of column
A. (I doulbe space the lyrics before I paste them in, leaving a row of
single spaced cells above each line, in which to put my chords) Is that
doable?