Inputing Data
 

Dick, Thanks for the info. I'm new to Excel and am used to using macros,
but keep getting an error when I try to use it there. Where do I put
your coding and how do I activate it without tying it to a macro tied to
an input button from the forms menu? Thanks for your help.

DLS

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Inputing Data
 

DLS

An event macro is a special kind of macro that runs whenever an "event"
happens. This one is the Change event which will run whenever you change
any cell on the worksheet. When we get it put in the right place, you won't
need to do anything to run it except change A1.

To use this macro, press Alt-F11 to open the VBE. On the left side is the
Project Explorer which lists all of the open workbooks (Press Cntrl-R if you
don't think you see it). You need to find the one for the workbook on which
you're working. If the workbook is Book1.xls, then it will look like this

VBAProject (Book1.xls)

Under that project, there will be a folder called Microsoft Excel Objects
and in that folder will be at least two modules. One module will be the
ThisWorkbook module and the others will be for each sheet in the workbook.
If you're using Sheet1, then find the module called

Sheet1 (Sheet1)

Double click this module to open a Code Pane (main part of screen). At the
top of the code pane will be two drop down boxes. From the left drop down,
choose Worksheet. This will but the Worksheet_SelectionChange Sub and End
Sub statements in the code pane - you don't want these, but it does it
automatically because it's the default event for worksheets. From the right
drop down, choose Change. This will put the Private Sub and End Sub
statements for the Worksheet_Change event in the code pane. Any code you
put between these statements will execute whenever a cell is changed. Paste
the code from my post in between these statements (don't include the Private
Sub and End Sub lines when you copy). You should be all set. Close the VBE
and return to Excel.

Be sure to post back if you get stuck.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

<DLS wrote in message ...
Dick, Thanks for the info. I'm new to Excel and am used to using macros,
but keep getting an error when I try to use it there. Where do I put
your coding and how do I activate it without tying it to a macro tied to
an input button from the forms menu? Thanks for your help.

DLS

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Inputing Data
 

Dick, Thanks a million. It works absolutely great and just what I
needed. I added a macro button that returns the selected cell to A1 when
clicked and your procedure transfers the contents to the next cell on
row 10. Super. Now, after I enter about 10 items on row 10 I need to
have it somehow go to row 11 where I can start the input sequence again,
and then to row 12, etc. Thanks for your help.

DLS

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Inputing Data
 

Dick, I've been playing with the spreedsheet and noticed that
target.value remembers where it last was and keeps incrementing. I
cleared all the input values from the target range, i.e. A10 thru A15
and tried to reinput info. The first time I did it the value was sent to
A16. Is there a way I can reset if I make a mistake on input in A1?

DLS

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Inputing Data
 

DLS

Post the code as you have it now. It should work as you describe, but I'll
need to see the code you are using to see what's wrong.

--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

<DLS wrote in message ...
Dick, I've been playing with the spreedsheet and noticed that
target.value remembers where it last was and keeps incrementing. I
cleared all the input values from the target range, i.e. A10 thru A15
and tried to reinput info. The first time I did it the value was sent to
A16. Is there a way I can reset if I make a mistake on input in A1?

DLS

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Inputing Data
 

The only thing I changed is the input field from A1 to E6. Here's the
code:

Private Sub Worksheet_Change(ByVal Target As Range)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.EnableEvents = False

Dim DestRng As Range

Set DestRng = Me.Range("IV20")

If Target.Address = "$d$6" Then
With DestRng.End(xlToLeft)
If IsEmpty(.Item(1)) Then
.Value = Target.Value
Else
.Offset(0, 1).Value = Target.Value
End If

End With

Target.ClearContents
Target.Select

End If

Application.EnableEvents = True

End Sub

DLS

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!