PDA

View Full Version : Solved: Insert Row Macro/VBA



LarryLaser
04-20-2006, 11:57 AM
:banghead: Hello
Neogeek here :thinking:
I have a problem I have been trying to solve for days :work: 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 ?? :thinking:

johnske
04-20-2006, 02:42 PM
Hi Larry, and welcome to VBAX.

This is worksheet code - copy and paste it in the code module for the relevant worksheet
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

LarryLaser
04-21-2006, 07:19 AM
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
04-21-2006, 09:17 AM
Works for me on office 2000

try the attachment

Gibbo

gibbo1715
04-21-2006, 09:23 AM
Actually this zipped version is probably better

johnske
04-21-2006, 12:29 PM
Cut out any sensitive info and post the workbook

LarryLaser
04-21-2006, 12:52 PM
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. :rofl:
</IMG>

johnske
04-21-2006, 02:07 PM
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...

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

LarryLaser
04-21-2006, 04:13 PM
Hey Johnske
Sorry bout the screwup, :dunno 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
04-21-2006, 04:28 PM
I tried the code,
It Unlocks and changes the protection of all cells.
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

johnske
04-21-2006, 06:01 PM
Download and try the attachment below. (Have a look at the format to see what I've done before making an entry)

LarryLaser
04-22-2006, 08:57 AM
Hey johnske
Tried the attachment, same issue as before
"http://www.vbaexpress.com/forum/showthread.php?p=62987#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
04-22-2006, 02:09 PM
Hey johnske
Tried the attachment, same issue as before
"http://www.vbaexpress.com/forum/showthread.php?p=62987#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
04-22-2006, 02:50 PM
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
Finish:
With Range("K8", "M" & Target.Row + 4)
.Locked = True
.FormulaHidden = True
End With
Application.EnableEvents = True
ActiveSheet.Protect password:=""
End Sub

johnske
04-22-2006, 03:29 PM
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
Finish:
With Range("K8", "M" & Target.Row + 4)
.Locked = True
.FormulaHidden = True
End With
Application.EnableEvents = True
ActiveSheet.Protect password:=""
End Sub
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
04-22-2006, 04:21 PM
:cleverman 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>:bug:

johnske
04-22-2006, 08:22 PM
:cleverman 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 If Target.Row = 1 Then Exit Sub immediately above the line On Error GoTo Finish 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
04-22-2006, 11:27 PM
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
04-23-2006, 01:12 AM
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
04-23-2006, 08:46 AM
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??

lucas
04-23-2006, 10:30 AM
Hi John,
Seems this thread is getting off track. Larry needs to understand that the help offered here is free and that rudeness is not conducive to getting answers to questions which is what we are supposed to be doing, not providing working applications.

Larry, there is lots of help available here but you are expected to do some of the work yourself and maintain a civil decorum. I have been following this thread and you have basically asked for someone to solve your problem for you and John has gone above and beyond, submitting efforts repeatedly to try to help you. You have contributed only that what John has offered has not worked for you. It takes time and patience to work out problems like this through postings so please be patient and civil and I'm sure this can be sorted out.

LarryLaser
04-23-2006, 11:02 AM
Steve
Just exactly where have I been uncivilized?
I have been working on this problem for a couple of weeks to no avail.
I am not an expert in Excel, I am a Basic user trying to learn and gain a better understanding of VBA. I have courteously asked a very basic Question.

can you go into depth a little about the arguments?for a very good reason, trying to understand what each argument is suppose to do.
If you consider this to be rude then please excuse me.
Last night I worked on the code in the workbook till 4 in the morning trying to make the mod's Johnske commented about and have gotten nowhere.
If you have a better suggestion, Please, be my guest.
Larry

lucas
04-23-2006, 11:18 AM
Hi Larry,
your response in post 20 is what triggered my response, especially after I read John's post 19 and noticed you hadn't even downloaded his attachment (0 downloads) and it seems to do exactly what you asked for it to do.

I may be missing something but you will have to look at it and let us know if we're still missing something.....

LarryLaser
04-23-2006, 12:33 PM
Hey Steve
I download and tried it. The cell protection function is staying the same, that much is true. But nothing is happening when the last cell changes in the target column. That is why I keep asking the same Question
can you go into depth a little about the arguments?

If I could just get a better understanding of what is being attempted I could figure out what I need to mod to make it work the way I need.
When I enter data nothing happens, the active cell jumps to home (1st cell in the data entry range) no insert, no copy, nothing.

Let me try to explain differently; Six of the sheets will change by the users data entry, here is just one of them.







Sheet7; B2:K~(~ = However many rows needed)
B2:K8 locked =header info
B9:H~ unlocked =data entry for user
I9:K~ Locked & Hidden =auto functions & formulas

Last 3 rows in the sheet are the totals
G~:K~ locked =totals data is transferred to Statement (sheet1)







Columns "B-D" always have data, "E:H" can have data (one must), "I" and "J" are conditional (depending on the entry in column "B", "K" is the total of the row from "E:J" (it also totals on condition of the status of "B")
Column formats:
"B" = Number, Validation (Tax code setting based on sheet2)
"C" = Date
"D" = Text (receipt details)
"E:H" = data entry, Currency, (always accessible to the user, Once completed the whole workbook will be locked and password protected, havent got that far yet)
"I:K" = Row SubTotals & Totals, Locked, Hidden.







The type of career this Expense workbook is for is not the usual you may have seen. Myself and a number of my friends have worked in Heavy Construction, We work in different parts of the country during the same year. Tax codes and rates change for the area we are in, (I have worked in as many a 9 states in one year) I have never been able to find any Program (or for that matter) a CPA that fully understands the Deductability of any thing you spend when you are out in the field. And none of my friends have either. The State and Federal Tax codes change all the time and It really affect those of us who travel from state to state (even county to county).

So we need to be able to Itemize every penny and place it in the right category for our tax's to work out right.

edit, Also I am the only one, of all of my friends that has experiance with coding and App development, (and I am pretty Green when it comes to VBA and the .NET platform)

lucas
04-23-2006, 01:32 PM
Ok Larry,
Looks and sounds like your sheet protection is correct and now your trying to get the add row to trigger on the last cell of a row....right?

Looking only at the vehicle data sheet, if you look at the code for the sheet which is supposed to add a row you will find it is set to trigger in column K....which is a protected cell with a formula...

It works for me if I change the K to a J in Johns code for the sheet.....in the vehicle data sheet. Thats the only sheet I looked at.


Const LastCol As String = "$J"

lucas
04-23-2006, 01:58 PM
Further testing shows some work needed on getting the totals and subtotal forumlas to update with the added rows...the rows however are getting their new formula's correct.

LarryLaser
04-23-2006, 02:06 PM
Hey Steve
I have done this on other sheets and had the same success this way, But;
My question is this, Can I not get this function to kick when the totals cell "M" for the last row changes (cell being protected) form 0.00 to any #>?
The problem is that, If you have to correct any data in the range of DataEntry, and you happen to tab across the target column or select ("J" in this case) the VBA code fires and you get a new blank row in the middle of the data range.

lucas
04-23-2006, 02:22 PM
Yes I see what your getting at but I don't see an easy solution yet....its not that hard to get rid of the extra rows if you comment out the sheet code and unprotect the sheet.

lucas
04-23-2006, 02:22 PM
I tried changing it to m but the formula change doesn't seem to trigger it.......

LarryLaser
04-23-2006, 02:57 PM
True enough, that is an issue that I don't have a problem with, but my friends who I am creating this with are not even beginners, Most of them have never used Excel before and I would like their experiance to not be confusing and frustrating.

can something like this be done??

If Target.Cells.Count > 0.00 Then Exit Sub

and if it can, what code changes would I need to make in the target range??

johnske
04-23-2006, 04:06 PM
Yes I see what your getting at but I don't see an easy solution yet.....Steve, another worksheet event can take care of that :)


'<< EG for sheet "vehicle data"
Option Explicit
Private Changed As Boolean
'
Private Sub Worksheet_Activate()
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Select
End Sub
'
Private Sub Worksheet_Change(ByVal Target As Range)
Changed = True
End Sub
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Dim Cell As Range
'**************************
'put your own last column below
Const LastCol As String = "$K"
'**************************
'
If Target.Row = 1 Then Exit Sub
'
If Changed = True And Target.Row = Range("B" & Rows.Count).End(xlUp).Row Then
'
On Error GoTo Finish
ActiveSheet.Unprotect password:=""
'
'//if reached the last entry in this row
If Left(Target.Address, 2) = LastCol Then
Application.EnableEvents = False
'
'//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
Rows(Target.Row + 1).Borders(xlEdgeTop).LineStyle = xlNone
'
With Range("M" & Target.Row + 1).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'
'//copy the 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
'
'//select column A in the new row for next entry
Range("B" & Target.Row + 1).Select
Changed = False
End If
'
End If
Finish:
Application.EnableEvents = True
ActiveSheet.Protect password:=""

End Sub

John

LarryLaser
04-23-2006, 05:31 PM
John
this works to protect the cells that need protection and inserts row only if last row is blank when data entry commences, and the user tabs accross the last column cell is selected in the code "that is not protected".

good enough for now. :)
Thanks for your help
Larry

lucas
04-23-2006, 06:44 PM
Thanks for catching up on the thread and picking up my slack John.
I haven't checked it out the latest yet, short on time.

Larry, glad to hear that you got it sorted for now.

johnske
04-25-2006, 06:13 PM
BTW Larry, a related but completely separate issue here... I notice your worksheet code for your Sub Totals (and Totals) is of the form K21=SUM(E9:H20,J9:J20).

I would assume that you want any data on new rows to also be included in the sum after a new row is inserted. In that case you'll need to change the worksheet formulas so they're of the form K21=SUM(E9:OFFSET(H21,-1,0),J9:OFFSET(J21,-1,0))

LarryLaser
04-26-2006, 06:20 PM
Hey John
Not sure I follow what you mean?? Which Sheet are you talking about, and can you explain the advantage of using the =offset v basic =sum function??
1 *note* if you are talking about the Vehicle data (!Sheet 3), one column is not currency value (I), and does not add to the totals or subtotals. (a part that I did not complete on the "wkbk" I posted.

Thanks John
Larry

johnske
04-26-2006, 08:19 PM
Yes, it was the vehicle data sheet I looked at.

Basically, the formula for the sub total in K21=SUM(E9:H20,J9:J20) gives the sum of all the amounts in columns E, F, G, H, and J from the 9th to the 20th row (inclusive) and this formula is fixed.

So, even though you may have added rows so that the sub total is now in row 201, that sub total will not change - it won't add any data from those columns that may be in rows 21 to 200 (the new rows).

What the offset does is to add the new data from the new rows to the sub total. The offset simply refers to the range that is to be summed. As a simpler example, let us say you have data in rows 9 to 15 and a sub total in column K, row 16 that is the sum of the data K9 to K15... the formula K16=SUM(K9:K15) will give the sum but when you add a new row, the sub total formula that's now in K17 will remain K17=SUM(K9:K15).

However K16=SUM(K9:OFFSET(K16,-1,0)) gives the same sum, as OFFSET(K16,-1,0) is simply K15 and when you add a new row with this formula, the sub total formula in K17 will become K17=SUM(K9:OFFSET(K17,-1,0)), and OFFSET(K17,-1,0) is simply K16, so this is: K17=SUM(K9:K16) and any data from the new row has been included in the sum.

The minus one in the offset refers to 'the row above', and the zero refers to 'the same column' (K)

HTH

lucas
04-27-2006, 09:28 AM
Hi Larry,
Hope you follow what John is saying....the subtotal cells are not adding the new rows basically.

I hope you post the final solution for this when you get it done....some might find it useful.

LarryLaser
04-27-2006, 11:30 AM
Thanks John
The more "in depth" explanation of the function and arguments Really helps. I really need to gain a better understanding of VBA language.

Can I ask you another question??

Can you define this argument for me, especially the "2"..?
If Left(Target.Address, 2) = LastCol Then

johnske
04-27-2006, 01:26 PM
Left(Target.Address, 2)... the 2 refers to the first two letters or numbers in the targets address, i.e. the column letter (c.f. Right(Target.Address, 2) would refer to the last two letters or numbers).

If you had a large number of cells in a row so that you got into the AA, AB, AC, ..., etc columns you would need to use Left(Target.Address, 3) to get the correct column letter.

johnske
04-27-2006, 02:32 PM
Actually, the "Left" above was left-over from code that was modified to suit this. Now that it appears to be working correctly we can do a little to optimize this so that it's more efficient (this automatically inserts a new row as soon as a formula is selected and then only copies formulas from the cells that have formulas)...

'<< EG for sheet "vehicle data"
Option Explicit
Private Changed As Boolean
'
Private Sub Worksheet_Activate()
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Select
End Sub
'
Private Sub Worksheet_Change(ByVal Target As Range)
Changed = True
End Sub
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Dim Cell As Range
'
'**************************
'enter the last FORMULA column below
Const LastFormulaCol As String = "M"
'**************************
'
If Target.Row = 1 Then Exit Sub
'
'//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
'
On Error GoTo Finish
ActiveSheet.Unprotect password:=""
'
Application.EnableEvents = False
'
'//insert a new row below
Rows(Target.Row + 1).Insert shift:=xlDown
'
'//copy the row
Rows(Target.Row).Copy
'
'//paste the formats in the new row
With Rows(Target.Row + 1)
.PasteSpecial xlPasteFormats
'//get rid of the copied unwanted heavy line
.Borders(xlEdgeTop).LineStyle = xlNone
End With
'
'//restore the thin border in the "M" column
With Range("M" & Target.Row + 1).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'
'//copy the formulas to the new row
For Each Cell In Range(Target.Address, LastFormulaCol & Target.Row)
Cell.Offset(1, 0) = Cell.FormulaR1C1
Next
'
'//select column B in the new row for next entry
Range("B" & Target.Row + 1).Select
Changed = False
End If
'
Finish:
Application.EnableEvents = True
ActiveSheet.Protect password:=""
End Sub

LarryLaser
04-27-2006, 02:35 PM
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??

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


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

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

LarryLaser
04-27-2006, 02:39 PM
Remember that the Last 3 cells (col's K,L and M) are locked and hidden.

johnske
04-27-2006, 02:50 PM
No, the change and selection change take place at different times... try the last code I just posted.

lucas
04-27-2006, 02:57 PM
That works great John

LarryLaser
04-27-2006, 03:01 PM
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
04-27-2006, 03:24 PM
If I read your intent correctly, I think this will give you what you want.
In the last code I posted, change
'//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
To:
'//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

lucas
04-27-2006, 03:26 PM
Take a look at this one, same sheet as above Larry....

LarryLaser
04-27-2006, 04:26 PM
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
04-27-2006, 04:37 PM
Hey Steve
Once I reset the protection then no change on new data entry.

johnske
04-27-2006, 04:42 PM
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
04-27-2006, 04:49 PM
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
04-27-2006, 04:55 PM
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
04-27-2006, 05:22 PM
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
04-27-2006, 05:32 PM
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
04-27-2006, 05:41 PM
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
04-27-2006, 05:45 PM
I may be wrong but I think your out of luck there Larry.

lucas
04-27-2006, 05:46 PM
what do you mean by lose its containment?

lucas
04-27-2006, 05:51 PM
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
04-27-2006, 06:19 PM
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
04-27-2006, 06:27 PM
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.....

johnske
04-27-2006, 06:49 PM
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.(sigh...) Larry, I think you have a misapprehension of what protection is or does - even though you can select them, the cells ARE completely protected.

If you only want the data entry ranges to be selectable it's not quite as simple to trigger an event to insert your new row, you need to have the 1st formula column selectable to trigger the event, as shown below (Note that although the 1st formula column (K) can be selected simply to trigger the event, the contents of the cells in that column cannot be changed without unlocking the sheet. However, apart from the 1st formula column, only the data entry ranges are now selectable...)
'<< EG for sheet "vehicle data"
Option Explicit
Private Changed As Boolean
'
Private Sub Worksheet_Activate()
Range("B" & Rows.Count).End(xlUp).Offset(1, 0).Select
End Sub
'
Private Sub Worksheet_Change(ByVal Target As Range)
Changed = True
End Sub
'
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
Dim Cell As Range
'
'**************************
'enter the last FORMULA column below
Const LastFormulaCol As String = "M"
'**************************
'
If Target.Row = 1 Then Exit Sub
'
'//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
'
On Error GoTo Finish
ActiveSheet.Unprotect password:=""
'
Application.EnableEvents = False
'
'//insert a new row below
Rows(Target.Row + 1).Insert shift:=xlDown
'
'//copy the row
Rows(Target.Row).Copy
'
'//paste the formats in the new row
With Rows(Target.Row + 1)
.PasteSpecial xlPasteFormats
'
'//get rid of the copied unwanted heavy line
.Borders(xlEdgeTop).LineStyle = xlNone
End With
'
'//restore the thin border in the "M" column
With Range(LastFormulaCol & Target.Row + 1).Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
End With
'
'//copy the formulas to the new row
For Each Cell In Range(Target.Address, LastFormulaCol & Target.Row)
Cell.Offset(1, 0) = Cell.FormulaR1C1
Next
'
'//select column B in the new row for next entry
Range("B" & Target.Row + 1).Select
ScrollArea = "B9:K" & Selection.Row
Changed = False
End If
'
Finish:
Application.EnableEvents = True
ActiveSheet.Protect password:=""
End Sub

LarryLaser
04-28-2006, 08:00 AM
Hey John
Thanks for the post. A couple of Questions.
Does ScrollArea = "B9:K" & Selection.Row make cell "K" selectable or do I need to change the Protection properties for "col K"??

johnske
04-28-2006, 01:30 PM
Yes.

No.

This cat has been well and truly skinned, if you have some new questions please start a new thread.