I have a very simple spreadsheet I'm using to collect data for establishing
metrics. The spreadsheet is only 4 columns (one for the date, two that are
validated using drown-down lists, and the last is a text column for
description/comments).

I'd like to automate it so that when the user presses enter when in the last
column in the row (D),

1) the focus is returned to the first column in the same row, and
2) a new row is inserted above

thus placing the cursor in the first column of the new row, i.e., ready for
user input.

I understnad the logic for making it happen but don't have the slightest
idea of how to actually make it happen.

Can anyone help?

Thanks!

Right click sheet tab>view code>insert this. Now when you make an entry in
column D the macro will fire.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column <> 4 Then Exit Sub
With Cells(Target.Row, 1)
..Select
..EntireRow.Insert
End With
End Sub

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Bethany" <(E-Mail Removed)> wrote in message
news:1CAE1F4F-B2F8-492B-AEC3-(E-Mail Removed)...
>I have a very simple spreadsheet I'm using to collect data for establishing
> metrics. The spreadsheet is only 4 columns (one for the date, two that
> are
> validated using drown-down lists, and the last is a text column for
> description/comments).
>
> I'd like to automate it so that when the user presses enter when in the
> last
> column in the row (D),
>
> 1) the focus is returned to the first column in the same row, and
> 2) a new row is inserted above
>
> thus placing the cursor in the first column of the new row, i.e., ready
> for
> user input.
>
> I understnad the logic for making it happen but don't have the slightest
> idea of how to actually make it happen.
>
> Can anyone help?
>
> Thanks!

Don,

Thanks for the code snippet - I'm getting a syntax error, though. It seems
to be bombing on the "..Select" line. Any ideas?


"Don Guillett" wrote:

> Right click sheet tab>view code>insert this. Now when you make an entry in
> column D the macro will fire.
>
> Private Sub Worksheet_Change(ByVal Target As Range)
> If Target.Column <> 4 Then Exit Sub
> With Cells(Target.Row, 1)
> ..Select
> ..EntireRow.Insert
> End With
> End Sub
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Bethany" <(E-Mail Removed)> wrote in message
> news:1CAE1F4F-B2F8-492B-AEC3-(E-Mail Removed)...
> >I have a very simple spreadsheet I'm using to collect data for establishing
> > metrics. The spreadsheet is only 4 columns (one for the date, two that
> > are
> > validated using drown-down lists, and the last is a text column for
> > description/comments).
> >
> > I'd like to automate it so that when the user presses enter when in the
> > last
> > column in the row (D),
> >
> > 1) the focus is returned to the first column in the same row, and
> > 2) a new row is inserted above
> >
> > thus placing the cursor in the first column of the new row, i.e., ready
> > for
> > user input.
> >
> > I understnad the logic for making it happen but don't have the slightest
> > idea of how to actually make it happen.
> >
> > Can anyone help?
> >
> > Thanks!
>
>
>

It's not two dots
...
it is ONE dot
..

--
Don Guillett
SalesAid Software
(E-Mail Removed)
"Bethany" <(E-Mail Removed)> wrote in message
news:72CF0EE8-EF5A-4ADE-9BA0-(E-Mail Removed)...
> Don,
>
> Thanks for the code snippet - I'm getting a syntax error, though. It
> seems
> to be bombing on the "..Select" line. Any ideas?
>
>
> "Don Guillett" wrote:
>
>> Right click sheet tab>view code>insert this. Now when you make an entry
>> in
>> column D the macro will fire.
>>
>> Private Sub Worksheet_Change(ByVal Target As Range)
>> If Target.Column <> 4 Then Exit Sub
>> With Cells(Target.Row, 1)
>> ..Select
>> ..EntireRow.Insert
>> End With
>> End Sub
>>
>> --
>> Don Guillett
>> SalesAid Software
>> (E-Mail Removed)
>> "Bethany" <(E-Mail Removed)> wrote in message
>> news:1CAE1F4F-B2F8-492B-AEC3-(E-Mail Removed)...
>> >I have a very simple spreadsheet I'm using to collect data for
>> >establishing
>> > metrics. The spreadsheet is only 4 columns (one for the date, two that
>> > are
>> > validated using drown-down lists, and the last is a text column for
>> > description/comments).
>> >
>> > I'd like to automate it so that when the user presses enter when in the
>> > last
>> > column in the row (D),
>> >
>> > 1) the focus is returned to the first column in the same row, and
>> > 2) a new row is inserted above
>> >
>> > thus placing the cursor in the first column of the new row, i.e., ready
>> > for
>> > user input.
>> >
>> > I understnad the logic for making it happen but don't have the
>> > slightest
>> > idea of how to actually make it happen.
>> >
>> > Can anyone help?
>> >
>> > Thanks!
>>
>>
>>

Don,

Thanks! That did it! I really need to learn VBA - I just never seem to
have the time! Thanks, again

"Don Guillett" wrote:

> It's not two dots
> ...
> it is ONE dot
> ..
>
> --
> Don Guillett
> SalesAid Software
> (E-Mail Removed)
> "Bethany" <(E-Mail Removed)> wrote in message
> news:72CF0EE8-EF5A-4ADE-9BA0-(E-Mail Removed)...
> > Don,
> >
> > Thanks for the code snippet - I'm getting a syntax error, though. It
> > seems
> > to be bombing on the "..Select" line. Any ideas?
> >
> >
> > "Don Guillett" wrote:
> >
> >> Right click sheet tab>view code>insert this. Now when you make an entry
> >> in
> >> column D the macro will fire.
> >>
> >> Private Sub Worksheet_Change(ByVal Target As Range)
> >> If Target.Column <> 4 Then Exit Sub
> >> With Cells(Target.Row, 1)
> >> ..Select
> >> ..EntireRow.Insert
> >> End With
> >> End Sub
> >>
> >> --
> >> Don Guillett
> >> SalesAid Software
> >> (E-Mail Removed)
> >> "Bethany" <(E-Mail Removed)> wrote in message
> >> news:1CAE1F4F-B2F8-492B-AEC3-(E-Mail Removed)...
> >> >I have a very simple spreadsheet I'm using to collect data for
> >> >establishing
> >> > metrics. The spreadsheet is only 4 columns (one for the date, two that
> >> > are
> >> > validated using drown-down lists, and the last is a text column for
> >> > description/comments).
> >> >
> >> > I'd like to automate it so that when the user presses enter when in the
> >> > last
> >> > column in the row (D),
> >> >
> >> > 1) the focus is returned to the first column in the same row, and
> >> > 2) a new row is inserted above
> >> >
> >> > thus placing the cursor in the first column of the new row, i.e., ready
> >> > for
> >> > user input.
> >> >
> >> > I understnad the logic for making it happen but don't have the
> >> > slightest
> >> > idea of how to actually make it happen.
> >> >
> >> > Can anyone help?
> >> >
> >> > Thanks!
> >>
> >>
> >>
>
>
>