Insert formulas on data entry trigger

[Jim G]

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?

 

[Bernie Deitrick]

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]

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

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]

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

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

 

[Bernie Deitrick]

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

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.

 

[Bernie Deitrick]

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

 

[Jim G]

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.

 

[Bernie Deitrick]

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]

Thank you Bernie, that was very helpful, works a treat.

Cheers