| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
automatically insert row
Thanks that worked great.
"K Dales" wrote: Probably the best thing would be to pop up message box asking if the new row is needed: Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRow As Integer With Target If (.Column = 15) And .EntireRow.Cells(1, 2) = "FY2005" Then ' Ask if new row is desired: NewRow = MsgBox("Insert a row?", vbYesNo) ' If response is yes then add the row: If NewRow = vbYes Then .EntireRow.Insert .EntireRow.Cells(0, 2) = "FY2006" End If End If End With End Sub It might also be possible to avoid needing any user input just by looking to see if the new row is there already (i.e. if the row above has FY2006 in col B) - but that depends on whether this ALWAYS means you will not want a new line if there is a FY2006 in the row above. If you can guarantee this wil be the case then you could do it like this: Private Sub Worksheet_Change(ByVal Target As Range) Dim NewRow As Boolean With Target If (.Column = 15) And (.EntireRow.Cells(1, 2) = "FY2005") Then ' Next line deals Row 1: No prior row to check so new line will be added NewRow = (.Row = 1) ' Now deal with other lines, look at prior line for FY2006 in B: If Not NewRow Then NewRow = (.EntireRow.Cells(0, 2) < "FY2006") ' Now only add row if needed: If NewRow Then .EntireRow.Insert .EntireRow.Cells(0, 2) = "FY2006" End If End If End With End Sub "Leslie" wrote: Thank you again. It is working now. I think it was something I did that caused it not to work but now I can't remember what it was. I realized after using it though that since the insert row is based on a "change" that more than one row gets inserted if I keep making changes to that one cell. Is there a way to limit it to a macro where I have to press Cntrl +Z (or something of that nature) and it inserts the new rows only once. Thanks. "K Dales" wrote: 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. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Insert new row automatically | Excel Discussion (Misc queries) | |||
| Insert New Row Automatically | Excel Discussion (Misc queries) | |||
| Automatically insert row | Excel Discussion (Misc queries) | |||
| Automatically insert time in excel but not automatically updated | Excel Worksheet Functions | |||
| automatically insert a row | Excel Worksheet Functions | |||
Hybrid Mode
