| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert formulas on data entry trigger
I have a data sheet template that has measurements entered into columns A
(Item), B (Width), C (Height) and D (Quantity). Columns E to L have formulas. Data starts on Row 6. If I enter the formulas into a hidden row (5), how can I have the formulas copied from E5:L5 into E6:L6 when the quantity is entered into column D and the cursor moved to column A of the next row to start the next data entry and so on. Data will vary from a few rows to dozens of rows. Does anyone have a suggestion for a macro based on a selection change in column D from row 6 onward? -- Jim |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert formulas on data entry trigger
Jim,
Copy the code below, right-click the sheet tab, select "View Code" and paste in the window that appears. Bernie Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column < 4 Then Exit Sub Application.EnableEvents = False Cells(Target.Row + 1, 1).Select Range("E5:L5").Copy Target.Offset(0, 1).Resize(1, 8) Application.EnableEvents = True End Sub "Jim G" wrote in message ... I have a data sheet template that has measurements entered into columns A (Item), B (Width), C (Height) and D (Quantity). Columns E to L have formulas. Data starts on Row 6. If I enter the formulas into a hidden row (5), how can I have the formulas copied from E5:L5 into E6:L6 when the quantity is entered into column D and the cursor moved to column A of the next row to start the next data entry and so on. Data will vary from a few rows to dozens of rows. Does anyone have a suggestion for a macro based on a selection change in column D from row 6 onward? -- Jim |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert formulas on data entry trigger
Thank you Bernie.
This worked exactly as I wanted. Nice adn simple, I even learned a thing or two to use next time. Cheers -- Jim "Bernie Deitrick" wrote: Jim, Copy the code below, right-click the sheet tab, select "View Code" and paste in the window that appears. Bernie Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column < 4 Then Exit Sub Application.EnableEvents = False Cells(Target.Row + 1, 1).Select Range("E5:L5").Copy Target.Offset(0, 1).Resize(1, 8) Application.EnableEvents = True End Sub "Jim G" wrote in message ... I have a data sheet template that has measurements entered into columns A (Item), B (Width), C (Height) and D (Quantity). Columns E to L have formulas. Data starts on Row 6. If I enter the formulas into a hidden row (5), how can I have the formulas copied from E5:L5 into E6:L6 when the quantity is entered into column D and the cursor moved to column A of the next row to start the next data entry and so on. Data will vary from a few rows to dozens of rows. Does anyone have a suggestion for a macro based on a selection change in column D from row 6 onward? -- Jim |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert formulas on data entry trigger
I presume I could include; "If Target.Row < 6 Then Exit Sub"
to ensure that only rows after headers and the formula row are affected. This will be a template and will have no data to start with. Since only formulas are copied it shouldn't matter if the completed file is reopened and data rentered (event triggered). However, If I wanted to prevent the change event occuring if data existed in the target, would I use; "If Not IsEmpty(Target) Then Exit Sub" or is there a better solution to this. -- Jim "Bernie Deitrick" wrote: Jim, Copy the code below, right-click the sheet tab, select "View Code" and paste in the window that appears. Bernie Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column < 4 Then Exit Sub Application.EnableEvents = False Cells(Target.Row + 1, 1).Select Range("E5:L5").Copy Target.Offset(0, 1).Resize(1, 8) Application.EnableEvents = True End Sub "Jim G" wrote in message ... I have a data sheet template that has measurements entered into columns A (Item), B (Width), C (Height) and D (Quantity). Columns E to L have formulas. Data starts on Row 6. If I enter the formulas into a hidden row (5), how can I have the formulas copied from E5:L5 into E6:L6 when the quantity is entered into column D and the cursor moved to column A of the next row to start the next data entry and so on. Data will vary from a few rows to dozens of rows. Does anyone have a suggestion for a macro based on a selection change in column D from row 6 onward? -- Jim |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert formulas on data entry trigger
Jim,
Yes, this will do it: If Target.Row < 6 Then Exit Sub If you don't want to overwrite existing formulas, you could use: If Target.Offset(0,1).HasFormula Then Exit Sub HTH, Bernie MS Excel MVP "Jim G" wrote in message ... I presume I could include; "If Target.Row < 6 Then Exit Sub" to ensure that only rows after headers and the formula row are affected. This will be a template and will have no data to start with. Since only formulas are copied it shouldn't matter if the completed file is reopened and data rentered (event triggered). However, If I wanted to prevent the change event occuring if data existed in the target, would I use; "If Not IsEmpty(Target) Then Exit Sub" or is there a better solution to this. -- Jim "Bernie Deitrick" wrote: Jim, Copy the code below, right-click the sheet tab, select "View Code" and paste in the window that appears. Bernie Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column < 4 Then Exit Sub Application.EnableEvents = False Cells(Target.Row + 1, 1).Select Range("E5:L5").Copy Target.Offset(0, 1).Resize(1, 8) Application.EnableEvents = True End Sub "Jim G" wrote in message ... I have a data sheet template that has measurements entered into columns A (Item), B (Width), C (Height) and D (Quantity). Columns E to L have formulas. Data starts on Row 6. If I enter the formulas into a hidden row (5), how can I have the formulas copied from E5:L5 into E6:L6 when the quantity is entered into column D and the cursor moved to column A of the next row to start the next data entry and so on. Data will vary from a few rows to dozens of rows. Does anyone have a suggestion for a macro based on a selection change in column D from row 6 onward? -- Jim |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert formulas on data entry trigger
Jim,
I just re-read your post - If you want to prevent overwriting existing data, then before you do the formula copy, use If Target.Offset(0,1).HasFormula Then Application.EnableEvents =False Application.Undo Application.EnableEvents =True Msgbox "Don't overwrite existing data!" Exit Sub End If Bernie "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Jim, Yes, this will do it: If Target.Row < 6 Then Exit Sub If you don't want to overwrite existing formulas, you could use: If Target.Offset(0,1).HasFormula Then Exit Sub HTH, Bernie MS Excel MVP "Jim G" wrote in message ... I presume I could include; "If Target.Row < 6 Then Exit Sub" to ensure that only rows after headers and the formula row are affected. This will be a template and will have no data to start with. Since only formulas are copied it shouldn't matter if the completed file is reopened and data rentered (event triggered). However, If I wanted to prevent the change event occuring if data existed in the target, would I use; "If Not IsEmpty(Target) Then Exit Sub" or is there a better solution to this. -- Jim "Bernie Deitrick" wrote: Jim, Copy the code below, right-click the sheet tab, select "View Code" and paste in the window that appears. Bernie Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column < 4 Then Exit Sub Application.EnableEvents = False Cells(Target.Row + 1, 1).Select Range("E5:L5").Copy Target.Offset(0, 1).Resize(1, 8) Application.EnableEvents = True End Sub "Jim G" wrote in message ... I have a data sheet template that has measurements entered into columns A (Item), B (Width), C (Height) and D (Quantity). Columns E to L have formulas. Data starts on Row 6. If I enter the formulas into a hidden row (5), how can I have the formulas copied from E5:L5 into E6:L6 when the quantity is entered into column D and the cursor moved to column A of the next row to start the next data entry and so on. Data will vary from a few rows to dozens of rows. Does anyone have a suggestion for a macro based on a selection change in column D from row 6 onward? -- Jim |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert formulas on data entry trigger
THanks Bernie.
I have a few other questions if you don't mind. 1. What does the Application.undo actually do (nothing in the help file) 2. What should I do if target.Offset(0, 1) is a constant and not a formula. 3. Could the message be modified to give a choice of accepting or rejecting (IE: a mistake) the changed data. -- Jim "Bernie Deitrick" wrote: Jim, I just re-read your post - If you want to prevent overwriting existing data, then before you do the formula copy, use If Target.Offset(0,1).HasFormula Then Application.EnableEvents =False Application.Undo Application.EnableEvents =True Msgbox "Don't overwrite existing data!" Exit Sub End If Bernie "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Jim, Yes, this will do it: If Target.Row < 6 Then Exit Sub If you don't want to overwrite existing formulas, you could use: If Target.Offset(0,1).HasFormula Then Exit Sub HTH, Bernie MS Excel MVP "Jim G" wrote in message ... I presume I could include; "If Target.Row < 6 Then Exit Sub" to ensure that only rows after headers and the formula row are affected. This will be a template and will have no data to start with. Since only formulas are copied it shouldn't matter if the completed file is reopened and data rentered (event triggered). However, If I wanted to prevent the change event occuring if data existed in the target, would I use; "If Not IsEmpty(Target) Then Exit Sub" or is there a better solution to this. -- Jim "Bernie Deitrick" wrote: Jim, Copy the code below, right-click the sheet tab, select "View Code" and paste in the window that appears. Bernie Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column < 4 Then Exit Sub Application.EnableEvents = False Cells(Target.Row + 1, 1).Select Range("E5:L5").Copy Target.Offset(0, 1).Resize(1, 8) Application.EnableEvents = True End Sub "Jim G" wrote in message ... I have a data sheet template that has measurements entered into columns A (Item), B (Width), C (Height) and D (Quantity). Columns E to L have formulas. Data starts on Row 6. If I enter the formulas into a hidden row (5), how can I have the formulas copied from E5:L5 into E6:L6 when the quantity is entered into column D and the cursor moved to column A of the next row to start the next data entry and so on. Data will vary from a few rows to dozens of rows. Does anyone have a suggestion for a macro based on a selection change in column D from row 6 onward? -- Jim |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert formulas on data entry trigger
Jim,
Application.Undo just undoes the last action which, in this case, is the entry of a value into a single cell. You can go up the Undo stack for as many levels as exist - but note that use of a macro removes the undo stack. Here's how to ask: If Target.Offset(0,1).Value <"" Then If MsgBox("You are overwrititng existing data. Are you sure?",vbYesNo) = vbNo Then Application.EnableEvents =False Application.Undo Application.EnableEvents =True Exit Sub End If End If HTH, Bernie MS Excel MVP "Jim G" wrote in message ... THanks Bernie. I have a few other questions if you don't mind. 1. What does the Application.undo actually do (nothing in the help file) 2. What should I do if target.Offset(0, 1) is a constant and not a formula. 3. Could the message be modified to give a choice of accepting or rejecting (IE: a mistake) the changed data. -- Jim "Bernie Deitrick" wrote: Jim, I just re-read your post - If you want to prevent overwriting existing data, then before you do the formula copy, use If Target.Offset(0,1).HasFormula Then Application.EnableEvents =False Application.Undo Application.EnableEvents =True Msgbox "Don't overwrite existing data!" Exit Sub End If Bernie "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Jim, Yes, this will do it: If Target.Row < 6 Then Exit Sub If you don't want to overwrite existing formulas, you could use: If Target.Offset(0,1).HasFormula Then Exit Sub HTH, Bernie MS Excel MVP "Jim G" wrote in message ... I presume I could include; "If Target.Row < 6 Then Exit Sub" to ensure that only rows after headers and the formula row are affected. This will be a template and will have no data to start with. Since only formulas are copied it shouldn't matter if the completed file is reopened and data rentered (event triggered). However, If I wanted to prevent the change event occuring if data existed in the target, would I use; "If Not IsEmpty(Target) Then Exit Sub" or is there a better solution to this. -- Jim "Bernie Deitrick" wrote: Jim, Copy the code below, right-click the sheet tab, select "View Code" and paste in the window that appears. Bernie Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column < 4 Then Exit Sub Application.EnableEvents = False Cells(Target.Row + 1, 1).Select Range("E5:L5").Copy Target.Offset(0, 1).Resize(1, 8) Application.EnableEvents = True End Sub "Jim G" wrote in message ... I have a data sheet template that has measurements entered into columns A (Item), B (Width), C (Height) and D (Quantity). Columns E to L have formulas. Data starts on Row 6. If I enter the formulas into a hidden row (5), how can I have the formulas copied from E5:L5 into E6:L6 when the quantity is entered into column D and the cursor moved to column A of the next row to start the next data entry and so on. Data will vary from a few rows to dozens of rows. Does anyone have a suggestion for a macro based on a selection change in column D from row 6 onward? -- Jim |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Insert formulas on data entry trigger
Thank you Bernie, that was very helpful, works a treat.
Cheers -- Jim "Bernie Deitrick" wrote: Jim, Application.Undo just undoes the last action which, in this case, is the entry of a value into a single cell. You can go up the Undo stack for as many levels as exist - but note that use of a macro removes the undo stack. Here's how to ask: If Target.Offset(0,1).Value <"" Then If MsgBox("You are overwrititng existing data. Are you sure?",vbYesNo) = vbNo Then Application.EnableEvents =False Application.Undo Application.EnableEvents =True Exit Sub End If End If HTH, Bernie MS Excel MVP "Jim G" wrote in message ... THanks Bernie. I have a few other questions if you don't mind. 1. What does the Application.undo actually do (nothing in the help file) 2. What should I do if target.Offset(0, 1) is a constant and not a formula. 3. Could the message be modified to give a choice of accepting or rejecting (IE: a mistake) the changed data. -- Jim "Bernie Deitrick" wrote: Jim, I just re-read your post - If you want to prevent overwriting existing data, then before you do the formula copy, use If Target.Offset(0,1).HasFormula Then Application.EnableEvents =False Application.Undo Application.EnableEvents =True Msgbox "Don't overwrite existing data!" Exit Sub End If Bernie "Bernie Deitrick" <deitbe @ consumer dot org wrote in message ... Jim, Yes, this will do it: If Target.Row < 6 Then Exit Sub If you don't want to overwrite existing formulas, you could use: If Target.Offset(0,1).HasFormula Then Exit Sub HTH, Bernie MS Excel MVP "Jim G" wrote in message ... I presume I could include; "If Target.Row < 6 Then Exit Sub" to ensure that only rows after headers and the formula row are affected. This will be a template and will have no data to start with. Since only formulas are copied it shouldn't matter if the completed file is reopened and data rentered (event triggered). However, If I wanted to prevent the change event occuring if data existed in the target, would I use; "If Not IsEmpty(Target) Then Exit Sub" or is there a better solution to this. -- Jim "Bernie Deitrick" wrote: Jim, Copy the code below, right-click the sheet tab, select "View Code" and paste in the window that appears. Bernie Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count 1 Then Exit Sub If Target.Column < 4 Then Exit Sub Application.EnableEvents = False Cells(Target.Row + 1, 1).Select Range("E5:L5").Copy Target.Offset(0, 1).Resize(1, 8) Application.EnableEvents = True End Sub "Jim G" wrote in message ... I have a data sheet template that has measurements entered into columns A (Item), B (Width), C (Height) and D (Quantity). Columns E to L have formulas. Data starts on Row 6. If I enter the formulas into a hidden row (5), how can I have the formulas copied from E5:L5 into E6:L6 when the quantity is entered into column D and the cursor moved to column A of the next row to start the next data entry and so on. Data will vary from a few rows to dozens of rows. Does anyone have a suggestion for a macro based on a selection change in column D from row 6 onward? -- Jim |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Data Entry and Formulas | New Users to Excel | |||
| Automating Data Entry (Position the cursor, insert row) | Excel Programming | |||
| new row insert on data entry | Excel Discussion (Misc queries) | |||
| How do I protect formulas in a worksheet and allow data entry | Excel Worksheet Functions | |||
| Function to automatically insert a new sheet as a result of data entry? | Excel Worksheet Functions | |||
Linear Mode
