Solved: Insert Row Macro/VBA

[LarryLaser]

Ok, so, in the code below, (from your erlier post), this can only affect the last cell that can be selected or "active" because it is in the Private Sub - "SelectionChange". Correct??

[vba] '//if reached the last entry in this row
If Left(Target.Address, 2) = LastCol Then
Application.EnableEvents = False
'
[/vba]

If so, can Private Sub Change be used directly to ;

[vba]Private Sub Worksheet_Change(ByVal Target As Range)

'
Dim Cell As Range
'
Const LastCol As String = "$M"
'**************************
'
If Target.Row.Count = 0.00 Then Exit Sub
'
If Changed = True And Target.Row = Range("B":"M" & Rows.Count).End(xlUp).Row Then
'
On Error Goto Finish
ActiveSheet.Unprotect password:=""
Application.EnableEvents = False
'
'//insert new row
Rows(Target.Row + 1).Insert Shift:=xlDown
'
'//copy row
Rows(Target.Row).Copy
'
'//paste formats in the new row
Rows(Target.Row + 1).PasteSpecial xlPasteFormats
Rows(Target.Row + 1).Borders(xlEdgeTop).LineStyle = xlNone
'
With Range("M" & Target.Row + 1).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'
'//copy formulas to the new row
For Each Cell In Range("B" & Target.Row, "M" & Target.Row)
If Cell.HasFormula Then Cell.Offset(1, 0) = Cell.FormulaR1C1
Next
'
Range("B" & Target.Row + 1).Select
Changed = False
End If
'
End If
Finish:
Application.EnableEvents = True
ActiveSheet.Protect password:=""

End Sub [/vba]

[LarryLaser]

Remember that the Last 3 cells (col's K,L and M) are locked and hidden.

[johnske]

No, the change and selection change take place at different times... try the last code I just posted.

[lucas]

That works great John

[LarryLaser]

I did, it still functions the same way, I am trying to get it to only fire IF
the last column, of Range B:M, and cell "M" is not <> 0.00, Then

[johnske]

If I read your intent correctly, I think this will give you what you want.
In the last code I posted, change
[vba]'//if past the last manual entry in this row
If Changed = True And Target.Row = Range("B" & Rows.Count).End(xlUp).Row And Target.HasFormula Then[/vba]
To:
[vba]'//if past the last manual entry in this row
If Changed = True _
And Target.Row = Range("B" & Rows.Count).End(xlUp).Row _
And Target.HasFormula _
And Range(LastFormulaCol & Target.Row) <> 0 Then[/vba]

[lucas]

Take a look at this one, same sheet as above Larry....

[LarryLaser]

Hey John
Sorry man I get nothing. I can't see why not either It looks like it should but it doesn't do any thing.

Steve
I tried yours, it still does the same as the problem John and I had back on test 2. all cell protection is lost.

[LarryLaser]

Hey Steve
Once I reset the protection then no change on new data entry.

[johnske]

Hey John
Sorry man I get nothing. I can't see why not either It looks like it should but it doesn't do any thing...

Well it should, it works fine on my machine. The arguments are now such that the row selected must

i) be the next empty row, and
ii) Column B (the State tax setting) must then have an entry made in it, and
iii) a monetary amount must also be entered somewhere in the row (to make the total in column M > 0)

Then, if a cell with a formula on the same row is selected the rest of the code is triggered and a new row inserted.

[LarryLaser]

Hey John

"Then, if a cell with a formula on the same row is "SELECTED" the rest of the code is triggered and a new row inserted.

The cell's that have formula's in them are all protected and can not be selected.

[johnske]

Hey John



The cell's that have formula's in them are all protected and can not be selected.

You can scroll across to them can't you (they are then selected). Sheet protection doesn't stop you 'selecting' a cell, it only stops you changing the selected cell if it's protected.

[lucas]

I'm with John on this one, the cells are protected.....? you can click on the cell but you can't see the formula or change the cell....

works fine on my machine also.?

[lucas]

Larry,
try downloading the attachment from post 47 again, when you open it put a number in the left column(tax) then tab across the column and add an expense as you go, then tab on across the sheet.......

row is added and sheet is protected. Then you can select that row again and tab across it and nothing happens......isn't that what you were after?

[LarryLaser]

Hey Steve and John
If the cells have no protection you can select any cell anywhere, including areas off the sheet. The sheet then loose ist containment. I want it to only have data entry ranges to be selectable.

[lucas]

I may be wrong but I think your out of luck there Larry.

[lucas]

what do you mean by lose its containment?

[lucas]

The only way you could have more control in my opinion would be to use a userform......might be a good idea for your purpose.

[LarryLaser]

That's a future function I plan on adding to the front page (Sheet1). but I am not ready to get there yet.

Thanks for your help Steve

You Too John

Larry

[lucas]

Here's a simple data entry form to get you started....look it over and there are a million things you can do with it.

I just noticed it doesn't have option explicit on the code page for the form so there is probably a variable you will need to dim eventually but it will give you some ideas. Spread sheet can just be used to store your data, even be hidden and everything can be done with the form.....