Solved: Insert Row Macro/VBA

[LarryLaser]

Hello
Neogeek here
I have a problem I have been trying to solve for days with no luck.

I am creating an Expense Workbook for myself and a few friends of mine that are employed in the same type of industry (Heavy Construction), that can help us deal with some of the idiotic tax laws that we have to deal with.

Specs:
OS= WinXP
Excel = 97 to 2k3

Workbook:
10 sheets, protected and customized

(Attaached is a .doc with an Image of one of the sheets to give you a view of what the pages look like)

Each sheet is for different data entries concerning types of tax codes.
At the bottom of each sheet is the totals dealing with sub totals, sales tax, receipt types and value types.

Previous rows are for the data entry.

Here is what I am trying to do;

• When data entry reaches the last row in the data entry range I want a macro to fire and “insert row” and copy “each cells format, data validation and formulas.
• Update the formulas in the cells below (“At the bottom of each sheet is the totals dealing with sub totals, sales tax, receipt types and value types.”)

I have tried to record a macro to accomplish this function and it only inserts the row, copies the format but does not copy the validation and formulas to the new row. It does, however update the totals range.

Does anyone have a solution for this Issue ??

[johnske]

Hi Larry, and welcome to VBAX.

This is worksheet code - copy and paste it in the code module for the relevant worksheet
[VBA] Option Explicit
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Dim Cell As Range
'**************************
'put your own last column below
Const LastCol As String = "$K"
'**************************
'
'//if reached the last entry in this row
If Left(Target.Address, 2) = LastCol Then
'
'//insert a new row
Rows(Target.Row + 1).Insert Shift:=xlDown
'
'//copy the row
Rows(Target.Row).Copy
'
'//paste the formats in the new row
Rows(Target.Row + 1).PasteSpecial xlPasteFormats
'
'//copy the formulas to the new row
For Each Cell In Range("B" & Target.Row, LastCol & Target.Row)
If Cell.HasFormula Then Cell.Offset(1, 0) = Cell.FormulaR1C1
Next
'
'//select column B in the new row for next entry
Intersect(Target.EntireRow, Columns(2)).Offset(1, 0).Select
End If
'
End Sub[/VBA]

[LarryLaser]

Hey johnske
Thanks for the response.
but, a couple of probs- It don't work.

couple of Q's
I assume that I need to enter this code for each sheet that needs to function this way? correct??

can you go into depth a little about the arguments?

I would appreciate this.

Thanks again
Larry

[gibbo1715]

Works for me on office 2000

try the attachment

Gibbo

[gibbo1715]

Actually this zipped version is probably better

[johnske]

Cut out any sensitive info and post the workbook

[LarryLaser]

Hey johnske
Attached is a copy of my test workbook (sensitive data omitted).
the code you posted is in the VBE for each sheet that needs the function.

I tried the code posted by "gibbo1715" but it has a strange reaction. (leaving Copy/Paste function alive)

As you will see the pages are protected (no password, just locked and hidden cells), the function (i assume) needs to unprotect the sheet, then perform the function then re-protect the sheet. If so can I begg you to give me a detailed explaination as to the Var's arg's and so on.

I have a number of books and ton's of sites I study, but I can't seem to grasp the totality of the operation. I even subscribed to MR.Excel new book to see if it will help me to comprehend the coding in VBA. (Still haven't received the first chapter.)

Thank You so very much for your assistance.
</IMG>

[johnske]

Hi Larry,

Cannot use the file you zipped, it was a .RAR file and not an .xls file.

If the sheet's protected it has to be unprotected for the code to work. (as in the example below)

This is "Worksheet Event" code, the event in this case being that a new cell has been selected. Your VBA Help files are a good source of information about this.

LastCol is the column that is the 'trigger' column, i.e. as soon as a cell is selected anywhere in that column, the code is triggered and a new row - with the code and all formats of the row above - is inserted directly below the selected cell.

I see from the image in your word attachment that your data goes from column B to column K. Now, assuming that you want to make manual entries in every cell in the row you're working on, the LastCol in that case will be the next column, column L.

However I see that column J has the header "Plus Tax" and column K has the header "Total" so I'm guessing that there are probably formulas for cells in both those columns. If that's the case, then the LastCol is column J. Adjust it to suit so that the code is triggered as soon as you hit enter after making your last manual entry in the row.

Example workbook attached...

[vba]Option Explicit
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Dim Cell As Range
'**************************
'put your own last column below
Const LastCol As String = "$L"
'**************************
'
ActiveSheet.Unprotect password:=""
UsedRange.Locked = False
'
'//if reached the last entry in this row
If Left(Target.Address, 2) = LastCol Then
'
'//insert a new row
Rows(Target.Row + 1).Insert Shift:=xlDown
'
'//copy the row
Rows(Target.Row).Copy
'
'//paste the formats in the new row
Rows(Target.Row + 1).PasteSpecial xlPasteFormats
'
'//copy the formulas to the new row
For Each Cell In Range("B" & Target.Row, LastCol & Target.Row)
If Cell.HasFormula Then Cell.Offset(1, 0) = Cell.FormulaR1C1
Next
'
'//select column A in the new row for next entry
Intersect(Target.EntireRow, Columns(2)).Offset(1, 0).Select
End If
'
ActiveSheet.Protect password:=""
'
End Sub[/vba]

[LarryLaser]

Hey Johnske
Sorry bout the screwup, This site don't like .rar files and I messed up the conversion (zip'd the rer instead of the xls file).

The Sheets are all protected, locked and hidden cells, the last 2 column's are locked (no data entry) they contain formulas that rely on relational data from another table based on entry in column "B".

Not every Cell in the "data entry range" has data input, but the last column always changes if data is entered into the "data entry range".

I re-Zip's the xls file below

Thanks

[LarryLaser]

I tried the code,
It Unlocks and changes the protection of all cells.
[VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Dim Cell As Range
'**************************
'put your own last column below
Const LastCol As String = "$K"
'**************************
'
ActiveSheet.Unprotect Password:=""
UsedRange.Locked = False
'
'//if reached the last entry in this row
If Left(Target.Address, 2) = LastCol Then
'
'//insert a new row
Rows(Target.Row + 1).Insert Shift:=xlDown
'
'//copy the row
Rows(Target.Row).Copy
'
'//paste the formats in the new row
Rows(Target.Row + 1).PasteSpecial xlPasteFormats
'
'//copy the formulas to the new row
For Each Cell In Range("B" & Target.Row, LastCol & Target.Row)
If Cell.HasFormula Then Cell.Offset(1, 0) = Cell.FormulaR1C1
Next
'
'//select column A in the new row for next entry
Intersect(Target.EntireRow, Columns(2)).Offset(1, 0).Select
End If
'
ActiveSheet.Protect Password:=""
'
End Sub
[/VBA]

[johnske]

Download and try the attachment below. (Have a look at the format to see what I've done before making an entry)

[LarryLaser]

Hey johnske
Tried the attachment, same issue as before
"http://www.vbaexpress.com/forum/show...2987#post62987"
the cell Protection is lost when data entry is made, and the row insert does not happen unless you activate the last cell in the row.
The last 3 cells in the row are suppose to be protected, same as the Totals range below.
I am not sure what you mean (Have a look at the format to see what I've done before making an entry), cant see what format changes you made, verses the changes made by the macros.

The macro makes some very unusual or bizarre changes, un protects all cells that need protection, and I am unable to reset the cell protection.

[johnske]

Hey johnske
Tried the attachment, same issue as before
"http://www.vbaexpress.com/forum/show...2987#post62987"
the cell Protection is lost when data entry is made, and the row insert does not happen unless you activate the last cell in the row.
The last 3 cells in the row are suppose to be protected, same as the Totals range below.

sorry, my oops - try now.

I am not sure what you mean (Have a look at the format to see what I've done before making an entry), cant see what format changes you made, verses the changes made by the macros.

you had a lot of blank rows, you only need the one to make your current entry on so I deleted the others.

The macro makes some very unusual or bizarre changes, un protects all cells that need protection, and I am unable to reset the cell protection.

before I got your workbook I assumed that the whole sheet would be locked so inserted the line UsedRange.Locked = False so that any inserted line would be able to accept data that's to be typed in. I have set the cells to locked and invisible in the last three columns in the latest tweak, if you want the code to lock other parts of your data range you will need to specify it (e.g. you could set all previous entries as locked and only have the blank row for the current entry unlocked).

[LarryLaser]

Hey Johnske
still not working
Now the Totals ranges end protected but areas outlside the sheet are unprotected (including the blank cell in the totals area) and the insert row does not function (unless you unprotect the sheet and make a cell in the last 3 columns active).
+ afey debug warnings pop up. Mainly in the
[vba]Finish:
With Range("K8", "M" & Target.Row + 4)
.Locked = True
.FormulaHidden = True
End With
Application.EnableEvents = True
ActiveSheet.Protect password:=""
End Sub
[/vba]

[johnske]

Hey Johnske
still not working
Now the Totals ranges end protected but areas outlside the sheet are unprotected (including the blank cell in the totals area) and the insert row does not function (unless you unprotect the sheet and make a cell in the last 3 columns active).
+ afey debug warnings pop up. Mainly in the
[vba]Finish:
With Range("K8", "M" & Target.Row + 4)
.Locked = True
.FormulaHidden = True
End With
Application.EnableEvents = True
ActiveSheet.Protect password:=""
End Sub
[/vba]

Well if it's crashing and you're getting debug messages it won't work properly.

The only way I can duplicate that error is if I manually change the password to something else than the example I've given in the VBA code (an empty string). If you're using something other than an empty string as a password, the password must be inserted between the quotation marks wherever it says Password:=""

BTW, I just noticed a small formatting error when checking this, so use the following attachment...

[LarryLaser]

btw-- no passwords, the lock function error happens if you go home (ctl+home) and end up at A1

Is it possible to use dynamic named ranges...(ie..Range = Totals (I22:M24,-K23) where the rows change as the macro adds rows above)?

If so, naming ranges of cells; DataEntry, SubTotals, Totals, TitleHeaders & DeadZone
then, using named ranges to control protection for each range independently.

can the deadzone (area outside of the worksheet) be named so you can add code to lock it down.


</IMG>

[johnske]

btw-- no passwords, the lock function error happens if you go home (ctl+home) and end up at A1

You'll notice I added a sheet activate event to automatically select the first cell in the empty row when the sheet was activated. The idea being that you only need to activate the sheet, type in your data, and when you're finished you simply activate the next sheet. As there's absolutely nothing in the first row I couldn't imagine why anyone'd want to select it so didn't provide for that possibility. However, that's what's causing the error message - put [vba]If Target.Row = 1 Then Exit Sub[/vba] immediately above the line [vba]On Error GoTo Finish[/vba] to fix that.

Is it possible to use dynamic named ranges...(ie..Range = Totals (I22:M24,-K23) where the rows change as the macro adds rows above)?

If so, naming ranges of cells; DataEntry, SubTotals, Totals, TitleHeaders & DeadZone
then, using named ranges to control protection for each range independently.

Yes you could, but I must admit I'm not a fan of named ranges at the best of times and I don't see any point in using named ranges here.

can the deadzone (area outside of the worksheet) be named so you can add code to lock it down.

Don't know what you mean by this line

[LarryLaser]

Non of this is functioning correctly at all.
The Sheet Protection should "only" change IF

• If the SubTotal for (last) row (which should start out 0.00) in column (K, L or M [depending on the sheet]) changes "Then", code fires to:
  • UnProtect, Insert row xlDown+1, Copy formula - format and validation (all properties)."Then"
  • Protect sheet

The protection should be removed "ONLY" If the last cell in the SubTotals range changes from "0.00" and then all "cell properties" should be restored after the rowInsert function is complete.

The user must be able to evaluate or change any data they enter without affecting the protected areas.

Example:

In the event of an audit: the user can give the WorkBook to a CPA or IRS agent for evaluation and comparison to hard copies of the documentation and no change can be made without releasing the protection.

As it stands now. If the sheet is active you can clickon (or activate) cells that should be locked, including the header and deadzone areas.

can the deadzone (area outside of the worksheet) be named so you can add code to lock it down.

Don't know what you mean by this line

The DeadZone is the area outside of the "sheet data entry area" (the Dark Green cells. They should Stay locked, except during the insert row function.
At this point they get unlocked any time the sheet is active.

[johnske]

try this then

EDIT: BTW, I believe I answered your original question in my very first post (post #2 above)

... Here is what I am trying to do;

• When data entry reaches the last row in the data entry range I want a macro to fire and “insert row” and copy “each cells format, data validation and formulas.
• Update the formulas in the cells below (“At the bottom of each sheet is the totals dealing with sub totals, sales tax, receipt types and value types.”)

I have tried to record a macro to accomplish this function and it only inserts the row, copies the format but does not copy the validation and formulas to the new row. It does, however update the totals range.

Does anyone have a solution for this Issue ??

Replies with a blunt "it doesn't work" are not very helpful and can only lead to errors being introduced or compounded. In fact the original code only needed to have a small mod to unlock and lock the worksheet... In free help forums it's usually left up to the original poster to modify the code given to suit their own ends and to introduce their own mods or error handling to handle any unusual situations (or to post these as separate questions).

Regards,
John

[LarryLaser]

EDIT: BTW, I believe I answered your original question in my very first post (post #2 above)

no,you did not.
It has never functioned.
In my humble understanding, people post to these sites to try and learn how to make things work in the programs like Excel.
I have helped on other sites dealing with O.S. problems and always test out the solution before posting back to the poster.

In fact the original code only needed to have a small mod to unlock and lock the worksheet

can you go into depth a little about the arguments?

If I was an expert in Excel I would be answering questions not asking them.
The first code you posted disabled the protection all over the sheet and did not do the function I asked. It fired any where on the sheet (anywhere in the last column) and did not Copy/Paste the properties to the new cells. Nor did it restore the sheet to its original properties. This you should have seen if you tried the code in the file I posted.

In free help forums it's usually left up to the original poster to modify the code given to suit their own ends

That was the point of this Question

can you go into depth a little about the arguments?

to introduce their own mods or error handling to handle any unusual situations

again-

can you go into depth a little about the arguments?

(or to post these as separate questions).

How many times??