Is there a way to automate this process. When a value is entered into Col. O
which is "Jun" and in Col B in that same row is the label "FY 2005" I want a
row inserted above that row and I want "FY 2006" to be put in the new row
Col. B. Thanks for any ideas. I'm new to this programming stuff so all
specifics are very much appreciated.

Go to The Visual Basic Editor and make sure you are showing the Project
Explorer (View menu). In the Project Explorer highlight and select
(double-click) the line for the worksheet you want to do this for. Now in
the code pane put this event procedure (event procedures run when certain
events take place; this one runs when any cell in the worksheet is changed):

Private Sub Worksheet_Change(ByVal Target As Range)

' Target refers to the cell that was changed
With Target
' If it is column O and if Column B has "FY2005":
If (.Column = 15) And .EntireRow.Cells(1, 2) = "FY2005" Then
' Insert the row
.EntireRow.Insert
' Put FY2006 in the new row column B
.EntireRow.Cells(0, 2) = "FY2006"
End If
End With

End Sub


"Leslie" wrote:

Is there a way to automate this process. When a value is entered into Col. O
which is "Jun" and in Col B in that same row is the label "FY 2005" I want a
row inserted above that row and I want "FY 2006" to be put in the new row
Col. B. Thanks for any ideas. I'm new to this programming stuff so all
specifics are very much appreciated.

Thank you very much. I tried it but it didn't seem to work. In the project
explorer I found my worksheet and then I went "view" code and it pulled up an
empty pane where I pasted the code. Is there something else I need to do?
Thanks.

"K Dales" wrote:

Go to The Visual Basic Editor and make sure you are showing the Project
Explorer (View menu). In the Project Explorer highlight and select
(double-click) the line for the worksheet you want to do this for. Now in
the code pane put this event procedure (event procedures run when certain
events take place; this one runs when any cell in the worksheet is changed):

Private Sub Worksheet_Change(ByVal Target As Range)

' Target refers to the cell that was changed
With Target
' If it is column O and if Column B has "FY2005":
If (.Column = 15) And .EntireRow.Cells(1, 2) = "FY2005" Then
' Insert the row
.EntireRow.Insert
' Put FY2006 in the new row column B
.EntireRow.Cells(0, 2) = "FY2006"
End If
End With

End Sub


"Leslie" wrote:

Is there a way to automate this process. When a value is entered into Col. O
which is "Jun" and in Col B in that same row is the label "FY 2005" I want a
row inserted above that row and I want "FY 2006" to be put in the new row
Col. B. Thanks for any ideas. I'm new to this programming stuff so all
specifics are very much appreciated.

Should have worked if you had the right line selected in Project Explorer -
but to be absolutely sure go to the Window menu in the VBA editor and you
should see a list of windows for all the different modules you currently have
in all your open Excel workbooks (there is a code module for each workbook
and each worksheet; then others you may create). From the list you can
select the correct window: look for the one with your sheet's name on it,
e.g. Sheet1 (Code) and select that. You can verify you are using the correct
"blank" pane for the code if it has above it two dropdowns that should say
(General) and (Declarations) when you start; then if you look at the choices
in the left hand dropdown box it should say (General) and Worksheet. (Avoid
selecting anything from the right hand drop down or it will begin to set up a
different event procedure for you - one you will not want to use!)

Another thing you can do to make sure the code is running would be to put
this line just before the End Sub statement:
MsgBox "MY CODE HAS RUN"

This should pop up that message any time you change anything anywhere on
that sheet. If you want to make sure the code is running, this will tell
you, but of course you will want to delete that line after you see it is
working or it will become very annoying!

Hope this gets you going -
K Dales

"Leslie" wrote:

Thank you very much. I tried it but it didn't seem to work. In the project
explorer I found my worksheet and then I went "view" code and it pulled up an
empty pane where I pasted the code. Is there something else I need to do?
Thanks.

"K Dales" wrote:

Go to The Visual Basic Editor and make sure you are showing the Project
Explorer (View menu). In the Project Explorer highlight and select
(double-click) the line for the worksheet you want to do this for. Now in
the code pane put this event procedure (event procedures run when certain
events take place; this one runs when any cell in the worksheet is changed):

Private Sub Worksheet_Change(ByVal Target As Range)

' Target refers to the cell that was changed
With Target
' If it is column O and if Column B has "FY2005":
If (.Column = 15) And .EntireRow.Cells(1, 2) = "FY2005" Then
' Insert the row
.EntireRow.Insert
' Put FY2006 in the new row column B
.EntireRow.Cells(0, 2) = "FY2006"
End If
End With

End Sub


"Leslie" wrote:

Is there a way to automate this process. When a value is entered into Col. O
which is "Jun" and in Col B in that same row is the label "FY 2005" I want a
row inserted above that row and I want "FY 2006" to be put in the new row
Col. B. Thanks for any ideas. I'm new to this programming stuff so all
specifics are very much appreciated.

The (General) and (Declarations) changes to (Worksheet) and (Change) when I
put the code in. Should it be doing that? I tried to change it back but it
won't let me. Thanks.

"K Dales" wrote:

Should have worked if you had the right line selected in Project Explorer -
but to be absolutely sure go to the Window menu in the VBA editor and you
should see a list of windows for all the different modules you currently have
in all your open Excel workbooks (there is a code module for each workbook
and each worksheet; then others you may create). From the list you can
select the correct window: look for the one with your sheet's name on it,
e.g. Sheet1 (Code) and select that. You can verify you are using the correct
"blank" pane for the code if it has above it two dropdowns that should say
(General) and (Declarations) when you start; then if you look at the choices
in the left hand dropdown box it should say (General) and Worksheet. (Avoid
selecting anything from the right hand drop down or it will begin to set up a
different event procedure for you - one you will not want to use!)

Another thing you can do to make sure the code is running would be to put
this line just before the End Sub statement:
MsgBox "MY CODE HAS RUN"

This should pop up that message any time you change anything anywhere on
that sheet. If you want to make sure the code is running, this will tell
you, but of course you will want to delete that line after you see it is
working or it will become very annoying!

Hope this gets you going -
K Dales

"Leslie" wrote:

Thank you very much. I tried it but it didn't seem to work. In the project
explorer I found my worksheet and then I went "view" code and it pulled up an
empty pane where I pasted the code. Is there something else I need to do?
Thanks.

"K Dales" wrote:

Go to The Visual Basic Editor and make sure you are showing the Project
Explorer (View menu). In the Project Explorer highlight and select
(double-click) the line for the worksheet you want to do this for. Now in
the code pane put this event procedure (event procedures run when certain
events take place; this one runs when any cell in the worksheet is changed):

Private Sub Worksheet_Change(ByVal Target As Range)

' Target refers to the cell that was changed
With Target
' If it is column O and if Column B has "FY2005":
If (.Column = 15) And .EntireRow.Cells(1, 2) = "FY2005" Then
' Insert the row
.EntireRow.Insert
' Put FY2006 in the new row column B
.EntireRow.Cells(0, 2) = "FY2006"
End If
End With

End Sub


"Leslie" wrote:

Is there a way to automate this process. When a value is entered into Col. O
which is "Jun" and in Col B in that same row is the label "FY 2005" I want a
row inserted above that row and I want "FY 2006" to be put in the new row
Col. B. Thanks for any ideas. I'm new to this programming stuff so all
specifics are very much appreciated.

Yes, good: that is what it should do after you put the code in! Those
dropdowns are used to select (or show) what part of the project you are
working on, and so (after you put the code in) it is telling you it
recognizes it as the Worksheet_Change procedure. (General would mean it is
general code not linked to any object, Declarations means it is where you are
declaring variables, etc...).

Now the only issues are whether the code is running, and then if the results
turn out the way you want them to. First, make sure the VBA editor is not
giving you any error messages. If I did my part correctly there should not
be any problems with the code I sent. But sometimes these newsgroup windows
can split one line up onto two lines, and that could be a problem if that
happens. If you see any errors in the VBA editor check to see if maybe a
line looks like it got split up.

If it is entered correctly the Worksheet_Change procedure should run
automatically any time you make a change to your worksheet (if you type a new
value in a cell, or erase anything, etc.) You can test that with the MsgBox
idea I sent, because that would force the code to display the message every
time the procedure runs. So try typing into some cells and see if the
message comes up. If so it is running OK.

And then hopefully it will insert the rows the way you wanted it to - if it
is not working let me know - I will try to keep checking back to see if it is
working for you.

Ken

"Leslie" wrote:

The (General) and (Declarations) changes to (Worksheet) and (Change) when I
put the code in. Should it be doing that? I tried to change it back but it
won't let me. Thanks.

"K Dales" wrote:

Should have worked if you had the right line selected in Project Explorer -
but to be absolutely sure go to the Window menu in the VBA editor and you
should see a list of windows for all the different modules you currently have
in all your open Excel workbooks (there is a code module for each workbook
and each worksheet; then others you may create). From the list you can
select the correct window: look for the one with your sheet's name on it,
e.g. Sheet1 (Code) and select that. You can verify you are using the correct
"blank" pane for the code if it has above it two dropdowns that should say
(General) and (Declarations) when you start; then if you look at the choices
in the left hand dropdown box it should say (General) and Worksheet. (Avoid
selecting anything from the right hand drop down or it will begin to set up a
different event procedure for you - one you will not want to use!)

Another thing you can do to make sure the code is running would be to put
this line just before the End Sub statement:
MsgBox "MY CODE HAS RUN"

This should pop up that message any time you change anything anywhere on
that sheet. If you want to make sure the code is running, this will tell
you, but of course you will want to delete that line after you see it is
working or it will become very annoying!

Hope this gets you going -
K Dales

"Leslie" wrote:

Thank you very much. I tried it but it didn't seem to work. In the project
explorer I found my worksheet and then I went "view" code and it pulled up an
empty pane where I pasted the code. Is there something else I need to do?
Thanks.

"K Dales" wrote:

Go to The Visual Basic Editor and make sure you are showing the Project
Explorer (View menu). In the Project Explorer highlight and select
(double-click) the line for the worksheet you want to do this for. Now in
the code pane put this event procedure (event procedures run when certain
events take place; this one runs when any cell in the worksheet is changed):

Private Sub Worksheet_Change(ByVal Target As Range)

' Target refers to the cell that was changed
With Target
' If it is column O and if Column B has "FY2005":
If (.Column = 15) And .EntireRow.Cells(1, 2) = "FY2005" Then
' Insert the row
.EntireRow.Insert
' Put FY2006 in the new row column B
.EntireRow.Cells(0, 2) = "FY2006"
End If
End With

End Sub


"Leslie" wrote:

Is there a way to automate this process. When a value is entered into Col. O
which is "Jun" and in Col B in that same row is the label "FY 2005" I want a
row inserted above that row and I want "FY 2006" to be put in the new row
Col. B. Thanks for any ideas. I'm new to this programming stuff so all
specifics are very much appreciated.